create table test(A int,B int) insert test select 2, 4 insert test select 34, 3 select C=case when A>B then A else B end from testdrop table testC ----------- 4 34(2 行受影响)
select case a > b then a else b end c from test
create table test(A int,B int) insert test select 2, 4 insert test select 34, 3select case when a > b then a else b end c from testdrop table test/* c ----------- 4 34(所影响的行数为 2 行) */
每天在SQL区泡着,还能学不少东西! 感谢这些星星们!
我来个不一样的,如果有多列,直接加union就可以了select (select max(a) from(select a as a union all select b )t) as MaxCol from test/** MaxCol ----------- 4 34(所影响的行数为 2 行) **/
select A,B,C = (case when A > B then A else B end) from Test
create table test(A int,B int) insert test select 2, 4 insert test select 34, 3 insert test select 1, 2--?? select max(A) as C from test union all select max(B) from test /* C ----------- 4 34 */ --?? select case when a > b then a else b end c from test /* c ----------- 4 34 2 */ drop table test
来个彪悍一点的,同时对n列数据做n种聚合运算/* 测试名称:利用XML求任意列之间的聚合 测试功能:对一张表的6列数据做min、max、sum和avg运算 运行原理:字段合并为xml后做xquery查询转为行集后聚合 作者:jinjazz(近身剪) */ --建立测试环境 declare @t table( id smallint, a smallint,b smallint, c smallint,d smallint, e smallint,f smallint)insert into @t select 1,1,2,3,4,6,7 union all select 2,34,45,56,54,9,6--测试语句 select a.*,c.* from @t a outer apply( select doc=( select * from @t as doc where id=a.id for xml path(''),type ) )b outer apply( select min(r) as minValue, max(r) as maxValue, sum(r) as sumValue, avg(r) as avgValue from( select cast(cast(d.n.query('text()') as varchar(max)) as int) as r from doc.nodes( '/a,b,c,d,e,f') D(n))tt )c
/*测试结果id a b c d e f minValue maxValue sumValue avgValue ------ ------ ------ ------ ------ ------ ------ ----------- ----------- ----------- ----------- 1 1 2 3 4 6 7 1 7 23 3 2 34 45 56 54 9 6 6 56 204 34 */
insert test select 2, 4
insert test select 34, 3
select C=case when A>B then A else B end from testdrop table testC
-----------
4
34(2 行受影响)
select case a > b then a else b end c from test
insert test select 2, 4
insert test select 34, 3select case when a > b then a else b end c from testdrop table test/*
c
-----------
4
34(所影响的行数为 2 行)
*/
感谢这些星星们!
(select max(a) from(select a as a union all select b )t) as MaxCol
from test/**
MaxCol
-----------
4
34(所影响的行数为 2 行)
**/
from Test
不是的,各有各精彩,有些是用case when做不到或者很难做到的
比如
http://blog.csdn.net/josy/archive/2008/12/25/3603018.aspx
insert test select 2, 4
insert test select 34, 3
insert test select 1, 2--??
select max(A) as C from test
union all
select max(B) from test
/*
C
-----------
4
34
*/
--??
select case when a > b then a else b end c from test
/*
c
-----------
4
34
2
*/
drop table test
测试名称:利用XML求任意列之间的聚合
测试功能:对一张表的6列数据做min、max、sum和avg运算
运行原理:字段合并为xml后做xquery查询转为行集后聚合
作者:jinjazz(近身剪)
*/
--建立测试环境
declare @t table(
id smallint,
a smallint,b smallint,
c smallint,d smallint,
e smallint,f smallint)insert into @t
select 1,1,2,3,4,6,7 union all
select 2,34,45,56,54,9,6--测试语句
select a.*,c.*
from @t a outer apply(
select doc=(
select * from @t as doc where id=a.id for xml path(''),type )
)b
outer apply(
select
min(r) as minValue,
max(r) as maxValue,
sum(r) as sumValue,
avg(r) as avgValue
from(
select cast(cast(d.n.query('text()') as varchar(max)) as int) as r
from doc.nodes( '/a,b,c,d,e,f') D(n))tt
)c
/*测试结果id a b c d e f minValue maxValue sumValue avgValue
------ ------ ------ ------ ------ ------ ------ ----------- ----------- ----------- -----------
1 1 2 3 4 6 7 1 7 23 3
2 34 45 56 54 9 6 6 56 204 34
*/