查询不重复的数据

我在深圳搬砖-Justin 2009-07-17 11:31:08
这样的数据:
---------------------------------
id X XX num
1 ** ** 55
2 ** ** 55
3 ** ** 21
3 ** ** 10

4 * * 111
5 * ( 55
5 * > 45

7 33 jj 4
6 2 v
6 1 v



1.获取不重复的
如果id重复 第一条件按照num判断取最大的
第二条件如果num都为空的话 按照 X判断取最大的
...全文
97 13 打赏 收藏 转发到动态 举报
写回复
用AI写文章
13 条回复
切换为时间正序
请发表友善的回复…
发表回复
  • 打赏
  • 举报
回复
自己Up一个 都去吃午饭了吗?
  • 打赏
  • 举报
回复
这样的数据:
---------------------------------
id X XX num
1 ** ** 55
2 ** ** 55
3 ** ** 21
3 ** ** 10
4 * * 111
5 * ( 55
5 * > 45
7 33 jj 4
6 2 v
6 1 v
8 1 t 5
8 1 t
--其它要求不变
现在时如果一个num为空一个不为空呢?
加分了
feixianxxx 2009-07-17
  • 打赏
  • 举报
回复
if object_id('[tb]') is not null 
drop table [tb]
go
create table [tb]([id] int,[XX] varchar(2),[XX1] varchar(2),[num] int)
insert [tb]
select 1,'**','**',55 union all
select 2,'**','**',55 union all
select 3,'**','**',21 union all
select 3,'**','**',10 union all
select 4,'*','*',111 union all
select 5,'*','(',55 union all
select 5,'*','>',45 union all select
7, '33' , 'jj' , 4 union all select
6 , '2' , 'v' , null union all select
6 , '1' , 'v' ,null
go
select * from tb t where num=(select top 1 num from tb where t.id=id order by num desc)
and xX =(select top 1 xx from tb where id=t.id and num=t.num order by xx desc )
union all
select * from tb t where XX= (select top 1 XX from tb where t.id=id order by XX desc) and num is null

/*
id XX XX1 num
----------- ---- ---- -----------
1 ** ** 55
2 ** ** 55
3 ** ** 21
4 * * 111
5 * ( 55
7 33 jj 4
6 2 v NULL

*/
ks_reny 2009-07-17
  • 打赏
  • 举报
回复
[Quote=引用 4 楼 jijunwu 的回复:]
引用 1 楼 jiangshun 的回复:
SQL codeselect*from TB twherenotexists(select1from TBwhere T.id=idand T.num <num)

X是什么类型的字段?


你的这个只能实现 查询不同的根据num判断
[/Quote]

select * from TB t where not exists(select 1 from TB where T.id=id and T.num <num)
and t.num is not null
union all
select * from TB t where not exists(select 1 from TB where T.id=id and T.X<X)
and t.num is null
  • 打赏
  • 举报
回复
[Quote=引用 1 楼 jiangshun 的回复:]
SQL codeselect*from TB twherenotexists(select1from TBwhere T.id=idand T.num<num)

X是什么类型的字段?
[/Quote]

你的这个只能实现 查询不同的根据num判断
  • 打赏
  • 举报
回复
int
  • 打赏
  • 举报
回复
x in 类型
jiangshun 2009-07-17
  • 打赏
  • 举报
回复
select * from TB t where not exists(select 1 from TB where T.id=id and T.num<num)


X是什么类型的字段?
feixianxxx 2009-07-17
  • 打赏
  • 举报
回复
if object_id('[tb]') is not null 
drop table [tb]
go
create table [tb]([id] int,[XX] varchar(2),[XX1] varchar(2),[num] int)
insert [tb]
select 1,'**','**',55 union all
select 2,'**','**',55 union all
select 3,'**','**',21 union all
select 3,'**','**',10 union all
select 4,'*','*',111 union all
select 5,'*','(',55 union all
select 5,'*','>',45 union all select
7, '33' , 'jj' , 4 union all select
6 , '2' , 'v' , null union all select
6 , '1' , 'v' ,null union all select
8 ,'1' , 't' , 5 union all select
8 , '1' , 't' ,NULL
go
select * from (
select * from tb t where num=(select top 1 num from tb where t.id=id order by num desc)
and xX =(select top 1 xx from tb where id=t.id and num=t.num order by xx desc )
union all
select * from tb t where XX= (select top 1 XX from tb where t.id=id order by XX desc) and num is null) t
where not exists (select * from tb where id=t.id and isnull(t.num,0)<isnull(num,0) )
order by id


/*
id XX XX1 num
----------- ---- ---- -----------
1 ** ** 55
2 ** ** 55
3 ** ** 21
4 * * 111
5 * ( 55
6 2 v NULL
7 33 jj 4
8 1 t 5


*/
feixianxxx 2009-07-17
  • 打赏
  • 举报
回复
if object_id('[tb]') is not null 
drop table [tb]
go
create table [tb]([id] int,[XX] varchar(2),[XX1] varchar(2),[num] int)
insert [tb]
select 1,'**','**',55 union all
select 2,'**','**',55 union all
select 3,'**','**',21 union all
select 3,'**','**',10 union all
select 4,'*','*',111 union all
select 5,'*','(',55 union all
select 5,'*','>',45 union all select
7, '33' , 'jj' , 4 union all select
6 , '2' , 'v' , null union all select
6 , '1' , 'v' ,null union all select
8 ,'1' , 't' , 5 union all select
8 , '1' , 't' ,NULL
go
select * from (
select * from tb t where num=(select top 1 num from tb where t.id=id order by num desc)
and xX =(select top 1 xx from tb where id=t.id and num=t.num order by xx desc )
union all
select * from tb t where XX= (select top 1 XX from tb where t.id=id order by XX desc) and num is null) t
where not exists (select * from tb where id=t.id and isnull(t.num,0)<isnull(num,0) )
order by id


/*
id XX XX1 num
----------- ---- ---- -----------
1 ** ** 55
2 ** ** 55
3 ** ** 21
4 * * 111
5 * ( 55
6 2 v NULL
7 33 jj 4
8 1 t 5


*/
  • 打赏
  • 举报
回复
如果有一个a字段值相等的 memo的值为空的 话那条记录 就查询不出来了
ChinaJiaBing 2009-07-17
  • 打赏
  • 举报
回复
照着写



数据如下:
name val memo
a 2 a2(a的第二个值)
a 1 a1--a的第一个值
a 3 a3:a的第三个值
b 1 b1--b的第一个值
b 3 b3:b的第三个值
b 2 b2b2b2b2
b 4 b4b4
b 5 b5b5b5b5b5
*/
--创建表并插入数据:
create table tb(name varchar(10),val int,memo varchar(20))
insert into tb values('a', 2, 'a2(a的第二个值)')
insert into tb values('a', 1, 'a1--a的第一个值')
insert into tb values('a', 3, 'a3:a的第三个值')
insert into tb values('b', 1, 'b1--b的第一个值')
insert into tb values('b', 3, 'b3:b的第三个值')
insert into tb values('b', 2, 'b2b2b2b2')
insert into tb values('b', 4, 'b4b4')
insert into tb values('b', 5, 'b5b5b5b5b5')
go

--一、按name分组取val最大的值所在行的数据。
--方法1:
select a.* from tb a where val = (select max(val) from tb where name = a.name) order by a.name
--方法2:
select a.* from tb a where not exists(select 1 from tb where name = a.name and val > a.val)
--方法3:
select a.* from tb a,(select name,max(val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name
--方法4:
select a.* from tb a inner join (select name , max(val) val from tb group by name) b on a.name = b.name and a.val = b.val order by a.name
--方法5
select a.* from tb a where 1 > (select count(*) from tb where name = a.name and val > a.val ) order by a.name
/*
name val memo
---------- ----------- --------------------
a 3 a3:a的第三个值
b 5 b5b5b5b5b5
*/

--二、按name分组取val最小的值所在行的数据。
--方法1:
select a.* from tb a where val = (select min(val) from tb where name = a.name) order by a.name
--方法2:
select a.* from tb a where not exists(select 1 from tb where name = a.name and val < a.val)
--方法3:
select a.* from tb a,(select name,min(val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name
--方法4:
select a.* from tb a inner join (select name , min(val) val from tb group by name) b on a.name = b.name and a.val = b.val order by a.name
--方法5
select a.* from tb a where 1 > (select count(*) from tb where name = a.name and val < a.val) order by a.name
/*
name val memo
---------- ----------- --------------------
a 1 a1--a的第一个值
b 1 b1--b的第一个值
*/

  • 打赏
  • 举报
回复
..

22,199

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