SQL Server : how to combine two select queries from the same table and take the result on Column

Question

我想在具有分组依据的同一个表上合并两个查询。

这是我的 table :

 Date#####   |  Value1 |  Value2 |   Value3 | Type
 ------------------------------------------------------
 23/04/2014  |  1,2    |    12,3 |    10    | Green
 23/04/2014  |  11,2   |    3    |   10,3   | Non-Green    
 24/04/2014  |  10,9   |    3    |    11    | Green
 24/04/2014  |  2,3    |    12,3 |    8     | Green
 24/04/2014  |  10     |    1    |    11    | Non-Green
 25/04/2014  |  10     |    2    |   10,8   | Non-Green    
 25/04/2014  |  1,4    |    5    |    12    | Green

这些值是十进制类型。统计value1,value2,value3>=10，预期结果:

 Date#####   | Green |Non-Green
 ------------------------------
 23/04/2014  |  2    | 2
 24/04/2014  |  3    | 2
 25/04/2014  |  1    | 2

绿色和非绿色的值来自对 value1、value2、value3 进行 >= 10 的计数，这是我的第一个查询:

Dim strCommand As String = "SELECT d, LW, cnt FROM(SELECT TOP 7 [date] AS d, [Type] as LW, SUM(CASE WHEN Value1 >= 10 THEN 1 ELSE 0 END + CASE WHEN Value2 >= 10 THEN 1 ELSE 0 END + CASE WHEN Value3 >= 10 THEN 1 ELSE 0 END) AS cnt FROM tbBooth where Type = 'Green' GROUP BY [date],[Type] ORDER BY [date] DESC) x ORDER BY d ASC"

显示:

 Date#####   | Cnt |Type
 ------------------------
 23/04/2014  |  2    | Green
 24/04/2014  |  3    | Green
 25/04/2014  |  1    | Green

我的第二个查询:

Dim strCommand As String = "SELECT d, LW, cnt FROM(SELECT TOP 7 [date] AS d, [Type] as LW, SUM(CASE WHEN Value1 >= 10 THEN 1 ELSE 0 END + CASE WHEN Value2 >= 10 THEN 1 ELSE 0 END + CASE WHEN Value3 >= 10 THEN 1 ELSE 0 END) AS cnt FROM tbBooth where Type = 'Non-Green' GROUP BY [date],[Type] ORDER BY [date] DESC) x ORDER BY d ASC"

显示:

 Date#####   | Cnt |Type
 ------------------------
 23/04/2014  |  2    | Non-Green
 24/04/2014  |  2    | Non-Green
 25/04/2014  |  2    | Non-Green

我想将两个查询合并为一个，并获取日期、绿色结果和非绿色结果。假设我有很多组日期，我希望它根据 ASC 顺序仅显示最后 7 组日期。

有什么办法吗？我尝试查看其他 SO 帖子，例如使用 UNION 或 PIVOT，但我不知道如何在我的代码中实现它。

抱歉，我昨天已经发布了这个问题，这是一个符合预期结果的编辑......

提前致谢......

Answer 1

SELECT d, 
       sum(CASE WHEN LW = 'Green'     THEN cnt ELSE 0 END) [Green], 
       sum(CASE WHEN LW = 'Non-Green' THEN cnt ELSE 0 END) [Non-Green]
FROM
(
  SELECT [date] AS d, 
  [Type] as LW, 
  CASE WHEN Value1 >= 10 THEN 1 ELSE 0 END +
  CASE WHEN Value2 >= 10 THEN 1 ELSE 0 END + 
  CASE WHEN Value3 >= 10 THEN 1 ELSE 0 END AS cnt,
  DENSE_RANK() over (ORDER BY [date] DESC) dr
  FROM tbBooth 
  WHERE Type in ('Green','Non-Green')
) x 
WHERE dr < 8
GROUP BY d
ORDER BY d ASC