SQL Server:如何将两个select查询组合在一起，并在列中获取结果。

I want to combine two queries on the same table that has group by.

我想要合并具有group by的同一表上的两个查询。

Here is my 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

The values are of Decimal type. Count the value1, value2, value3 >= 10. the expected result:

这些值是十进制的。计算value1、value2、value3 >= 10。预期的结果:

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

The value on green and non-green was from counting >= 10 of value1, value2, value3, here is my first query :

绿色和非绿色的值是从计算>= 10的value1, value2, value3，这里是我的第一个查询:

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"

It display :

它显示:

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

My second query :

我的第二个查询:

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"

It display :

它显示:

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

I want to combine both query to become one, and take the date, result of green and result of non-green. Assume that I have a lot of group of date, I want it to display only the last 7 group of date based on ASC order.

我想将两个查询合并为一个，取日期，绿色的结果和非绿色的结果。假设我有很多日期组，我希望它只显示基于ASC订单的最后7组日期。

Is there anyway how to do it? I tried to look on other SO post, like using UNION or PIVOT but I don't have any clue how to implement it on my code.

怎么做呢?我尝试查看其他的SO post，比如使用UNION或PIVOT，但是我不知道如何在代码上实现它。

Sorry, I already post this question yesterday, this is an edit with the expected result....

对不起,昨天我已经把这问题,这是一个与预期的结果....编辑

Thanks in advance....

提前谢谢....

4 个解决方案

#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

#2

You could use two case statements, one for each value; here I'm splitting the rows up in a common table expression first to simplify the cases somewhat;

您可以使用两个case语句，每个值一个;在这里，我将行分割成一个通用的表表达式，首先简化这些情况;

WITH cte AS (
  SELECT date, type, value1 value FROM tbBooth UNION ALL
  SELECT date, type, value2 value FROM tbBooth UNION ALL
  SELECT date, type, value3 value FROM tbBooth
)
SELECT date d, 
  SUM(CASE WHEN type='Green'     AND value>=10.0 THEN 1 ELSE 0 END) Green,
  SUM(CASE WHEN type='Non-Green' AND value>=10.0 THEN 1 ELSE 0 END) NonGreen
FROM cte
GROUP BY [date] 
ORDER BY [date] DESC

An SQLfiddle to test with.

要测试的SQLfiddle。

#3

try this one query instead of case when for every column...

尝试使用这个查询，而不是对每个列…

Select *
From (
    Select [Date], [Type], COUNT(*) Cnt
    From tbBooth
    UnPivot (Value For Name In ([Value1],[Value2],[Value3])) As UnPvt
    Where Value >= 10
    Group By [Date], [Type]
) As GrpBooth
Pivot (SUM(Cnt) For Type IN ([Green],[Non-Green])) As Pvt

and out put is :

输出是:

Date                    Green       Non-Green
----------------------- ----------- -----------
2014-04-23 00:00:00.000 2           2
2014-04-24 00:00:00.000 3           2
2014-04-25 00:00:00.000 1           2

#4

I have tried to get your required output. Please check it out.

我已经尽力得到你需要的输出。请检查它。

Please let me know if I have missed anything here so that I can correct it.

如果我漏掉了什么，请告诉我，以便我能改正。

--Simulated your table
DECLARE @tbl TABLE
(
[DATE] DATETIME,
Value1 FLOAT,
Value2 FLOAT,
Value3 FLOAT,
[Type] VARCHAR(50)
)

--Entered values for testing
INSERT INTO @tbl([DATE], VALUE1, VALUE2, VALUE3, [TYPE]) VALUES('04/23/2014','1.2','12.3 ','10','Green')
INSERT INTO @tbl([DATE], VALUE1, VALUE2, VALUE3, [TYPE]) VALUES('04/23/2014','11.2','3','10.3','Non-Green')
INSERT INTO @tbl([DATE], VALUE1, VALUE2, VALUE3, [TYPE]) VALUES('04/24/2014','10.9','3','11','Green')
INSERT INTO @tbl([DATE], VALUE1, VALUE2, VALUE3, [TYPE]) VALUES('04/24/2014','2.3','12.3 ','8','Green')
INSERT INTO @tbl([DATE], VALUE1, VALUE2, VALUE3, [TYPE]) VALUES('04/24/2014','10','1','11','Non-Green')
INSERT INTO @tbl([DATE], VALUE1, VALUE2, VALUE3, [TYPE]) VALUES('04/25/2014','10','2','10.8','Non-Green')
INSERT INTO @tbl([DATE], VALUE1, VALUE2, VALUE3, [TYPE]) VALUES('04/25/2014','1.4','5','12','Green')

--your required query
SELECT TOP 7 * FROM
(
    SELECT [date] AS d, 
    SUM(CASE WHEN [Type] = 'Green' AND Value1 >= 10 THEN 1 ELSE 0 END + CASE WHEN [Type] = 'Green' AND Value2 >= 10 THEN 1 ELSE 0 END + CASE WHEN [Type] = 'Green' AND Value3 >= 10 THEN 1 ELSE 0 END) AS Green,
    SUM(CASE WHEN [Type] = 'Non-Green' AND Value1 >= 10 THEN 1 ELSE 0 END + CASE WHEN [Type] = 'Non-Green' AND Value2 >= 10 THEN 1 ELSE 0 END + CASE WHEN [Type] = 'Non-Green' AND Value3 >= 10 THEN 1 ELSE 0 END) AS [Non-Green]   
    FROM @tbl 
    GROUP BY [date]
)x
ORDER BY d ASC

Hope this helps

希望这有助于