时间戳

首页 > 解决方案 > 仅在满足所有 CASE 语句条件时才显示行

mysql - 仅在满足所有 CASE 语句条件时才显示行

问题描述

DB-小提琴

CREATE TABLE sales (
    id int primary key,
    campaign VARCHAR(255),
    date_offered VARCHAR (255),
    date_sold VARCHAR (255), 
    quantity_offered VARCHAR(255),
    quantity_sold VARCHAR(255),
    value_offered VARCHAR(255),
    value_sold VARCHAR(255)
);

INSERT INTO sales
(id, campaign,
date_offered, date_sold,
quantity_offered, quantity_sold,
value_offered, value_sold
)
VALUES 
("1", "C001", "2019-02-06", "2019-02-10", "500", "600", "4000", "4800"),
("2", "C002", "2019-04-15", "2019-04-12", "400", "800", "3200", "6400"),
("3", "C003", "2019-06-23", "2019-03-19", "650", "700", "5200", "5600"),
("4", "C004", "2019-08-09", "2019-08-22", "250", "150", "1750", "1050"),
("5", "C005", "2019-11-12", "2020-01-15", "860", "450", "6020", "3150"),
("6", "C006", "2020-01-30", "2019-12-18", "130", "280", "1170", "2520"),
("7", "C007", "2020-02-17", "2019-11-23", "470", "400", "4230", "3600");

上表中有几个错误，我CASE在此查询中的语句中发现了这些错误：

SELECT
id,
campaign,
(CASE WHEN DATE(date_offered) > DATE(date_sold) THEN "Error" ELSE "OK" END) AS Check_Date,
(CASE WHEN SUM(quantity_offered) < SUM(quantity_sold) THEN "Error" ELSE "OK" END) AS Check_Quantity,
(CASE WHEN SUM(value_offered)/SUM(quantity_offered) > 10 THEN "Error" ELSE "OK" END) AS Check_Price_01,
(CASE WHEN SUM(value_sold)/SUM(quantity_sold) > 7 THEN "Error" ELSE "OK" END) AS Check_Price_02
FROM sales
GROUP BY 1;

所有这一切都完全按照我需要的方式工作。

现在，我想修改result查询的，以便结果中只保留至少包含一个错误rows的查询。 如果所有值都正常，则不应显示：
row

id  campaign       Check_Date      Check_Quantity       Check_Price_01     Check_Price_02
1   C001            OK              Error                OK                 Error
2   C002            Error           Error                OK                 Error
3   C003            Error           Error                OK                 Error
6   C006            Error           Error                OK                 Error
7   C007            Error           OK                   OK                 Error

我必须如何更改我的查询才能实现这一目标？

标签： mysqlsql

解决方案

只需使用该HAVING子句。

SELECT
id,
campaign,
(CASE WHEN DATE(date_offered) > DATE(date_sold) THEN "Error" ELSE "OK" END) AS Check_Date,
(CASE WHEN SUM(quantity_offered) < SUM(quantity_sold) THEN "Error" ELSE "OK" END) AS Check_Quantity,
(CASE WHEN SUM(value_offered)/SUM(quantity_offered) > 10 THEN "Error" ELSE "OK" END) AS Check_Price_01,
(CASE WHEN SUM(value_sold)/SUM(quantity_sold) > 7 THEN "Error" ELSE "OK" END) AS Check_Price_01
FROM sales
GROUP BY 1
HAVING Check_Date = "Error" 
OR Check_Quantity = "Error"
...
;

推荐阅读