时间戳

首页 > 解决方案 > 获取子查询不为空的Mysql结果

mysql - 获取子查询不为空的Mysql结果

问题描述

我正在处理一个查询，该查询将使用子查询获取项目计数，其中两个子查询的结果都不为空。

我有这个查询

SELECT s.id, (SELECT SUM(qty) FROM salesItems WHERE sale_id = s.id AND status = 'S') AS shipped, (SELECT SUM(qty) FROM salesItems WHERE sale_id = s.id AND (status = 'A' OR status = 'B')) AS unshipped FROM `sales` s WHERE s.status = 'A' OR s.status = 'B'

产生这个：

id   | shipped | unshipped
1500 |   NULL  |    2
1501 |   NULL  |    1
1502 |   NULL  |    1
1503 |    1    |    2
1504 |    2    |  NULL
1505 |    2    |    5

我只希望已发货和未发货的结果具有正值。我尝试了几件事，例如

SELECT s.id, (SELECT SUM(qty) FROM salesItems WHERE sale_id = s.id AND status = 'S' HAVING SUM(qty) > 0) AS shipped, (SELECT SUM(qty) FROM salesItems WHERE sale_id = s.id AND (status = 'A' OR status = 'B') HAVING SUM(qty) > 0) AS unshipped FROM `sales` s WHERE s.status = 'A' OR s.status = 'B'

和

SELECT s.id, (SELECT SUM(qty) FROM salesItems WHERE sale_id = s.id AND status = 'S') AS shipped, (SELECT SUM(qty) FROM salesItems WHERE sale_id = s.id AND (status = 'A' OR status = 'B')) AS unshipped FROM `sales` s WHERE s.status = 'A' OR s.status = 'B' AND (SELECT SUM(qty) FROM salesItems WHERE sale_id = s.id AND status = 'S') IS NOT NULL AND (SELECT SUM(qty) FROM salesItems WHERE sale_id = s.id AND (status = 'A' OR status = 'B')) IS NOT NULL

两者仍然返回相同的结果。

我创建了一个 DB Fiddle，但它显示没有找到任何结果，我通过 MySql 运行相同的代码并返回所有项目

https://www.db-fiddle.com/f/mHuQ4nCHZVPmEacog1iQvq/0

标签： mysqlsubquery

解决方案

根据@Arganas 的回答，您可以使用HAVING

SELECT s.id, 
(
SELECT SUM(qty) 
FROM salesItems 
WHERE sale_id = s.id AND status = 'S'
) AS shipped, 
(
SELECT SUM(qty) 
FROM salesItems 
WHERE sale_id = s.id AND (status = 'A' OR status = 'B')
) AS unshipped 
FROM `sales` s 
WHERE (s.status = 'A' OR s.status = 'B') 
HAVING shipped is not Null and unshipped is not null

这是小提琴https://www.db-fiddle.com/f/mHuQ4nCHZVPmEacog1iQvq/1

推荐阅读