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

  • 写回答

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条)

报告相同问题?

悬赏问题

  • ¥15 关于#c语言#的问题:构成555单稳态触发器,采用LED指示灯延时时间,对延时时间进行测量并显示(如楼道声控延时灯)需要Proteus仿真图和C语言代码
  • ¥50 神舟笔记本,没有linux的驱动,装的Ubuntu系统,想把风扇速度调到最大
  • ¥15 workstation加载centos进入emergency模式,查看日志报警如图,怎样解决呢?
  • ¥50 如何用单纯形法寻优不能精准找不到给定的参数,并联机构误差识别,给定误差有7个?matlab
  • ¥15 workstation加载centos进入emergency模式,查看日志报警如图,没有XFS,怎样解决呢?
  • ¥15 应用商店如何检测在架应用内容是否违规?
  • ¥15 Ubuntu系统配置PX4
  • ¥50 nw.js调用activex
  • ¥15 数据库获取信息反馈出错,直接查询了ref字段并且还使用了User文档的_id而不是自己的
  • ¥15 将安全信息用到以下对象时发生以下错误:c:dumpstack.log.tmp 另一个程序正在使用此文件,因此无法访问