dongyong6428
2019-03-14 10:21
浏览 77
已采纳

Mysql where子句在codeigniter中不起作用

I have MySQL Query like this: (I use CodeIgniter)

$report = $this->db->query("
          SELECT c.categoryName,
            note1.*,
            ((SELECT SUM(noteAmount)
              FROM notes
              WHERE DATE_FORMAT(noteDate, '%d-%m-%Y') = DATE_FORMAT(note1.noteDate, '%d-%m-%Y') AND noteType = 'cash_in')
            -
            (SELECT SUM(noteAmount)
             FROM notes
             WHERE DATE_FORMAT(noteDate, '%d-%m-%Y') = DATE_FORMAT(note1.noteDate, '%d-%m-%Y') AND noteType = 'cash_out')) as trxCount
           FROM notes AS note1
           JOIN
             (SELECT noteDate
              FROM notes
              GROUP BY noteDate
              HAVING COUNT(noteDate) > 0)
            AS note2
              ON note1.noteDate = note2.noteDate
            JOIN category c
              ON c.categoryID = note1.categoryID
            WHERE note1.noteType = 'cash_in'
            ORDER BY note1.noteDate DESC
            LIMIT $start, $per_page
          ")->result();

See on WHERE Clause: WHERE note1.noteType = 'cash_in'

I only want to take the cash data, but why do I get all the data? (including cash out and others). My table also have cash out data

notes table:

noteID | noteTitle | noteDate | noteAmount | categoryID | noteType

category table

categoryID | categoryName | parentID

图片转代码服务由CSDN问答提供 功能建议

我有这样的MySQL查询:(我使用CodeIgniter)

 <  code> $ report = $ this-&gt; db-&gt; query(“
 SELECT c.categoryName,
 note1。*,
((SELECT SUM(noteAmount)
 FROM notes 
 WHERE DATE_FORMAT(noteDate,  '%d-%m-%Y')= DATE_FORMAT(note1.noteDate,'%d-%m-%Y')AND noteType ='cash_in')
  -  
(SELECT SUM(noteAmount)
 FROM  notes 
 WHERE DATE_FORMAT(noteDate,'%d-%m-%Y')= DATE_FORMAT(note1.noteDate,'%d-%m-%Y')和noteType ='cash_out'))为trxCount 
 FROM 注释AS note1 
 JOIN 
(选择noteDate 
 FROM notes 
 GROUP BY noteDate 
 HAVING COUNT(noteDate)&gt; 0)
 AS note2 
 ON note1.noteDate = note2.noteDate 
 JOIN category c  
 ON c.categoryID = note1.categoryID 
 WHERE note1.noteType ='cash_in'
  ORDER BY note1.noteDate DESC 
 LIMIT $ start,$ per_page 
“) - &gt; result(); 
   
 
 

参见WHERE 条款:WHERE note1.noteType ='cash_in'

我只想获取现金数据,但为什么我要获取所有数据? (包括现金和其他)。 我的表还有现金数据

notes表:

noteTitle | noteDate | noteAmount | categoryID | noteType

类别表

categoryID | categoryName | parentID

  • 写回答
  • 好问题 提建议
  • 关注问题
  • 收藏
  • 邀请回答

3条回答 默认 最新

  • dongzhao1865 2019-03-14 11:56
    已采纳

    The query

    SELECT noteDate
              FROM notes
              GROUP BY noteDate
              HAVING COUNT(noteDate) > 0
    

    is selecting the entire notes tables as note2 (including cash_out entries) and filtering is being done only on entries where note1.noteType = 'cash_in'. This will give both cash_in and cash_out entries as the join condition only checks for note1.noteDate = note2.noteDate which will always have some match.

    Probably add where condition inside the on condition and see if that is what you want.

    评论
    解决 无用
    打赏 举报
  • 查看更多回答(2条)

相关推荐 更多相似问题