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 MIN([Hour]) FROM (SELECT [1小时] AS [Hour] UNION ALL SELECT [2小时] AS [Hour] UNION ALL SELECT [3小时] AS [Hour] UNION ALL SELECT [4小时] AS [Hour] UNION ALL SELECT [5小时] AS [Hour] UNION ALL SELECT [6小时] AS [Hour])t ), [最大]=(SELECT Max([Hour]) FROM (SELECT [1小时] AS [Hour] UNION ALL SELECT [2小时] AS [Hour] UNION ALL SELECT [3小时] AS [Hour] UNION ALL SELECT [4小时] AS [Hour] UNION ALL SELECT [5小时] AS [Hour] UNION ALL SELECT [6小时] AS [Hour])t ) FROM C AS a
還是這樣顯示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 */
---测试数据--- 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 行受影响)
调整一下显示格式 ---查询--- ;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 行受影响)
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 MIN([Hour])
FROM (SELECT [1小时] AS [Hour] UNION ALL SELECT [2小时] AS [Hour] UNION ALL SELECT [3小时] AS [Hour] UNION ALL SELECT [4小时] AS [Hour]
UNION ALL SELECT [5小时] AS [Hour] UNION ALL SELECT [6小时] AS [Hour])t
),
[最大]=(SELECT Max([Hour])
FROM (SELECT [1小时] AS [Hour] UNION ALL SELECT [2小时] AS [Hour] UNION ALL SELECT [3小时] AS [Hour] UNION ALL SELECT [4小时] AS [Hour]
UNION ALL SELECT [5小时] AS [Hour] UNION ALL SELECT [6小时] AS [Hour])t
)
FROM
C AS a
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
*/
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 行受影响)
---查询---
;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 行受影响)