业务情景：有这样一张表：其中Id列为表主键，Name为用户名，State为记录的状态值，Note为状态的说明，方便阅读。

需求描述：需要查询出这样的结果：某个人某种状态的记录数，如：张三，待审核记录数，审核中记录数，审核通过记录数；大概像这样：

解决方案：

1.建表和初始化测试数据： 

--1.建表语句CREATE TABLE [dbo].[Content](	[Id] [int] NULL,	[Name]   [nvarchar](50) NULL,	[State]  [int] NULL,	[Note]   [nvarchar](50) NULL,)  GO--2.初始化数据INSERT INTO CONTENT VALUES(1,'张三',1,'待审核')INSERT INTO CONTENT VALUES(2,'张三',2,'审核中')INSERT INTO CONTENT VALUES(3,'张三',2,'审核中')INSERT INTO CONTENT VALUES(4,'李四',1,'待审核')INSERT INTO CONTENT VALUES(5,'李四',2,'审核中')INSERT INTO CONTENT VALUES(5,'李四',3,'审核通过')

2.查询语句：

SELECT NAME,       SUM(审核通过) AS '审核通过',       SUM(待审核) AS '待审核',       SUM(审核中) AS '审核中'FROM   (           (               SELECT NAME,                      COUNT(*) AS '审核通过',                      0 AS '审核中',                      0 AS '待审核'               FROM   CONTENT               WHERE  [STATE] = 3                      AND NAME IN ('张三', '李四')               GROUP BY                      NAME           )                       UNION                       (               SELECT NAME,                      0 AS '审核通过',                      COUNT(*) AS '审核中',                      0 AS '待审核'               FROM   CONTENT               WHERE  [STATE] = 2                      AND NAME IN ('张三', '李四')               GROUP BY                      NAME           )            UNION            (               SELECT NAME,                      0 AS '审核通过',                      0 AS '审核中',                      COUNT(*) AS '待审核'               FROM   CONTENT               WHERE  [STATE] = 1                      AND NAME IN ('张三', '李四')               GROUP BY                      NAME           )       )tempGROUP BY       NAME

这里用到两个知识点：

1.UNION 操作符用于合并两个或多个 SELECT 语句的结果集。请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。

2.GROUP BY + 聚合函数(如SUM, COUNT, MAX, AVG等)统计数据。

版权声明：本文为博主原创文章，未经博主允许不得转载。