mssql Çó¶à×Ö¶Î×î´óÖµ2

×òÌì·¢µÄÕâ¸öÌû×Ó£ºhttp://topic.csdn.net/u/20100407/15/1af60691-1b11-4a0a-bf85-6187add9fb4f.html

SQL code:

select tId,max(class1) maxClass
from(select tId,class1 from tb1 union all select tId,class2 from tb1) tt
group by tId
--2
select tId,max(cc1) maxClass
from(select tId,class1+class2 cc1 from tb1 union all select tId,class1+class2 cc2 from tb1) tt
group by tId



ÏÖÔÚÎÒÒªÏÔʾÕâ¸öµÄ½á¹û
tId tName maxClass maxSum
1 test2 35 60
2 test21 85 125
3 test32 70 140

ÇëÎÊÔõô°Ñ×Ó²éѯºÏ²¢ÆðÀ´£¬Ð»Ð»¡£

---

select tId,max(CASE WHEN class1>class2 THEN class1 ELSE class2 END) as maxClass,
max(class1+class2) as maxSum
from tb1
group by tId

ÒÔÉϼÙÉèclass1 ºÍ class2¶¼²»ÄÜΪNullÖµ

---

±ÈÈçÔÙ¼ÓÒ»¸ö×ֶΣº
SQL code:

create table tb1
(
tId int,
tName nvarchar(20),
class1 int,
class2 int,
class3 int
)
insert into tb1
select 1,'test',10,20,30
union all
select 1,'test1',20,35,25
union all
select 1,'test2',30,30,40
union all
select 2,'test21',30,85,20
union all
select 2,'test22',50,75,90
union all
select 3,'test31',60,65,40
union all
select 3,'test32',70,70,50



ÄÇÓÖÔõô½â¾ö