22,206
社区成员
发帖
与我相关
我的任务
分享
create table aa(
id int identity primary key,
bid int,
type varchar(60),
name varchar(60),
value varchar(60),
writedate datetime,
sysdate datetime
)
go
insert into aa
select 1429,'你好','好','aaa','2010-11-2','2010-11-3' union
select 1429,'你好','好','bbb','2010-11-6','2010-11-1' union
select 1429,'你好','不好','ccc','2010-11-2','2010-11-3' union
select 1429,'你好','好','ddd','2010-11-2','2010-11-4' union
select 1429,'你好','不好','eee','2010-11-2','2010-11-2' union
select 1429,'你好','好','fff',null,null union
select 1429,'你好','不好','ggg',null,null union
select 1429,'吃饭','吃','hhh',null,null union
select 1429,'你好','好','iii',null,null union
select 1429,'吃饭','吃','jjj',null,null union
select 1429,'你好','好','kkk',null,null union
select 1430,'喝水','喝','lll',null,null union
select 1430,'喝水',' 不喝','mmm',null,null
go
create table #tb(
id int identity primary key,
bid int,
type varchar(20),
name varchar(20),
value varchar(20),
writedate datetime,
sysdate datetime
)
go
insert into #tb
select 1429,'你好','好','aaa','2010-11-2','2010-11-3' union
select 1429,'你好','好','bbb','2010-11-6','2010-11-1' union
select 1429,'你好','不好','ccc','2010-11-2','2010-11-3' union
select 1429,'你好','好','ddd','2010-11-2','2010-11-4' union
select 1429,'你好','不好','eee','2010-11-2','2010-11-2' union
select 1429,'你好','好','fff',null,null union
select 1429,'你好','不好','ggg',null,null union
select 1429,'吃饭','吃','hhh',null,null union
select 1429,'你好','好','iii',null,null union
select 1429,'吃饭','吃','jjj',null,null union
select 1429,'你好','好','kkk',null,null union
select 1430,'喝水','喝','lll',null,null union
select 1430,'喝水',' 不喝','mmm',null,null
go
;with cte as
(
select * from #tb where
not exists(select 1 from #tb t where #tb.bid=t.bid and #tb.type=t.type and #tb.name=t.name and (#tb.writedate<t.writedate or #tb.writedate is null))
),
cte2 as(
select * from #tb where writedate is null and sysdate is null
and not exists(select 1 from #tb t where #tb.bid=t.bid and #tb.type=t.type and #tb.name=t.name and t.writedate is not null)
)
select * from
(select *,rn=row_number() over(partition by bid,type,name order by writedate,sysdate desc)
from cte
) n where rn=1
union all
select * from
(select *,rn=row_number() over(partition by bid,type,name order by id desc)
from cte2
) n where rn=1
order by id asc
id bid type name value writedate sysdate rn
----------- ----------- -------------------- -------------------- -------------------- ----------------------- ----------------------- --------------------
2 1429 吃饭 吃 jjj NULL NULL 1
3 1429 你好 不好 ccc 2010-11-02 00:00:00.000 2010-11-03 00:00:00.000 1
7 1429 你好 好 bbb 2010-11-06 00:00:00.000 2010-11-01 00:00:00.000 1
12 1430 喝水 不喝 mmm NULL NULL 1
13 1430 喝水 喝 lll NULL NULL 1
(5 行受影响)
--> 生成测试数据表: [tb]
IF OBJECT_ID('[aa]') IS NOT NULL
DROP TABLE aa
GO
create table aa(
id int identity primary key,
bid int,
type varchar(60),
name varchar(60),
value varchar(60),
writedate datetime,
sysdate datetime
)
go
insert into aa
select 1429,'你好','好','aaa','2010-11-2','2010-11-3' union
select 1429,'你好','好','bbb','2010-11-6','2010-11-1' union
select 1429,'你好','不好','ccc','2010-11-2','2010-11-3' union
select 1429,'你好','好','ddd','2010-11-2','2010-11-4' union
select 1429,'你好','不好','eee','2010-11-2','2010-11-2' union
select 1429,'你好','好','fff',null,null union
select 1429,'你好','不好','ggg',null,null union
select 1429,'吃饭','吃','hhh',null,null union
select 1429,'你好','好','iii',null,null union
select 1429,'吃饭','吃','jjj',null,null union
select 1429,'你好','好','kkk',null,null union
select 1430,'喝水','喝','lll',null,null union
select 1430,'喝水',' 不喝','mmm',null,null
go
SELECT id,bid,TYPE,name,VALUE,writedate,sysdate
FROM (
SELECT rn=ROW_NUMBER()OVER(PARTITION BY bid,type,name ORDER BY writedate desc,sysdate DESC,id desc),*
FROM aa a
) t
WHERE rn=1
ORDER BY id
/*
id bid TYPE name VALUE writedate sysdate
----------- ----------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ----------------------- -----------------------
2 1429 吃饭 吃 jjj NULL NULL
3 1429 你好 不好 ccc 2010-11-02 00:00:00.000 2010-11-03 00:00:00.000
7 1429 你好 好 bbb 2010-11-06 00:00:00.000 2010-11-01 00:00:00.000
12 1430 喝水 不喝 mmm NULL NULL
13 1430 喝水 喝 lll NULL NULL
(5 行受影响)
*/
没最终看出规律
IF OBJECT_ID('aa') IS NOT NULL
DROP TABLE aa
create table aa(
id int identity primary key,
bid int,
type varchar(20),
name varchar(20),
value varchar(20),
writedate datetime,
sysdate datetime
)
go
insert into aa
select 1429,'你好','好','aaa','2010-11-2','2010-11-3' union
select 1429,'你好','好','bbb','2010-11-6','2010-11-1' union
select 1429,'你好','不好','ccc','2010-11-2','2010-11-3' union
select 1429,'你好','好','ddd','2010-11-2','2010-11-4' union
select 1429,'你好','不好','eee','2010-11-2','2010-11-2' union
select 1429,'你好','好','fff',null,null union
select 1429,'你好','不好','ggg',null,null union
select 1429,'吃饭','吃','hhh',null,null union
select 1429,'你好','好','iii',null,null union
select 1429,'吃饭','吃','jjj',null,null union
select 1429,'你好','好','kkk',null,null union
select 1430,'喝水','喝','lll',null,null union
select 1430,'喝水',' 不喝','mmm',null,null
go
SELECT * FROM aa t1
WHERE id IN(
SELECT TOP 1 id FROM
aa
WHERE t1.bid=bid AND t1.[name]=NAME AND t1.[type]=type
ORDER BY bid,TYPE,NAME,writedate DESC,sysdate DESC ,id DESC
)
--->结果
id bid type name value writedate sysdate
----------- ----------- -------------------- -------------------- -------------------- ----------------------- -----------------------
2 1429 吃饭 吃 jjj NULL NULL
3 1429 你好 不好 ccc 2010-11-02 00:00:00.000 2010-11-03 00:00:00.000
7 1429 你好 好 bbb 2010-11-06 00:00:00.000 2010-11-01 00:00:00.000
12 1430 喝水 不喝 mmm NULL NULL
13 1430 喝水 喝 lll NULL NULL
select * from aa a where id = (select top 1 id from aa where bid=a.bid and type=a.type and name=a.name order by writeDate desc, sysDate desc, id desc)
/*
id bid type name value writedate sysdate
----------- ----------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ----------------------- -----------------------
2 1429 吃饭 吃 jjj NULL NULL
3 1429 你好 不好 ccc 2010-11-02 00:00:00.000 2010-11-03 00:00:00.000
7 1429 你好 好 bbb 2010-11-06 00:00:00.000 2010-11-01 00:00:00.000
12 1430 喝水 不喝 mmm NULL NULL
13 1430 喝水 喝 lll NULL NULL
(5 行受影响)
*/