;with t as(select d.CompanyName, b.BizInfoType, b.Title, b.price,b.PublishTime from YSBiz10_Fct_BizInfo as b inner join YSBiz10_Fct_User as d on b.UserID=d.UserID inner join YSBiz10_Rel_UserFavorites as t on b.UserID=t.FavoriteUserID where t.UserID='3678c81f-fbe4b83-90c4-e27aa01d1571' and t.FavoriteUserID=b.UserID ) select * from t tt where not exists(select 1 from t where CompanyName=t.CompanyName and Title<t.Title)
表 :(省略几个字段) CompanyName timea 2010/8/1 a 2010/8/9 d 2010/8/3语句: with data as ( select ROW_NUMBER() over (order by b.UserID) as ID, d.CompanyName, b.BizInfoType, b.Title, b.price,b.PublishTime from YSBiz10_Fct_BizInfo as b inner join YSBiz10_Fct_User as d on b.UserID=d.UserID inner join YSBiz10_Rel_UserFavorites as t on b.UserID=t.FavoriteUserID where t.UserID='3678c81f-fbe4b83-90c4-e27aa01d1571' and t.FavoriteUserID=b.UserID ) select * from data where ID in (select MIN(ID) from data group by CompanyName ) 结果集: a 2010/8/1 d 2010/8/3 可是我想得到时间(time)最近的不重复的记录: a 2010/8/9 d 2010/8/3 请问怎么改
use test go if object_id('test.dbo.tb') is not null drop table tb -- 创建数据表 create table tb ( CompanyName char(2), time datetime ) go --插入测试数据 insert into tb select 'a','2010/8/1' union all select 'a','2010/8/9' union all select 'd','2010/8/3' go --代码实现select * from tb t where not exists(select 1 from tb where CompanyName=t.CompanyName and time>t.time)/*测试结果CompanyName time ---------------------------------- a 2010-08-09 00:00:00.000 d 2010-08-03 00:00:00.000(2 行受影响) */select CompanyName,time=convert(varchar(10),time,111) from tb t where not exists(select 1 from tb where CompanyName=t.CompanyName and time>t.time)/*测试结果CompanyName time ----------------------- a 2010/08/09 d 2010/08/03(2 行受影响) */
;with t as(select d.CompanyName, b.BizInfoType, b.Title, b.price,b.PublishTime
from YSBiz10_Fct_BizInfo as b
inner join YSBiz10_Fct_User as d on b.UserID=d.UserID
inner join YSBiz10_Rel_UserFavorites as t on b.UserID=t.FavoriteUserID
where t.UserID='3678c81f-fbe4b83-90c4-e27aa01d1571' and t.FavoriteUserID=b.UserID )
select * from t tt where not exists(select 1 from t where CompanyName=t.CompanyName and Title<t.Title)
CompanyName timea 2010/8/1
a 2010/8/9
d 2010/8/3语句:
with data as
(
select ROW_NUMBER() over (order by b.UserID) as ID, d.CompanyName, b.BizInfoType, b.Title, b.price,b.PublishTime from YSBiz10_Fct_BizInfo as b
inner join YSBiz10_Fct_User as d on b.UserID=d.UserID
inner join YSBiz10_Rel_UserFavorites as t on b.UserID=t.FavoriteUserID
where t.UserID='3678c81f-fbe4b83-90c4-e27aa01d1571' and t.FavoriteUserID=b.UserID
)
select * from data where ID in (select MIN(ID) from data group by CompanyName )
结果集:
a 2010/8/1
d 2010/8/3
可是我想得到时间(time)最近的不重复的记录:
a 2010/8/9
d 2010/8/3
请问怎么改
go
if object_id('test.dbo.tb') is not null drop table tb
-- 创建数据表
create table tb
(
CompanyName char(2),
time datetime
)
go
--插入测试数据
insert into tb select 'a','2010/8/1'
union all select 'a','2010/8/9'
union all select 'd','2010/8/3'
go
--代码实现select * from tb t where not exists(select 1 from tb where CompanyName=t.CompanyName and time>t.time)/*测试结果CompanyName time
----------------------------------
a 2010-08-09 00:00:00.000
d 2010-08-03 00:00:00.000(2 行受影响)
*/select CompanyName,time=convert(varchar(10),time,111) from tb t where not exists(select 1 from tb where CompanyName=t.CompanyName and time>t.time)/*测试结果CompanyName time
-----------------------
a 2010/08/09
d 2010/08/03(2 行受影响)
*/