//相关sqlcreate 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,70
//单个字段的会,下面是求单个字段的select a.* from tb1 a,(select tId,max(class1) as maxT from tb1 group by tId) b
where a.tId=b.tId and a.class1=b.maxT order by a.tId
//数据显示如下tId tName class1 class2
1 test2 30 30
2 test22 50 75
3 test32 70 70
//我想显示为tId tName maxClass
1 test2 35
2 test22 85
3 test32 70
我想要的结果就是class1,class2不区分,取最大值。//谢谢。
(
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,70
//单个字段的会,下面是求单个字段的select a.* from tb1 a,(select tId,max(class1) as maxT from tb1 group by tId) b
where a.tId=b.tId and a.class1=b.maxT order by a.tId
//数据显示如下tId tName class1 class2
1 test2 30 30
2 test22 50 75
3 test32 70 70
//我想显示为tId tName maxClass
1 test2 35
2 test22 85
3 test32 70
我想要的结果就是class1,class2不区分,取最大值。//谢谢。
FROM (
SELECT TID,TNAME,CLASS1 FROM TB
UNION ALL
SELECT TID,TNAME,CLASS2 FROM TB
)T
GROUP BY TID,TNAME
from
(
select tId,tName,class1 from tb1
union all
select tId,tName,class2 from tb1
) tt
group by tIdtId class1
----------- -----------
1 35
2 85
3 70(3 行受影响)
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,70
select tid,
tname,
max(class1)maxclass
into #
from(
select tid,
tname,
class1
from tb1
union all
select tid,
tname,
class2
from tb1)t
group by tid,tname
select *
from # t
where maxclass=(select max(maxclass)from # where tid=t.tid)
drop table tb1,#
/*
tid tname maxclass
----------- -------------------- -----------
3 test32 70
2 test21 85
1 test1 35(3 個資料列受到影響)
*/
FROM (
SELECT TID,TNAME,CLASS1 FROM TB
UNION ALL
SELECT TID,TNAME,CLASS2 FROM TB
)T
GROUP BY TID最后再按你第一次的连接一次吧
4 3 4 1
3 2 2 1
2 2 3 4
4 4 3 4
3 1 2 2
怎么写代码 去比较lvl1、lvl2、lvl3、lvl4 对应每行的值,取其中最小的,将其值添加到lvl列里
运行结果应该是
lvl
1
1
2
3
1*/--方法(一) 函數法-->Title:Generating test data
-->Author:wufeng4552
-->Date :2009-10-16 09:58:16if not object_id('Tempdb..#t') is null
drop table #t
Go
Create table #t([lvl1] int,[lvl2] int,[lvl3] int,[lvl4] int,[lvl] int)
Insert #t
select 4,3,4,1,null union all
select 3,2,2,1,null union all
select 2,2,3,4,null union all
select 4,4,3,4,null union all
select 3,1,2,2,null
Go
if object_id('UF_minget')is not null drop function UF_minget
go
create function UF_minget
(@col1 int,@col2 int,@col3 int,@col4 int)
returns int
as
begin
declare @t table(col int)
insert @t select @col1 union all
select @col2 union all
select @col3 union all
select @col4
return(select min(col)from @t)
end
go
update t set [lvl]=dbo.UF_minget([lvl1],[lvl2],[lvl3],[lvl4])
from #t t
select * from #t
/*
lvl1 lvl2 lvl3 lvl4 lvl
----------- ----------- ----------- ----------- -----------
4 3 4 1 1
3 2 2 1 1
2 2 3 4 2
4 4 3 4 3
3 1 2 2 1(5 個資料列受到影響)
*/--方法二 MSSQL2005 XML PATH-------------------------------------
-- Author : liangCK 梁爱兰
-- Comment: 小梁 爱 兰儿
-- Date : 2009-10-16 09:57:38
---------------------------------------> 生成测试数据: @T
DECLARE @T TABLE (lvl1 int,lvl2 int,lvl3 int,lvl4 int,lvl int)
INSERT INTO @T
SELECT 4,3,4,1,null UNION ALL
SELECT 3,2,2,1,null UNION ALL
SELECT 2,2,3,4,null UNION ALL
SELECT 4,4,3,4,null UNION ALL
SELECT 3,1,2,2,null--SQL查询如下:UPDATE A SET
lvl = B.x.value('min(//row/*)','int')
FROM @T AS A
CROSS APPLY (SELECT x = (SELECT A.* FOR XML PATH('row'),TYPE)) AS B;
SELECT * FROM @T;/*
lvl1 lvl2 lvl3 lvl4 lvl
----------- ----------- ----------- ----------- -----------
4 3 4 1 1
3 2 2 1 1
2 2 3 4 2
4 4 3 4 3
3 1 2 2 1(5 行受影响)*/
--方法(三) 作者 (四方城) if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([lvl1] int,[lvl2] int,[lvl3] int,[lvl4] int,[lvl] int)
insert [tb]
select 4,3,4,1,null union all
select 3,2,2,1,null union all
select 2,2,3,4,null union all
select 4,4,3,4,null union all
select 3,1,2,2,null
gocreate function getmin(@a varchar(8000))
returns int
as
begin declare @ table (id int identity,a char(1))
declare @t int
insert @ select top 8000 null from sysobjects a,sysobjects b
select @t=min(cast(substring(','+@a,id+1,charindex(',',','+@a+',',id+1)-id-1) as int))
from @ where substring(','+@a,id,8000) like ',_%'
return @t
end
go-->查询
select
lvl1,
lvl2,
lvl3,
lvl4,
lvl=dbo.getmin(ltrim(lvl1)+','+ltrim(lvl2)+','+ltrim(lvl3)+','+ltrim(lvl4))
from tb/**
lvl1 lvl2 lvl3 lvl4 lvl
----------- ----------- ----------- ----------- -----------
4 3 4 1 1
3 2 2 1 1
2 2 3 4 2
4 4 3 4 3
3 1 2 2 1(5 行受影响)
**/--方法(四)-->Title:Generating test data
-->Author:wufeng4552
-->Date :2009-10-16 09:58:16if not object_id('Tempdb..#t') is null
drop table #t
Go
Create table #t([lvl1] int,[lvl2] int,[lvl3] int,[lvl4] int,[lvl] int)
Insert #t
select 4,3,4,1,null union all
select 3,2,2,1,null union all
select 2,2,3,4,null union all
select 4,4,3,4,null union all
select 3,1,2,2,null
Goif object_id('UF_minget')is not null drop function UF_minget
go
create function UF_minget
(@s varchar(200))
returns int
as
begin
return(
select col=min(substring(@s,number,charindex(',',@s+',',number)-number))
from master..spt_values
where type='p' and number<=len(@s+'a') and charindex(',',','+@s,number)=number)
end
go
select
[lvl1],
[lvl2],
[lvl3],
[lvl4],
[lvl]=dbo.UF_minget(ltrim([lvl1])+','+ltrim([lvl2])+','+ltrim([lvl3])+','+ltrim([lvl4]))
from #T
/*
lvl1 lvl2 lvl3 lvl4 lvl
----------- ----------- ----------- ----------- -----------
4 3 4 1 1
3 2 2 1 1
2 2 3 4 2
4 4 3 4 3
3 1 2 2 1*/--方法(五)-->Title:Generating test data
-->Author:wufeng4552
-->Date :2009-10-16 09:58:16
if not object_id('Tempdb..#t') is null
drop table #t
Go
Create table #t([lvl1] int,[lvl2] int,[lvl3] int,[lvl4] int,[lvl] int)
Insert #t
select 4,3,4,1,null union all
select 3,2,2,1,null union all
select 2,2,3,4,null union all
select 4,4,3,4,null union all
select 3,1,2,2,null
Go
select [lvl1],
[lvl2],
[lvl3],
[lvl4],
[lvl]=(select min([lvl1])
from (select [lvl1]
union all select [lvl2]
union all select [lvl3]
union all select [lvl4])T)
from #t
/*
lvl1 lvl2 lvl3 lvl4 lvl
----------- ----------- ----------- ----------- -----------
4 3 4 1 1
3 2 2 1 1
2 2 3 4 2
4 4 3 4 3
3 1 2 2 1(5 個資料列受到影響)
*/本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/fredrickhu/archive/2009/10/16/4682241.aspx
from tb1 a,
(select tId,max(case when class1>class2 then class1 else class2 end) as maxT from tb1 group by tId) b
where a.tId=b.tId and a.class1=b.maxT or a.class2=b.maxT
order by a.tId/**
tId tName maxClass
----------- -------------------- -----------
1 test1 35
2 test21 85
3 test32 70(3 行受影响)**/
(
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 * from tb1
tId tName maxClass
1 test2 35
2 test22 85
3 test32 70select distinct T1.tID,max(T1.tName) tName,max(T2.class1) class1 from tb1 T1
join
(
select tId,max(class1) class1
from
(
select tId,tName,class1 from tb1
union all
select tId,tName,class2 from tb1
) tt
group by tId
) T2
on T1.tID=T2.tID
group by T1.tID
tID tName class1
----------- -------------------- -----------
1 test2 35
2 test22 85
3 test32 70(3 行受影响)
(
tId int,
tName nvarchar(20),
class1 int,
class2 int
)
go
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,70
goselect tb1.tId,tb1.tName,class
from tb1,
(
select tId,class=max(case when class1>class2 then class1 else class2 end)
from tb1
group by tId
) tb2
where tb1.tId=tb2.tId and (class1=class or class2=class)
(
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,70--select * from tb1select tid,tname,case when class1>class2 then class1 else class2 end maxclass
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
----------- -------------------- -----------
1 test1 35
2 test21 85
3 test32 70(3 行受影响)
*/drop table tb1
union all
select 3,'test32',70,70
???????有这样的数据怎么办
tid tname maxclass
----------- -------------------- -----------
1 test1 35
2 test21 85
3 test32 70(3 行受影响)
*/