php - Mysql where 子句在 codeigniter 中不起作用

Question

我有这样的 MySQL 查询:(我使用 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'

我只想取现金数据，为什么我取的是所有数据？ (包括套现和其他)。我的表也有提现数据

注释表:

noteID | noteTitle | noteDate | noteAmount | categoryID | noteType

类别表

categoryID | categoryName | parentID

Answer 1

查询

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

正在选择整个 notes 表作为 note2(包括 cash_out 条目)并且仅对 note1.noteType = 'cash_in'< 的条目进行过滤。这将同时提供 cash_in 和 cash_out 条目，因为连接条件只检查 note1.noteDate = note2.noteDate，它总是有一些匹配。

可能在 on 条件中添加 where 条件，看看这是否是您想要的。