数据库里的表:
ID Title Path
1 523 a
2 523 b
3 523 c
4 522 d
5 522 e
6 521 f
7 520 g
8 520 h
等等。
写sql语句,要求Title为523、522、521、520等等各一行
也就是取出的(select)数据集title不能重复,如果title一样随便踢掉其他几行取其中一行即可。例如取出数据库中下面几行:
1 523 a
4 522 d
6 521 f
7 520 g
不要重复的取出title为523、522、521、520
ID Title Path
1 523 a
2 523 b
3 523 c
4 522 d
5 522 e
6 521 f
7 520 g
8 520 h
等等。
写sql语句,要求Title为523、522、521、520等等各一行
也就是取出的(select)数据集title不能重复,如果title一样随便踢掉其他几行取其中一行即可。例如取出数据库中下面几行:
1 523 a
4 522 d
6 521 f
7 520 g
不要重复的取出title为523、522、521、520
解决方案 »
- 求sql语句,关于查询最新时间和筛选不重复数据
- 笔试的时候碰到了一道题(有点难),具体原题我拿不到了。但大体意思如下,不会有出入。大家帮看下这道sql题目怎么做?
- 最最基础的问题(续)
- 请问:SQL Server的登录帐户(如 sa )密码在哪里修改?
- sql语句数据统计,统计不出来,help
- SQL server 2005 安装问题
- 我用FASTREPOT作的报表,为什么在别的打印机上无法打印?可以预览。在本机默认的打印机上可以打印,其它打印机安装正确
- 怎么能只备份数据不备日志?备完了怎么还原
- sql 超难
- 我在VB中如何动态添加sql server 2000的用户和密码还有权限
- 请问如何将多条查询语句显示在一个窗体中
- 谁有 SQLSERVER7.0 在线等 (只有 20分了 )
insert into t1 select 1,523,'a'
insert into t1 select 2,523,'b'
insert into t1 select 3,523,'c'
insert into t1 select 4,522,'d'
insert into t1 select 5,522,'e'
insert into t1 select 6,521,'f'
insert into t1 select 7,520,'g'
insert into t1 select 8,520,'h'
go
select * from t1 a where not exists(select 1 from t1 where Title=a.Title and ID<a.ID)
/*
ID Title Path
----------- ----------- ----------
1 523 a
4 522 d
6 521 f
7 520 g(4 行受影响)*/
go
drop table t1
min,是最小的.2楼实际查询结果都写在那儿了......
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([ID] int,[Title] int,[Path] nvarchar(1))
Insert #T
select 1,523,N'a' union all
select 2,523,N'b' union all
select 3,523,N'c' union all
select 4,522,N'd' union all
select 5,522,N'e' union all
select 6,521,N'f' union all
select 7,520,N'g' union all
select 8,520,N'h'
Go
--I、[Title]相同ID最小的记录(推荐用1,2,3),--方法3在SQl05时,效率高于1、2
--方法1:
Select * from #T a where not exists(select 1 from #T where [Title]=a.[Title] and ID<a.ID)--方法2:
select a.* from #T a join (select min(ID)ID,[Title] from #T group by [Title]) b on a.[Title]=b.[Title] and a.ID=b.ID--方法3:
select * from #T a where ID=(select min(ID) from #T where [Title]=a.[Title])--方法4:
select a.* from #T a join #T b on a.[Title]=b.[Title] and a.ID>=b.ID group by a.ID,a.[Title],a.[Path] having count(1)=1 --方法5:
select * from #T a group by ID,[Title],[Path] having ID=(select min(ID)from #T where [Title]=a.[Title])--方法6:
select * from #T a where (select count(1) from #T where [Title]=a.[Title] and ID<a.ID)=0--方法7:
select * from #T a where ID=(select top 1 ID from #T where [Title]=a.[Title] order by ID)--方法8:
select * from #T a where ID!>all(select ID from #T where [Title]=a.[Title])--方法9(注:ID为唯一时可用):
select * from #T a where ID in(select min(ID) from #T group by [Title])--SQL2005:--方法10:
select ID,[Title],[Path] from (select *,min(ID)over(partition by [Title]) as MinID from #T a)T where ID=MinID--方法11:select ID,[Title],[Path] from (select *,row_number()over(partition by [Title] order by ID) as MinID from #T a)T where MinID=1
select * from t1 a where not EXISTS
(select 1 from t1 where Title=a.Title and ID<a.ID)SELECT * FROM t1 T
WHERE ID=(SELECT MIN(ID) FROM t1 WHERE TITLE=T.TITLE)