SQL 查询 - 初学者

Question

我是SQL新手。我需要SQL查询来实现上述输出。 我问过一个类似的问题，但这并不能很好地描述我的问题。这是我的详细要求。

我有一个表格，数据如下

Table: boxes
+------------+----------+
| box_id     | Status   | 
+------------+----------+
| 1          | created  |
| 2          | created  |
| 3          | opened   |
| 4          | opened   |
| 5          | closed   |
| 6          | closed   |
| 7          | closed   |
| 8          | wrapped  |
+------------+----------+

有了这个，还有一个状态名称destroyed但是没有被破坏的盒子。

我需要这样的输出

+--------------+-------+
| Status       | Count |
+--------------+-------+
| created      | 2     |
| opened       | 2     |
| destroyed    | 0     |
| other_status | 4     |    # this includes status (closed and wrapped)
| total        | 8     |
+--------------+-------+

如何在SQL中实现这一点。提前致谢

Answer 1

如果您使用的是MSSQL或MySQL8.0，则可以按如下方式使用CTE来实现所需的输出-

在这里演示

WITH CTE AS 
(
SELECT 'created' Status UNION ALL
SELECT 'opened'  UNION ALL
SELECT 'destroyed'  UNION ALL
SELECT 'other_status' 
)
,CTE2 AS
(
SELECT
CASE 
WHEN Status IN ('created','opened','destroyed') THEN Status 
ELSE 'other_status' 
END Status,
SUM(1) Cnt
FROM your_table
GROUP BY
CASE 
WHEN Status IN ('created','opened','destroyed') THEN Status 
ELSE 'other_status' 
END
)

SELECT CTE.Status,ISNULL(CTE2.Cnt, 0) Cnt
FROM CTE LEFT JOIN CTE2 ON CTE.Status = CTE2.Status
UNION ALL
SELECT 'Total' Status, SUM(CTE2.Cnt) FROM CTE2

Answer 2

您可以尝试以下代码。

select status, count(box_id) 
from table 
where status in ('created','opened', 'destroyed')  
group by status
UNION ALL
select 'other_status' status, count(box_id) 
from table 
where status not in ('created','opened', 'destroyed')  
UNION ALL
select 'total' status, count(box_id) 
from table;