MySQL:当 'flag' 列的值为 1 时，从 SUM 函数中删除行

Question

在下面的 View 中:

CREATE VIEW msr_joined_view AS 
SELECT table1.id AS msr_id, table1.msr_number, table1.overview, 
SUM(table2.quantity * table2.unit_price) AS grand_total 
FROM table1 
INNER JOIN table2 ON table1.id = table2.msr_id 
GROUP BY table1.msr_number;

我需要 SUM(table2.quantity * table2.unit_price) 来忽略列 table2.deleted_record 等于 1 的任何行，这样如果我有我表中的以下数据:

table2
------
  msr_id(FK)  quantity    unit_price     deleted_record
  ----------  --------    ----------     --------------
     3          3             1.00          0
     3          10            9.99          1
     3          1             5.00          0

我对该组的 grand_total 应该仅为 6.00。

我不擅长 SQL IF 或 CASE 语句。这是要走的路，还是有更好的方法？

Answer 1

你需要一个过滤器。您可以通过添加一个 where 子句来做到这一点

CREATE VIEW msr_joined_view AS 
SELECT table1.id AS msr_id, table1.msr_number, table1.overview, 
SUM(table2.quantity * table2.unit_price) AS grand_total 
FROM table1 
INNER JOIN table2 ON table1.id = table2.msr_id
WHERE table2.Deleted_record != 1
GROUP BY table1.msr_number;

作为您和其他人的引用，正如评论中指出的那样，having 子句也很有用。

例如，假设您想过滤总计需要 > 10 的汇总结果。您可以这样做...

CREATE VIEW msr_joined_view AS 
SELECT table1.id AS msr_id, table1.msr_number, table1.overview, 
SUM(table2.quantity * table2.unit_price) AS grand_total 
FROM table1 
INNER JOIN table2 ON table1.id = table2.msr_id
WHERE table2.Deleted_record != 1
GROUP BY table1.msr_number;
HAVING grand_total > 10