T-SQL查询，用于计数表中出现的不同条件。

Question

DBMS: Server 2008

我有一个表格，它的结构如下，代表不同公司的某一供应商的投标申请及其申请状况。决定R=拒绝，A=接受。

---------------------------------------------------------------
| ID | Company    | ApplicationDate | Decision | DecisionDate |
---------------------------------------------------------------
| 1  | ABC        | 15/03/2011      | A        | 17/04/2011   |
| 2  | ABC        | 23/05/2012      | R        | 01/03/2014   |
| 3  | XYZ        | 14/07/2012      | R        | 20/07/2012   |
| 4  | ABC        | 18/01/2013      | A        | 24/02/2013   |
| 5  | XYZ        | 12/08/2013      | R        | 11/09/2013   |
| 6  | ABC        | 30/09/2013      | R        | 14/10/2013   |
| 7  | ABC        | 08/01/2014      | A        | 08/06/2014   |
| 8  | ABC        | 10/05/2014      | A        | 19/05/2014   |
---------------------------------------------------------------

*日期采用时间戳格式。示例表(dd/mm/yyyy)中的日期仅用于表示目的。

我需要从这个简单的数据库中挖掘，

1. 在过去12个月中申请的投标书数目-假定2014年7月11日为当前日期。
2. 在过去12个月内被拒绝投标的数目。
3. 自上次投标申请以来的几个月内。
4. 上一次拒绝投标已经过去几个月了。
5. 过去12个月在ABC申请的投标数量。
6. 美国广播公司在过去12个月中拒绝投标的数量。
7. 自上一次向ABC公司提出投标申请以来的几个月内。
8. 自从ABC最后一次拒绝投标以来，几个月以来的时间。

因此根据给定的表数据，统计数据将是，

1. 四。( in 5、6、7及8的申请日期为今日12个月内)
2. 三个( is 2、5和6的决定日期为12个月，决定为R)
3. 两次(2014年5月10日至今)
4. 四(2014年03月1日，第二组被驳回)
5. 三(ID 6、7和8)
6. 二(ID 2和6)
7. 两次(2014年5月10日至今)
8. 四(2014年03月1日，第二组被驳回)

是否有一种方法可以使用表上的单个查询获得这些统计数据(可能通过使用case的求和)？

到目前为止，我的情况如下。

SELECT
  SUM(CASE WHEN DATEDIFF(MM, ApplicationDate, GETDATE()) <= 12 THEN 1 ELSE 0 END) 'Total Tenders',
  SUM(CASE WHEN DATEDIFF(MM, DecisionDate, GETDATE()) <= 12 AND DECISION = R THEN 1 ELSE 0 END) 'Total Rejects'
  SUM(CASE WHEN DATEDIFF(MM, ApplicationDate, GETDATE()) <= 12 AND Company = 'ABC' THEN 1 ELSE 0 END) 'Total Tenders To ABC',
  SUM(CASE WHEN DATEDIFF(MM, DecisionDate, GETDATE()) <= 12 AND DECISION = R AND Company = 'ABC' THEN 1 ELSE 0 END) 'Total Rejects By ABC'
FROM TenderTable;

这给了我所需的1，2，5和6的数据。

Answer 1

您可以使用WITH ROLLUP

SET DATEFORMAT 'dmy'

DECLARE @tbl TABLE (ID INT, Company VARCHAR(3), ApplicationDate DATE, Decision CHAR(1), DecisionDate DATE)

INSERT INTO @tbl 
    (ID, Company, ApplicationDate, Decision, DecisionDate)
VALUES
    (1,'ABC','15/03/2011','A','17/04/2011'),
    (2,'ABC','23/05/2012','R','01/03/2014'),
    (3,'XYZ','14/07/2012','R','20/07/2012'),
    (4,'ABC','18/01/2013','A','24/02/2013'),
    (5,'XYZ','12/08/2013','R','11/09/2013'),
    (6,'ABC','30/09/2013','R','14/10/2013'),
    (7,'ABC','08/01/2014','A','08/06/2014'),
    (8,'ABC','10/05/2014','A','19/05/2014')

SELECT
    Company                          = CASE WHEN (GROUPING(Company) = 1) THEN 'ALL' ELSE ISNULL(Company, 'UNKNOWN') END,
    TendersApplied                   = SUM(CASE WHEN ApplicationDate >= DATEADD(M, -12, CAST(GETDATE() AS DATE)) THEN 1 END),
    TendersRejected                  = SUM(CASE WHEN DecisionDate >= DATEADD(M, -12, CAST(GETDATE() AS DATE)) AND Decision = 'R' THEN 1 END),
    MonthsSinceLastTenderApplication = DATEDIFF(M, MAX(ApplicationDate), GETDATE()),
    MonthsSinceLastTenderRejection   = DATEDIFF(M, MAX(CASE WHEN Decision = 'R' THEN DecisionDate END), GETDATE())
FROM @tbl
GROUP BY Company
WITH ROLLUP
HAVING GROUPING(Company) = 1
OR Company = 'ABC'
ORDER BY GROUPING(Company), Company

产

Company TendersApplied TendersRejected MonthsSinceLastTenderApplication MonthsSinceLastTenderRejection
------- -------------- --------------- -------------------------------- ------------------------------
ABC     3              2               2                                4
ALL     4              3               2                                4

由发问者编辑:对上述查询的修改满足了要求。

SELECT
    TendersApplied                      = SUM(CASE WHEN ApplicationDate >= DATEADD(M, -12, CAST(GETDATE() AS DATE)) THEN 1 END),
    TendersRejected                     = SUM(CASE WHEN DecisionDate >= DATEADD(M, -12, CAST(GETDATE() AS DATE)) AND Decision = 'R' THEN 1 END),
    MonthsSinceLastTenderApplication    = DATEDIFF(M, MAX(ApplicationDate), GETDATE()),
    MonthsSinceLastTenderRejection      = DATEDIFF(M, MAX(CASE WHEN Decision = 'R' THEN DecisionDate END), GETDATE()),
    TendersAppliedABC                   = SUM(CASE WHEN Company = 'ABC' AND ApplicationDate >= DATEADD(M, -12, CAST(GETDATE() AS DATE)) THEN 1 END),
    TendersRejectedABC                  = SUM(CASE WHEN Company = 'ABC' AND DecisionDate >= DATEADD(M, -12, CAST(GETDATE() AS DATE)) AND Decision = 'R' THEN 1 END),
    MonthsSinceLastTenderApplicationABC = DATEDIFF(M, MAX(CASE WHEN Company = 'ABC' THEN ApplicationDate END), GETDATE()),
    MonthsSinceLastTenderRejectionABC   = DATEDIFF(M, MAX(CASE WHEN Company = 'ABC' AND Decision = 'R' THEN DecisionDate END), GETDATE())
FROM @tbl

Answer 2

你可能忽略了一个简单的方式，这是不明显的，如果你没有见过它。

例如，我有一个名为StateCounty的表，它具有州和县数据

select count(*) as TotalCounties
, (select count(*) from StateCounty where StateCode = 'AK') as AlaskaCounties
, (select count(*) from StateCounty where StateCode = 'TX') as TexasCounties
, (select count(*) from StateCounty where County = 'Marion') as CountiesNamedMarion
, (select count(*) from StateCounty where County = 'Washington') as CountiesNamedWashington
from StateCounty

产生输出

TotalCounties AlaskaCounties TexasCounties CountiesNamedMarion CountiesNamedWashington
------------- -------------- ------------- ------------------- -----------------------
3131          17             254           17                  31

(1行受影响)

我相信你现在可以写你想要的查询。