原始数据
方向 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 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 行受影响)