select max(a) from (select a from tb union all select b as a from tb union all select c as a from tb )t
---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2010-05-06 16:45:10 -- Version: -- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) -- Nov 24 2008 13:01:59 -- Copyright (c) 1988-2005 Microsoft Corporation -- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3) -- ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([a] int,[b] int,[c] int) insert [tb] select 3,9,5 union all select 5,2,7 union all select 6,3,8 --------------开始查询-------------------------- select max(a) from (select a from tb union all select b as a from tb union all select c as a from tb )t----------------结果---------------------------- /* ----------- 9(1 行受影响)*/
create table #test(a int,b int, c int) insert #test select 3 ,9 ,5 insert #test select 5 ,2 ,7 insert #test select 5 ,2 ,7select max(id) from ( select max(a) as id from #test union all select max(b) as id from #test union all select max(c) as id from #test ) a ----------- 9(1 行受影响)
if object_id('[tb]') is not null drop table [tb] go create table [tb]([a] int,[b] int,[c] int) insert [tb] select 3,9,5 union all select 5,2,7 union all select 6,3,8select max([a]) [a] FROM ( select [a] from [tb] union all select [b] from [tb] union all select [c] from [tb] ) tt ----------- 9(1 行受影响)
---------------------------------------------------------------------------------- -- Author : htl258(Tony) -- Date : 2010-05-06 16:44:30 -- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) -- Jul 9 2008 14:43:34 -- Copyright (c) 1988-2008 Microsoft Corporation -- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3) -- Blog : http://blog.csdn.net/htl258 ------------------------------------------------------------------------------------> 生成测试数据表: [tb] IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb] GO CREATE TABLE [tb] ([a] [int],[b] [int],[c] [int]) INSERT INTO [tb] SELECT '3','9','5' UNION ALL SELECT '5','2','7' UNION ALL SELECT '6','3','8'--SELECT * FROM [tb]-->SQL查询如下: select max(value) maxnum from tb unpivot(value for type in(a,b,c)) b /* maxnum ----------- 9(1 行受影响) */
select max(case when a>=b and a>=c then a when b>=a and b>=c then b else c end )as id from #test 还是这个吧
---------------------------------------------------------------------------------- -- Author : htl258(Tony) -- Date : 2010-05-06 16:44:30 -- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) -- Jul 9 2008 14:43:34 -- Copyright (c) 1988-2008 Microsoft Corporation -- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3) -- Blog : http://blog.csdn.net/htl258 ------------------------------------------------------------------------------------> 生成测试数据表: [tb] IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb] GO CREATE TABLE [tb] ([a] [int],[b] [int],[c] [int]) INSERT INTO [tb] SELECT '3','9','5' UNION ALL SELECT '5','2','7' UNION ALL SELECT '6','3','8'--SELECT * FROM [tb]-->SQL查询如下: select max(case when case when a>b then a else b end<c then c else case when a>b then a else b end end) maxnum from tb /* maxnum ----------- 9(1 行受影响) */再来一个
正如htl258的方法运用行转列 select max(value) maxnum from tb unpivot(value for type in(a,b,c)) b 或者如fredrickhu ,使用Unicon你们都快我一步。
create database aaa use aaa create table a ( a int , b int, c int ) insert into a values (3,9,5) insert into a values (5,2,7) insert into a values (6,3,8)select * from aselect max(a) from (select a from a union all select b as a from a union all select c as a from a ) a --2楼的写法 说明:首选union all 没有选择 union 说明你很重视效率哦 select id=max(id) from (select id=max(a) from a union select max(b) from a union select max(c) from a) a--9楼的写法 说明: 测试通过 不过想法挺好的 效率上还差点哦.....select max(case when a>=b and a>=c then a when b>=a and b>=c then b else c end) as id from a --8楼的写法 说明 你的效率应该是不错的select max(case when case when a>b then a else b end<c then c else case when a>b then a else b end end) maxnum from a--7楼的写法 说明 思路和8楼的相似 但是比较的过于繁琐哦.... ------------------------------------------------------------------主要的写法就是这几种了 其中4楼的写法和2楼相近 其他的写法都差不多....... 各位大侠看看我总结的是否有错?请多多指点.................
max(a)
from
(select a from tb
union all
select b as a from tb
union all
select c as a from tb
)t
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-05-06 16:45:10
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([a] int,[b] int,[c] int)
insert [tb]
select 3,9,5 union all
select 5,2,7 union all
select 6,3,8
--------------开始查询--------------------------
select
max(a)
from
(select a from tb
union all
select b as a from tb
union all
select c as a from tb
)t----------------结果----------------------------
/* -----------
9(1 行受影响)*/
insert #test select 3 ,9 ,5
insert #test select 5 ,2 ,7
insert #test select 5 ,2 ,7select max(id) from (
select max(a) as id from #test
union all
select max(b) as id from #test
union all
select max(c) as id from #test
) a
-----------
9(1 行受影响)
go
create table [tb]([a] int,[b] int,[c] int)
insert [tb]
select 3,9,5 union all
select 5,2,7 union all
select 6,3,8select max([a]) [a] FROM
(
select [a] from [tb]
union all
select [b] from [tb]
union all
select [c] from [tb]
) tt
-----------
9(1 行受影响)
-- Author : htl258(Tony)
-- Date : 2010-05-06 16:44:30
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
-- Blog : http://blog.csdn.net/htl258
------------------------------------------------------------------------------------> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([a] [int],[b] [int],[c] [int])
INSERT INTO [tb]
SELECT '3','9','5' UNION ALL
SELECT '5','2','7' UNION ALL
SELECT '6','3','8'--SELECT * FROM [tb]-->SQL查询如下:
select max(value) maxnum from tb unpivot(value for type in(a,b,c)) b
/*
maxnum
-----------
9(1 行受影响)
*/
select max(case when a>=b and a>=c then a
when b>=a and b>=c then b
else c end )as id from #test 还是这个吧
-- Author : htl258(Tony)
-- Date : 2010-05-06 16:44:30
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
-- Blog : http://blog.csdn.net/htl258
------------------------------------------------------------------------------------> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([a] [int],[b] [int],[c] [int])
INSERT INTO [tb]
SELECT '3','9','5' UNION ALL
SELECT '5','2','7' UNION ALL
SELECT '6','3','8'--SELECT * FROM [tb]-->SQL查询如下:
select max(case when case when a>b then a else b end<c then c else case when a>b then a else b end end) maxnum from tb
/*
maxnum
-----------
9(1 行受影响)
*/再来一个
insert #test select 3 ,9 ,5
insert #test select 5 ,2 ,7
insert #test select 5 ,2 ,7
select max(case when a>=b and a>=c then a
when b>=a and b>=c then b
else c end )as id from #test
id
-----------
9(1 行受影响)
select id=max(a) from tb
union
select max(b) from tb
union
select max(c) from tb )t
select max(a) as id from #test
union all
select max(b) as id from #test
union all
select max(c) as id from #test
) a
这个应该效率最高
select max(value) maxnum from tb unpivot(value for type in(a,b,c)) b
或者如fredrickhu ,使用Unicon你们都快我一步。
create database aaa
use aaa
create table a
(
a int ,
b int,
c int
)
insert into a values (3,9,5)
insert into a values (5,2,7)
insert into a values (6,3,8)select * from aselect max(a) from (select a from a union all select b as a from a union all select c as a from a ) a --2楼的写法 说明:首选union all 没有选择 union 说明你很重视效率哦 select id=max(id) from (select id=max(a) from a union select max(b) from a union select max(c) from a) a--9楼的写法 说明: 测试通过 不过想法挺好的 效率上还差点哦.....select max(case when a>=b and a>=c then a when b>=a and b>=c then b else c end) as id from a --8楼的写法 说明 你的效率应该是不错的select max(case when case when a>b then a else b end<c then c else case when a>b then a else b end end) maxnum from a--7楼的写法 说明 思路和8楼的相似 但是比较的过于繁琐哦....
------------------------------------------------------------------主要的写法就是这几种了 其中4楼的写法和2楼相近 其他的写法都差不多.......
各位大侠看看我总结的是否有错?请多多指点.................
就是同样的语句,对待不同的数据布局、数据量 优化器也会生成不同的执行计划
结果同样也会后很大的差异
有兴趣的看看这个
http://blog.csdn.net/leimin/archive/2004/02/04/12896.aspx