日期:2014-05-18  浏览次数:20499 次

横向求和
原始数据

方向 1小时 2小时 3小时 4小时 5小时 6小时
向东 20 25 12 54 12 45
向西 11 45 44 78 54 14

我要求出1-6小时里,东西向 的和哪个是最小的或者是最大的 ?

结果

  【最小】1小时:31 。 【最大】4小时:132 。



------解决方案--------------------
還是這樣顯示

SQL code
use Tempdb
go
--> --> 
 
if not object_id(N'Tempdb..#1') is null
    drop table #1
Go
Create table #1([方向] nvarchar(2),[1小时] int,[2小时] int,[3小时] int,[4小时] int,[5小时] int,[6小时] int)
Insert #1
select N'向东',20,25,12,54,12,45 union all
select N'向西',11,45,44,78,54,14
Go


;WITH C
AS
(Select 
    SUM([1小时]) AS [1小时],
    SUM([2小时]) AS [2小时],
    SUM([3小时]) AS [3小时],
    SUM([4小时]) AS [4小时],
    SUM([5小时]) AS [5小时],                    
    SUM([6小时]) AS [6小时]
from #1 
WHERE [方向] IN(N'向东',N'向西'))--东西向

SELECT 
    [最小]=(SELECT TOP 1 col
            FROM (SELECT N'1小时:'+RTRIM([1小时]) AS col,[1小时] AS [Hour] UNION ALL SELECT N'2小时:'+RTRIM([2小时]) AS col,[2小时] AS [Hour]  UNION ALL SELECT N'3小时:'+RTRIM([3小时]) AS col,[3小时] AS [Hour] 
            UNION ALL SELECT N'4小时:'+RTRIM([4小时]) AS col,[4小时] AS [Hour]     UNION ALL SELECT N'5小时:'+RTRIM([5小时]) AS col,[5小时] AS [Hour] UNION ALL SELECT N'6小时:'+RTRIM([6小时]) AS col,[6小时] AS [Hour])t 
            ORDER BY [Hour] asc
            ),
    [最大]=(SELECT TOP 1 col
            FROM (SELECT N'1小时:'+RTRIM([1小时]) AS col,[1小时] AS [Hour] UNION ALL SELECT N'2小时:'+RTRIM([2小时]) AS col,[2小时] AS [Hour]  UNION ALL SELECT N'3小时:'+RTRIM([3小时]) AS col,[3小时] AS [Hour] 
            UNION ALL SELECT N'4小时:'+RTRIM([4小时]) AS col,[4小时] AS [Hour]     UNION ALL SELECT N'5小时:'+RTRIM([5小时]) AS col,[5小时] AS [Hour] UNION ALL SELECT N'6小时:'+RTRIM([6小时]) AS col,[6小时] AS [Hour])t 
            ORDER BY [Hour] desc
            )
FROM 
    C AS a
    
    /*
    
最小    最大
1小时:31    4小时:132
*/

------解决方案--------------------
SQL code
---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([方向] varchar(4),[1小时] int,[2小时] int,[3小时] int,[4小时] int,[5小时] int,[6小时] int)
insert [tb]
select '向东',20,25,12,54,12,45 union all
select '向西',11,45,44,78,54,14
 
---查询---
;with cte1 as
(
  select '1小时' as tm,sum([1小时]) as sm from tb
  union select '2小时',sum([2小时]) from tb
  union select '3小时',sum([3小时]) from tb
  union select '4小时',sum([4小时]) from tb
  union select '5小时',sum([5小时]) from tb
  union select '6小时',sum([6小时]) from tb
)
select *
from cte1 t
where not exists(select 1 from cte1 where sm<t.sm)
or not exists(select 1 from cte1 where sm>t.sm)

---结果---
tm    sm
----- -----------
1小时   31
4小时   132

(2 行受影响)

------解决方案--------------------
调整一下显示格式
SQL code
---查询---
;with cte1 as
(
  select '1小时' as tm,sum([1小时]) as sm from tb
  union select '2小时',sum([2小时]) from tb
  union select '3小时',sum([3小时]) from tb
  union select '4小时',sum([4小时]) from tb
  union select '5小时',sum([5小时]) from tb
  union select '6小时',sum([6小时]) from tb
)
select '【最小】'+' '+tm+': '+ltrim(sm) as result
from cte1 t
where not exists(select 1 from cte1 where sm<t.sm)
union 
select '【最大】'+' '+tm+': '+ltrim(sm) as result
from cte1 t
where not exists(select 1 from cte1 where sm>t.sm)

---结果---
result
----------------------------
【最大】 4小时: 132
【最小】 1小时: 31

(2 行受影响)