时间戳

首页 > 解决方案 > SQL 只返回带有数据的行，而不是那些为 0 的行

sql - SQL 只返回带有数据的行，而不是那些为 0 的行

问题描述

我正在尝试报告有多少项目处于特定状态。我得到的代码只返回有值的行，而不返回没有值的行。

SELECT Department
       ,Status
       ,count(Department) AS "Number in status" 
       ,convert(VARCHAR, getdate(), 103) AS "Date report ran"
FROM [Server name].[xxx].[Database name]
WHERE STATUS NOT IN (
              'Closed'
              ,'Cancelled'
              )
       AND Department IN (
              'Department name'
              )
GROUP BY Department,Status

ORDER BY CASE 
              WHEN STATUS = 'Open'
                     THEN 1
              WHEN STATUS = 'In Progress'
                     THEN 2
              WHEN STATUS = 'Authorised'
                     THEN 3
              WHEN STATUS = 'Awaiting Auth'
                     THEN 4
              WHEN STATUS = 'Awaiting Collection'
                     THEN 5
              WHEN STATUS = 'Awaiting Delivery'
                     THEN 6
              WHEN STATUS = 'Awaiting Development'
                     THEN 7
              WHEN STATUS = 'Awaiting Engineer'
                     THEN 8
              WHEN STATUS = 'Awaiting Invoice/Credit'
                     THEN 9
              WHEN STATUS = 'Awaiting Quote'
                     THEN 10
              WHEN STATUS = 'Cancelled'
                     THEN 11
              WHEN STATUS = 'Chase End User'
                     THEN 12
              WHEN STATUS = 'Final Chase End User'
                     THEN 13
              WHEN STATUS = 'Closed'
                     THEN 14
              WHEN STATUS = 'Future Requirements'
                     THEN 15
              WHEN STATUS = 'In Test'
                     THEN 16
              WHEN STATUS = 'Next Release'
                     THEN 17
                     WHEN STATUS = 'On Hold'
                     THEN 18
                     WHEN STATUS = 'With End-User'
                     THEN 22
                     WHEN STATUS = 'With IIT'
                     THEN 23
                     WHEN STATUS = 'Processing Via Demand Management'
                     THEN 27
              END;

结果如下，但它不包括可能具有 0 值的所有其他状态：

+-----------------+----------------------------------+----+------------+
|                 |                                  |    |            |
+-----------------+----------------------------------+----+------------+
| Department name | Open                             | 92 | 29/03/2019 |
| Department name | In Progress                      |  9 | 29/03/2019 |
| Department name | Awaiting Development             |  4 | 29/03/2019 |
| Department name | Future Requirements              |  1 | 29/03/2019 |
| Department name | In Test                          |  7 | 29/03/2019 |
| Department name | On Hold                          | 15 | 29/03/2019 |
| Department name | With End-User                    | 28 | 29/03/2019 |
| Department name | With IIT                         |  2 | 29/03/2019 |
| Department name | Processing Via Demand Management |  2 | 29/03/2019 |
+-----------------+----------------------------------+----+------------+

标签： sqltsql

解决方案

您需要一个状态表，其中至少包含状态描述和（可能）报告顺序：

CREATE TABLE Statuses (
    Status varchar(30) not null,
    ReportOrder int not null,
    ShowInReport bit not null,
    constraint PK_Statuses (Status)
)

现在您可以使用LEFT JOIN从该表到其他未命名表的查询来编写查询（因为您的查询当前声称正在查询数据库而不是其FROM子句中的表：

SELECT Department
       ,s.Status
       ,count(Department) AS "Number in status" 
       ,convert(VARCHAR, getdate(), 103) AS "Date report ran"
FROM
    Statuses s
       left join
    unnamedTable t
       on
          s.Status = t.Status
WHERE s.ShowInReport = 1
       AND Department IN (
              'Department name'
              )
GROUP BY Department,s.Status
ORDER BY s.ReportOrder

Departments如果您仍然希望0行的输出中的部门名称与上述非常相似，那么拥有一个表也可能是谨慎的：

...
FROM
    Statuses s
       cross join
    Departments d
       left join
    unnamedTable t
       on
          s.Status = t.Status and
          d.Department = t.Department
...

（您还应该在未命名表之间有一个 FK，并Statuses确保其中使用的所有状态值都是有效的）

推荐阅读