SQL 分组依据状态

Question

我正在寻找一种按状态对数据进行分组的方法。我有一个简单的选择，只是为了演示:

SQL Fiddle

MS SQL Server 2014 架构设置:

CREATE TABLE Projects ([ID] int, [Project] varchar(10));
INSERT INTO Projects ([ID], [Project])
VALUES
    (1, 'Admin'),
    (2, 'Accounts'),
    (3, 'Finance')
;

CREATE TABLE Tasks ([ID] int, [ProjectID] int, [Task] int, [Status] varchar(8));
INSERT INTO Tasks
    ([ID], [ProjectID], [Task], [Status])
VALUES
    (1, 1, '001', 'Open'),
    (2, 1, '002', 'Canceled'),
    (3, 1, '003', 'Canceled'),
    (4, 1, '004', 'Canceled'),
    (5, 2, '005', 'Rejected'),
    (6, 2, '006', 'Canceled'),
    (7, 2, '007', 'Canceled'),
    (8, 3, '008', 'Canceled'),
    (9, 3, '009', 'Canceled'),
    (10, 3, '010', 'Canceled'),
    (11, 3, '011', 'Canceled'),
    (12, 3, '0012', 'Rejected')
;

查询 1:

SELECT
  Projects.Project,
  COUNT(Tasks.Status) AS Total
FROM
  Projects
  LEFT JOIN Tasks ON Projects.ID = Tasks.ProjectID
 GROUP BY
   Projects.Project

Results :

|  Project | Total |
|----------|-------|
| Accounts |     3 |
|    Admin |     4 |
|  Finance |     5 |

但我正在寻找一种输出方式:

|  Project | Open | Canceled | Rejected |
|----------|------|----------|----------|
|    Admin |    1 |        3 |          |
| Accounts |      |        2 |        1 |
|  Finance |      |        4 |        1 |

所需结果按任务状态对项目进行分组。我怎样才能做到这一点？谢谢。

Answer 1

您可以使用条件聚合:

SELECT
    Projects.Project
  , [Open]   = count(case when tasks.status='open' then 1 end)
  , Canceled = count(case when tasks.status='Canceled ' then 1 end)
  , Rejected = count(case when tasks.status='Rejected ' then 1 end)
  , Total = COUNT(Tasks.Status)
FROM
  Projects
  LEFT JOIN Tasks ON Projects.ID = Tasks.ProjectID
 GROUP BY
   Projects.Project

fiddle :http://sqlfiddle.com/#!6/4b862/4/0

返回:

+----------+------+----------+----------+-------+
| Project  | Open | Canceled | Rejected | Total |
+----------+------+----------+----------+-------+
| Accounts |    0 |        2 |        1 |     3 |
| Admin    |    1 |        3 |        0 |     4 |
| Finance  |    0 |        4 |        1 |     5 |
+----------+------+----------+----------+-------+