mysql - 在子查询中使用外部 MySQL 查询中的列

Question

我有一个 MySQL 查询，我想稍微优化一下。这是:

SELECT `items`.category AS 'Category', 'Without' AS 'Which', COUNT(*) AS 'Count'
FROM `items` 
WHERE `id`=706 
GROUP BY `category`

UNION

SELECT `items`.category AS 'Category', 'With' AS 'Which', COUNT(*) AS 'Count'  
FROM `items` 
WHERE `id`=706 AND `qa_items`.pointsoff=0 
GROUP BY `category`;

我得到的是这样的:

+---------------------------+---------+-------+
| Category                  | Which   | Count |
+---------------------------+---------+-------+
| Category A                | Without |     3 |
| Category B                | Without |     8 |
| Category C                | Without |     4 |
| Category A                | With    |     2 |
| Category B                | With    |     6 |
| Category C                | With    |     4 |
+---------------------------+---------+-------+

但我想要的是这样的:

+---------------------------+---------+-------+
| Category                  | Without | With  |
+---------------------------+---------+-------+
| Category A                |       3 |     2 |
| Category B                |       8 |     6 |
| Category C                |       4 |     4 |
+---------------------------+---------+-------+

我知道我可能必须执行某种子查询，但我不确定如何引用我在子查询中查找的类别。我确信我缺少一些简单的东西。有人可以帮忙吗？谢谢!

Answer 1

在这里您可以简单地删除 UNION 并使用:

SELECT
    category as Category,
    COUNT(*) as Without,
    SUM(pointsoff = 0) as With
FROM items
WHERE id=706
GROUP BY category;

注意 SUM( bool 条件)模式。这是一个非常有用值得记住的技巧，因为它有一些巧妙的性能方面的用法。请注意，您可以在其中使用任何您想要的 bool 条件，它只会为您提供组中满足条件的行数。