SQL Server SQL alternative to inner join

7fyelxc5  于 2023-04-19  发布在  其他
关注(0)|答案(4)|浏览(147)

I have a table which has data that appears as:

Staion  Date        Temperature
A      2015-07-31   8
B      2015-07-31   6
C      2015-07-31   8
A      2003-02-21   4
B      2003-02-21   7
C      2003-02-21   7

For each date I need to create arrays so that it has the following combination:

c1 = (A + B)/2, c2 = (A + B + C)/3 and c3 = (B + C)/2

Right I am doing three different inner join on the table itself and doing a final inner join to achieve the following as result:

Date         c1    c2      c3
2015-07-31   7     7.33    7
2003-02-21   5.5   6       7

Is there a cleaner way to do this?

ijxebb2r

ijxebb2r1#

No need for a JOIN , you could simply use a GROUP BY and an aggregation function:

WITH CTE AS
(
    SELECT  [Date],
            MIN(CASE WHEN Staion = 'A' THEN Temperature END) A,
            MIN(CASE WHEN Staion = 'B' THEN Temperature END) B,
            MIN(CASE WHEN Staion = 'C' THEN Temperature END) C
    FROM dbo.YourTable  
    GROUP BY [date]
)
SELECT  [Date],
        (A+B)/2 c1,
        (A+B+C)/3 c2,
        (B+C)/2 c3
FROM CTE;
q3qa4bjr

q3qa4bjr2#

SUM Function is very useful in such cases:

SELECT 
    c1 = SUM(CASE WHEN Staion IN ('A', 'B') THEN Temperature ELSE 0 END) / 2,
    c2 = SUM(Temperature) / 3,
    c3 = SUM(CASE WHEN Staion IN ('B', 'C') THEN Temperature ELSE 0 END) / 2,
    [Date]
FROM dbo.Table
GROUP BY [Date]
ma8fv8wu

ma8fv8wu3#

You can do it with just two joins and almost literally the formulas you've provided:

declare @t table (Station char(1) not null,[Date] date not null, Temperature int not null)
insert into @t(Station,[Date],Temperature) values
('A','20150731',8),
('B','20150731',6),
('C','20150731',8),
('A','20030221',4),
('B','20030221',7),
('C','20030221',7)

select
    B.[Date],
    c1 = (A.Temperature + B.Temperature)/2.0,
    c2 = (A.Temperature + B.Temperature + C.Temperature)/3.0,
    c3 = (B.Temperature + C.Temperature)/2.0
from
    @t B
        inner join
    @t A
        on
            B.[Date] = A.[Date]
        inner join
    @t C
        on
            B.[Date] = C.[Date]
where
    A.Station = 'A' and
    B.Station = 'B' and
    C.Station = 'C'

Result:

Date       c1              c2          c3
---------- --------------- ----------- ----------
2015-07-31 7.000000        7.333333    7.000000
2003-02-21 5.500000        6.000000    7.000000
8tntrjer

8tntrjer4#

You can use pivot and calculation on pivoted data as below:

select [Date], c1 = (A+B)/2.0, c2 = (A+B+C)/3.0, C3 = (B+C)/2.0 from 
( select * from #yourstation ) s
pivot (max(temparature) for station in ([A], [B], [C])) p

Your input table:

create table #yourStation (station char(1), date date, Temparature int)

insert into #yourStation (station, date, Temparature) values
 ('A','2015-07-31',   8     )
,('B','2015-07-31',   6     )
,('C','2015-07-31',   8     )
,('A','2003-02-21',   4     )
,('B','2003-02-21',   7     )
,('C','2003-02-21',   7     )

相关问题