昨天发的这个帖子:http://topic.csdn.net/u/20100407/15/1af60691-1b11-4a0a-bf85-6187add9fb4f.html
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(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请问怎么把子查询合并起来,谢谢。
max(class1+class2) as maxSum
from tb1
group by tId以上假设class1 和 class2都不能为Null值
DROP TABLE tb1
GO
create table tb1
(
tId int,
tName nvarchar(20),
class1 int,
class2 int
)
insert into tb1
select 1,'test',10,20
union all
select 1,'test1',20,35
union all
select 1,'test2',30,30
union all
select 2,'test21',30,85
union all
select 2,'test22',50,75
union all
select 3,'test31',60,65
union all
select 3,'test32',70,70select tid,tname,
case when class1>class2 then class1 else class2 end maxclass,
(select max(class1+class2) from tb1 where tid=t.tid) maxSum
from tb1 t
where tname =(select top 1 tname from tb1 where tId=t.tId order by case when class1>class2 then class1 else class2 end desc)
/*
tid tname maxclass maxSum
----------- -------------------- ----------- -----------
1 test1 35 60
2 test21 85 125
3 test32 70 140(3 行受影响)
*/
(
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
那又怎么解决了。
DROP TABLE tb1
GO
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
select tid,tname,
case when case when class1>class2 then class1 else class2 end>class3 then case when class1>class2 then class1 else class2 end else class3 end maxclass,
(select max(class1+class2+class3) from tb1 where tid=t.tid) maxSum
from tb1 t
where tname =(select top 1 tname from tb1 where tId=t.tId order by case when case when class1>class2 then class1 else class2 end>class3 then case when class1>class2 then class1 else class2 end else class3 end desc)
/*
tid tname maxclass maxSum
----------- -------------------- ----------- -----------
1 test2 40 100
2 test22 90 215
3 test32 70 190(3 行受影响)
*/
if object_id('tb1') is not null drop table tb1
go
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--select * from tb1declare @sql varchar(8000)select @sql = isnull(@sql+' union all
','')+'select tid,tname,' + name + ' as maxclass from tb1'
from syscolumns
where id = object_id('tb1')
and name not in ('tid','tname')
select @sql = 'select distinct * ,(select max(maxclass) from
(select tid,tname,sum(maxclass) as maxclass
from ('+@sql+') f group by tid,tname) h where tid = t.tid) as maxnum
from (' + @sql + ') t
where maxclass in (select top 1 maxclass from ('+@sql+') r where tid = t.tid
order by maxclass desc)'exec(@sql)tid tname maxclass maxnum
----------- -------------------- ----------- -----------
1 test2 40 100
2 test22 90 215
3 test32 70 190
这里主要是两和求最大,如果有n个字段求和了,上面的方案都不行。/*
tid tname maxclass maxSum1 maxsum2
----------- -------------------- ----------- -----------
1 test2 40 100 值
2 test22 90 215 值
3 test32 70 190 值(3 行受影响)
*/
--1
select tId,max(class1) maxSingle
from(
select tId,class1 from tb1 union all
select tId,class2 from tb1 union all
select tId,class3 from tb1) tt
group by tId
--2
select tId,max(cc1) maxTwoSum
from(
select tId,class1+class2 cc1 from tb1 union all
select tId,class2+class3 cc2 from tb1 union all
select tId,class3+class1 cc3 from tb1) tt
group by tId
--3
select tId,max(cc1) maxThreeSum
from(
select tId,class1+class2+class3 cc1 from tb1
) tt
group by tId
请问怎么拼在一起了,显示效果如6楼。
select a.tId,max(CASE WHEN class1>class2 THEN class1 ELSE class2 END) max1,tb2.max2,
max(class1+class2+class3) max3
from tb1 a inner join
(select tId,max(max22) max2 from
(select tId,class1+class2 max22 from tb1 union all
select tId,class2+class3 max23 from tb1 union all
select tId,class3+class1 max24 from tb1
) tt group by tt.tId
) tb2 on tb2.tId=a.tId
group by a.tId,tb2.max2
数据显示结果如下:
max1:单项最大值
max2:任意两项和最大值
max3:三项和最大值tId max1 max2 max3
1 35 70 100
2 85 165 215
3 70 140 190