sql 2000 如何读取不重复数据

jiajiawoaini1314520 2011-10-22 09:19:15

id playTime name forenoticeid2
40102449 2011-10-21 23:45:00.000 MTV国际音乐榜 NULL
40102448 2011-10-21 23:00:00.000 东方夜新闻 75428
40102447 2011-10-21 22:30:00.000 夜新闻(天气) NULL
40102446 2011-10-21 21:18:00.000 《当婆婆遇上妈》包装 75709
40102445 2011-10-21 21:15:00.000 百里挑一 74173
40102444 2011-10-21 21:12:00.000 《当婆婆遇上妈》特别节目（10月21日） 75709
40102443 2011-10-21 20:29:00.000 黄金剧场:当婆婆遇上妈（29） 75709
40102442 2011-10-21 20:23:00.000 《当婆婆遇上妈》包装 75709
40102441 2011-10-21 19:35:00.000 黄金剧场:当婆婆遇上妈（28） 75709

请问这样一组数据，如何根据playTime降序,forenoticeid2大于0，获取不重复的数据
结果应该是
40102448 2011-10-21 23:00:00.000 东方夜新闻 75428
40102446 2011-10-21 21:18:00.000 《当婆婆遇上妈》包装 75709
40102445 2011-10-21 21:15:00.000 百里挑一 74173
40102444 2011-10-21 21:12:00.000 《当婆婆遇上妈》特别节目（10月21日） 75709

请求高手指导

...全文

232 7 打赏收藏转发到动态举报

写回复

用AI写文章

7 条回复

切换为时间正序

请发表友善的回复…

发表回复

--小F-- 2011-10-22

打赏
举报

select

 * 

from

 tb t 

where

 isnull(forenoticeid2,0)>0

and

 id=(select max(id) from tb where name=t.name )

jiajiawoaini1314520 2011-10-22

打赏
举报

[Quote=引用 4 楼 qianjin036a 的回复:]
SQL code

create table [tb] (id int,playTime datetime,name varchar(34),forenoticeid2 int)
insert into [tb]
select 40102449,'2011-10-21 23:45:00.000','MTV国际音乐榜',null union all
select 40102448,'201……
[/Quote]
请问 qianjin036a
select 1 from tb where name=a.name and id>a.id
这句sql可以解释下么？

唐诗三百首 2011-10-22

打赏
举报

sql 2000的写法,



create table tb1 (id int,playTime datetime,name varchar(34),forenoticeid2 int)



insert into tb1

select 40102449,'2011-10-21 23:45:00.000','MTV国际音乐榜',null union all

select 40102448,'2011-10-21 23:00:00.000','东方夜新闻',75428 union all

select 40102447,'2011-10-21 22:30:00.000','夜新闻(天气)',null union all

select 40102446,'2011-10-21 21:18:00.000','《当婆婆遇上妈》包装',75709 union all

select 40102445,'2011-10-21 21:15:00.000','百里挑一',74173 union all

select 40102444,'2011-10-21 21:12:00.000','《当婆婆遇上妈》特别节目(10月21日)',75709 union all

select 40102443,'2011-10-21 20:29:00.000','黄金剧场:当婆婆遇上妈(29)',75709 union all

select 40102442,'2011-10-21 20:23:00.000','《当婆婆遇上妈》包装',75709 union all

select 40102441,'2011-10-21 19:35:00.000','黄金剧场:当婆婆遇上妈(28)',75709



select a.* 

from tb1 a

inner join

(select name,max(playtime) mplaytime

from tb1

where forenoticeid2 is not null and forenoticeid2>0

group by name) b on a.name=b.name and a.playtime=b.mplaytime

order by a.playTime desc



id          playTime                name                               forenoticeid2

----------- ----------------------- ---------------------------------- -------------

40102448    2011-10-21 23:00:00.000 东方夜新闻                              75428

40102446    2011-10-21 21:18:00.000 《当婆婆遇上妈》包装                    75709

40102445    2011-10-21 21:15:00.000 百里挑一                                74173

40102444    2011-10-21 21:12:00.000 《当婆婆遇上妈》特别节目(10月21日)     75709

40102443    2011-10-21 20:29:00.000 黄金剧场:当婆婆遇上妈(29)             75709

40102441    2011-10-21 19:35:00.000 黄金剧场:当婆婆遇上妈(28)             75709

-晴天 2011-10-22

打赏
举报

create table [tb] (id int,playTime datetime,name varchar(34),forenoticeid2 int)

insert into [tb]

select 40102449,'2011-10-21 23:45:00.000','MTV国际音乐榜',null union all

select 40102448,'2011-10-21 23:00:00.000','东方夜新闻',75428 union all

select 40102447,'2011-10-21 22:30:00.000','夜新闻(天气)',null union all

select 40102446,'2011-10-21 21:18:00.000','《当婆婆遇上妈》包装',75709 union all

select 40102445,'2011-10-21 21:15:00.000','百里挑一',74173 union all

select 40102444,'2011-10-21 21:12:00.000','《当婆婆遇上妈》特别节目(10月21日)',75709 union all

select 40102443,'2011-10-21 20:29:00.000','黄金剧场:当婆婆遇上妈(29)',75709 union all

select 40102442,'2011-10-21 20:23:00.000','《当婆婆遇上妈》包装',75709 union all

select 40102441,'2011-10-21 19:35:00.000','黄金剧场:当婆婆遇上妈(28)',75709

go

select * from tb a where isnull(forenoticeid2,0)>0

and not exists(select 1 from tb where name=a.name and id>a.id)

