横向求和解决方案

横向求和
原始数据

方向1小时2小时3小时4小时5小时6小时
向东202512541245
向西114544785414

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

结果

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



[解决办法]
還是這樣顯示

SQL code

use Tempdbgo--> -->  if not object_id(N'Tempdb..#1') is null    drop table #1GoCreate table #1([方向] nvarchar(2),[1小时] int,[2小时] int,[3小时] int,[4小时] int,[5小时] int,[6小时] int)Insert #1select N'向东',20,25,12,54,12,45 union allselect N'向西',11,45,44,78,54,14Go;WITH CAS(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]gocreate 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 allselect '向西',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 twhere not exists(select 1 from cte1 where sm<t.sm)or not exists(select 1 from cte1 where sm>t.sm)---结果---tm    sm----- -----------1小时   314小时   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 resultfrom cte1 twhere not exists(select 1 from cte1 where sm<t.sm)union select '【最大】'+' '+tm+': '+ltrim(sm) as resultfrom cte1 twhere not exists(select 1 from cte1 where sm>t.sm)---结果---result----------------------------【最大】 4小时: 132【最小】 1小时: 31(2 行受影响)