SQL Server将同一个表上的多个查询组合成单个resultset。

I've seen similar questions but can't quite find a solution to this problem. Bear with the mess of tables, as the question should be? fairly straightforward.

我也遇到过类似的问题，但却找不到解决这个问题的办法。问题应该是，把桌子弄得一团糟吧?相当简单。

I have the following table.

我有下表。

LDTime                      Value
2013-07-24 00:00:00.000     14.85
2013-07-24 00:00:00.000     1.339
2013-07-24 00:15:00.000     95.9
2013-07-24 00:15:00.000     1.285
2013-07-24 00:30:00.000     160
2013-07-24 00:30:00.000     0.952

Rows 1,3,and 5 are one type of data. 2, 4, and 6 are another type. I have the following queries which can retrieve the types based on a filter criterion:

行1、3和5是一种数据类型。2 4和6是另一种类型。我有以下查询，可以根据筛选标准检索类型:

SELECT 
    A.LDTime, A.Value As AValue
FROM      
    Table7 G 
INNER JOIN
     Table1 A 
INNER JOIN
     Table2 B ON A.DataSetIdx = B.DataSetIdx 
INNER JOIN
     Table3 C ON B.RetrievalIdx = C.RetrievalIdx ON G.ActionIdx = C.ActionIdx 
INNER JOIN
     Table4 D ON G.SpatialRefIdx = D.SpatialRefIdx 
INNER JOIN
     Table5 E 
INNER JOIN
     Table6 F ON E.StationIdx = F.StationIDx ON D.SpatialRefIdx = E.SpatialRefIdx
WHERE 
     F.StationIDx = 1 
     AND A.ParameterIdx = 18
     AND A.LDTime BETWEEN '2013-7-24' AND '2013-7-25'
ORDER 
     BY A.LDTime


SELECT 
    A.LDTime, A.Value As BValue
FROM
    Table7 G 
INNER JOIN
    Table1 A 
INNER JOIN
    Table2 B ON A.DataSetIdx = B.DataSetIdx 
INNER JOIN
    Table3 C ON B.RetrievalIdx = C.RetrievalIdx ON G.ActionIdx = C.ActionIdx 
INNER JOIN
    Table4 D ON G.SpatialRefIdx = D.SpatialRefIdx 
INNER JOIN
    Table5 E 
INNER JOIN
    Table6 F ON E.StationIdx = F.StationIDx ON D.SpatialRefIdx = E.SpatialRefIdx
WHERE 
    F.StationIDx = 1 
    AND A.ParameterIdx = 19
    AND A.LDTime BETWEEN '2013-7-24' AND '2013-7-25'
ORDER BY 
     A.LDTime

Note that I change only one filter variable (ParameterIdx) between the two queries, which distinguishes the type of data stored in the value column.

注意，我只更改了两个查询之间的一个filter变量(ParameterIdx)，它区分了存储在值列中的数据类型。

How can I combine the queries to produce one resultset? (Alternatively, I am using stored procedures, so this could be done in multiple queries).

如何组合查询以生成一个结果集?(或者，我使用的是存储过程，因此可以在多个查询中完成)。

LDTime                      AValue   BValue
2013-07-24 00:00:00.000     14.85    1.339
2013-07-24 00:15:00.000     95.9     1.285
2013-07-24 00:30:00.000     160      0.952

2 个解决方案

#1

This should do it:

这应该这样做:

SELECT a.LDTime
     , Avalue = MAX(CASE WHEN A.ParameterIdx = 18 THEN A.Value END)
     , BValue = MAX(CASE WHEN A.ParameterIdx = 19 THEN A.Value END)
FROM         Table7 G INNER JOIN
Table1 A INNER JOIN
Table2 B ON A.DataSetIdx = B.DataSetIdx INNER JOIN
Table3 C ON B.RetrievalIdx = C.RetrievalIdx ON G.ActionIdx = C.ActionIdx INNER JOIN
Table4 D ON G.SpatialRefIdx = D.SpatialRefIdx INNER JOIN
Table5 E INNER JOIN
Table6 F ON E.StationIdx = F.StationIDx ON D.SpatialRefIdx = E.SpatialRefIdx
WHERE F.StationIDx = 1 AND A.ParameterIdx in (18, 19)
AND A.LDTime BETWEEN '2013-7-24' AND '2013-7-25'
GROUP BY A.LDTime
ORDER BY A.LDTime

#2

The only difference appears to be the where clause. So the following should label the rows correctly:

唯一的区别似乎是where子句。因此，下面应该正确地标记行:

SELECT A.LDTime,A.Value,
       (case when F.StationIDx = 1 AND A.ParameterIdx = 18 then 'A'
             when A.ParameterIdx = 19 then 'B'
        end) as AorB
FROM         Table7 G INNER JOIN
Table1 A INNER JOIN
Table2 B ON A.DataSetIdx = B.DataSetIdx INNER JOIN
Table3 C ON B.RetrievalIdx = C.RetrievalIdx ON G.ActionIdx = C.ActionIdx INNER JOIN
Table4 D ON G.SpatialRefIdx = D.SpatialRefIdx INNER JOIN
Table5 E INNER JOIN
Table6 F ON E.StationIdx = F.StationIDx ON D.SpatialRefIdx = E.SpatialRefIdx
WHERE F.StationIDx = 1 AND A.ParameterIdx in (18, 19)
AND A.LDTime BETWEEN '2013-7-24' AND '2013-7-25'
ORDER BY A.LDTime

To do what you want, you need a group by on the query. This gives you the results you want:

要想做你想做的事，你需要一个组来查询。这给了你想要的结果:

SELECT A.LDTime,
       max(case when A.ParameterIdx = 18 then Value end) as AValue,
       max(case when A.ParameterIdx = 19 then Value end) as BValue
FROM         Table7 G INNER JOIN
Table1 A INNER JOIN
Table2 B ON A.DataSetIdx = B.DataSetIdx INNER JOIN
Table3 C ON B.RetrievalIdx = C.RetrievalIdx ON G.ActionIdx = C.ActionIdx INNER JOIN
Table4 D ON G.SpatialRefIdx = D.SpatialRefIdx INNER JOIN
Table5 E INNER JOIN
Table6 F ON E.StationIdx = F.StationIDx ON D.SpatialRefIdx = E.SpatialRefIdx
WHERE F.StationIDx = 1 AND A.ParameterIdx in (18, 19)
AND A.LDTime BETWEEN '2013-7-24' AND '2013-7-25'
group BY A.LDTime
order by 1

#1