初学者

kkbh8khc 于 2021-07-26 发布在 Java

关注(0)|答案(2)|浏览(414)

我是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中实现这一点。提前谢谢

sql

来源：https://stackoverflow.com/questions/62431577/sql-query-beginner

2条答案

按热度按时间

eqfvzcg81#

如果您使用的是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

赞(0）回复(0）举报 2021-07-26

afdcj2ne2#

您可以尝试以下代码。

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;

赞(0）回复(0）举报 2021-07-26

我来回答

初学者

2条答案

相关问题

热门标签

最新问答