数据表T有下面的数据
AIP PID
1 2
1 2
2 20
2 40
3 60
4 100现在想选出来,相同AIP,取PID的最大的值,如果PID也相同就随便取一个。结果呈现为下面的AIP PID
1 2
2 40
3 60
4 100
AIP PID
1 2
1 2
2 20
2 40
3 60
4 100现在想选出来,相同AIP,取PID的最大的值,如果PID也相同就随便取一个。结果呈现为下面的AIP PID
1 2
2 40
3 60
4 100
where not exists(select * from tb where aip=t.aip and pid>t.pid)
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-11-11 14:29:38
-- 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.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([AIP] int,[PID] int)
insert [tb]
select 1,2 union all
select 1,2 union all
select 2,20 union all
select 2,40 union all
select 3,60 union all
select 4,100
--------------开始查询--------------------------select distinct * from [tb] t where PID=(select max(pid) from tb where aip=t.aip) order by 1
----------------结果----------------------------
/* AIP PID
----------- -----------
1 2
2 40
3 60
4 100(4 行受影响)
*/
insert into @tb
select 1,2 union all
select 1,2 union all
select 2,20 union all
select 2,40 union all
select 3,60 union all
select 4,100select * ,id=identity(int,1,1) into #t from @tbselect aip,pid from #t t
where not exists(select * from #t where aip=t.aip and id>t.id)aip pid
----------- -----------
1 2
2 40
3 60
4 100(4 行受影响)
drop table #t
SELECT AIP,MAX(PID)PID FROM (SELECT DISTINCT * FROM TB)AS T GRUOP BY AIP
楼主的意思就是按AIP分组取最大吧.
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([AIP] int,[PID] int)
insert [tb]
select 1,2 union all
select 1,2 union all
select 2,20 union all
select 2,40 union all
select 3,60 union all
select 4,100
--------------查询SQL--------------------------
select aip, max(pid) from tb group by aip
网上好像还有很多复杂的写法.
有的好几层嵌套,
我都看不明白它的逻辑.
其实简单的SQL就可以实现
select aip,max(PID) as pid from tb group by aip
SELECT AIP, MAX(PID) AS PID FROM T GROUP BY AIP
AIP PID
1 2
1 2
2 20
2 40
3 60
4 100 现在想选出来,相同AIP,取PID的最大的值,如果PID也相同就随便取一个。
-------------------------------------------------------
你不就两个字段吗?PID随不随机取,不还都一样?
数据表T有下面的数据
AIP PID Name
1 2 王三
1 2 王三
2 20 王三
2 40 王三
3 60 王三
4 100 王三
1 2 李四
2 20 李四现在想选王三的数据,相同AIP,取PID的最大的值,如果PID也相同就随便取一个。结果呈现为下面的AIP PID Name
1 2 王三
2 40 王三
3 60 王三
4 100 王三
select aip, max(pid) as pid, '王三' as name from tb where name='王三' group by aip
select aip, max(pid) as pid, '王三' as name from tb where name='王三' group by aip