/*

id          playTime                name                               forenoticeid2

----------- ----------------------- ---------------------------------- -------------

40102448    2011-10-21 23:00:00.000 东方夜新闻                              75428

40102446    2011-10-21 21:18:00.000 《当婆婆遇上妈》包装                         75709

40102445    2011-10-21 21:15:00.000 百里挑一                               74173

40102444    2011-10-21 21:12:00.000 《当婆婆遇上妈》特别节目(10月21日)               75709

40102443    2011-10-21 20:29:00.000 黄金剧场:当婆婆遇上妈(29)                    75709

40102441    2011-10-21 19:35:00.000 黄金剧场:当婆婆遇上妈(28)                    75709



(6 行受影响)



*/

go

drop table tb

AcHerat 2011-10-22

打赏
举报



create table [tb] (id int,playTime datetime,name varchar(34),forenoticeid2 int)

insert into [tb]

select 40102449,'2011-10-21 23:45:00.000','MTV国际音乐榜',null union all

select 40102448,'2011-10-21 23:00:00.000','东方夜新闻',75428 union all

select 40102447,'2011-10-21 22:30:00.000','夜新闻(天气)',null union all

select 40102446,'2011-10-21 21:18:00.000','《当婆婆遇上妈》包装',75709 union all

select 40102445,'2011-10-21 21:15:00.000','百里挑一',74173 union all

select 40102444,'2011-10-21 21:12:00.000','《当婆婆遇上妈》特别节目(10月21日)',75709 union all

select 40102443,'2011-10-21 20:29:00.000','黄金剧场:当婆婆遇上妈(29)',75709 union all

select 40102442,'2011-10-21 20:23:00.000','《当婆婆遇上妈》包装',75709 union all

select 40102441,'2011-10-21 19:35:00.000','黄金剧场:当婆婆遇上妈(28)',75709



--开始查询

--SQL2005

;with cte as(

select rid=ROW_NUMBER() over (PARTITION by name order by playTime desc),* 

from tb 

where ISNULL(forenoticeid2,0)>0

)

select id,playTime,name,forenoticeid2 from cte where rid=1

order by 2 desc





--SQL2000

select *

from tb t

where not exists (select 1 from tb where name = t.name and playTime > t.playTime)

	and forenoticeid2 is not null

order by playTime desc



--结束查询

drop table [tb]



/*****************



id          playTime                name                               forenoticeid2

----------- ----------------------- ---------------------------------- -------------

40102448    2011-10-21 23:00:00.000 东方夜新闻                              75428

40102446    2011-10-21 21:18:00.000 《当婆婆遇上妈》包装                         75709

40102445    2011-10-21 21:15:00.000 百里挑一                               74173

40102444    2011-10-21 21:12:00.000 《当婆婆遇上妈》特别节目(10月21日)               75709

40102443    2011-10-21 20:29:00.000 黄金剧场:当婆婆遇上妈(29)                    75709

40102441    2011-10-21 19:35:00.000 黄金剧场:当婆婆遇上妈(28)                    75709



(6 行受影响)

geniuswjt 2011-10-22

打赏
举报

好吧，我没看到2000，楼下写吧，思路同理，我匿了[Quote=引用 1 楼 geniuswjt 的回复:]
SQL code

--lz的需求和结果不符，正解如下：

--> 测试数据: [tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb] (id int,playTime datetime,name varchar(34),forenoticeid2 int)
insert into [tb]
s……
[/Quote]

geniuswjt 2011-10-22

打赏
举报



--lz的需求和结果不符，正解如下：



--> 测试数据: [tb]

if object_id('[tb]') is not null drop table [tb]

create table [tb] (id int,playTime datetime,name varchar(34),forenoticeid2 int)

insert into [tb]

select 40102449,'2011-10-21 23:45:00.000','MTV国际音乐榜',null union all

select 40102448,'2011-10-21 23:00:00.000','东方夜新闻',75428 union all

select 40102447,'2011-10-21 22:30:00.000','夜新闻(天气)',null union all

select 40102446,'2011-10-21 21:18:00.000','《当婆婆遇上妈》包装',75709 union all

select 40102445,'2011-10-21 21:15:00.000','百里挑一',74173 union all

select 40102444,'2011-10-21 21:12:00.000','《当婆婆遇上妈》特别节目(10月21日)',75709 union all

select 40102443,'2011-10-21 20:29:00.000','黄金剧场:当婆婆遇上妈(29)',75709 union all

select 40102442,'2011-10-21 20:23:00.000','《当婆婆遇上妈》包装',75709 union all

select 40102441,'2011-10-21 19:35:00.000','黄金剧场:当婆婆遇上妈(28)',75709



--开始查询

;with cte as(

select rid=ROW_NUMBER() over (PARTITION by name order by playTime desc),* 

from tb 

where ISNULL(forenoticeid2,0)>0

)

select id,playTime,name,forenoticeid2 from cte where rid=1

order by 2 desc



--结束查询

drop table [tb]



/*

id          playTime                name                               forenoticeid2

----------- ----------------------- ---------------------------------- -------------

40102448    2011-10-21 23:00:00.000 东方夜新闻                              75428

40102446    2011-10-21 21:18:00.000 《当婆婆遇上妈》包装                         75709

40102445    2011-10-21 21:15:00.000 百里挑一                               74173

40102444    2011-10-21 21:12:00.000 《当婆婆遇上妈》特别节目(10月21日)               75709

40102443    2011-10-21 20:29:00.000 黄金剧场:当婆婆遇上妈(29)                    75709

40102441    2011-10-21 19:35:00.000 黄金剧场:当婆婆遇上妈(28)                    75709



(6 行受影响)