27,579
社区成员
发帖
与我相关
我的任务
分享
use Tempdb
go
--> --> 听雨停了-->生成测试数据
if not object_id(N'Tempdb..#tab') is null
drop table #tab
Go
Create table #tab(nam nvarchar(22),p_id nvarchar(25),dname nvarchar(24),dcode nvarchar(27),occ_time DATETIME,amount int, pc nvarchar(27),total int)
Insert #tab
select N'张三',N'00001',N'螺内酯片',N'db20033','2017-11-02 00:00',1,N'q2h',10 union all
select N'张三',N'00001',N'螺内酯片',N'db20033','2017-11-02 02:00',1,N'q2h',10 union all
select N'张三',N'00001',N'螺内酯片',N'db20033','2017-11-02 04:00',1,N'q2h',10 union all
select N'张三',N'00001',N'螺内酯片',N'db20033','2017-11-02 06:00',1,N'q2h',10 union all
select N'张三',N'00001',N'螺内酯片',N'db20033','2017-11-02 08:00',1,N'q2h',10 union all
select N'张三',N'00001',N'螺内酯片',N'db20033','2017-11-02 10:00',1,N'q2h',10 union all
select N'张三',N'00001',N'螺内酯片',N'db20033','2017-11-02 12:00',1,N'q2h',10 union all
select N'张三',N'00001',N'螺内酯片',N'db20033','2017-11-02 14:00',1,N'q2h',10 union all
select N'张三',N'00001',N'螺内酯片',N'db20033','2017-11-02 16:00',1,N'q2h',10 union all
select N'张三',N'00001',N'螺内酯片',N'db20033','2017-11-02 18:00',1,N'q2h',10 union all
select N'张三',N'00001',N'螺内酯片',N'db20033','2017-11-02 20:00',1,N'q2h',10 union all
select N'张三',N'00001',N'螺内酯片',N'db20033','2017-11-02 22:00',1,N'q2h',10 union all
select N'李四',N'0002',N'呋塞米片',N'db20044','2017-11-03 08:00',2,N'8-12-16',6 union all
select N'李四',N'0002',N'呋塞米片',N'db20044','2017-11-03 12:00',2,N'8-12-16',6 union all
select N'李四',N'0002',N'呋塞米片',N'db20044','2017-11-02 16:00',2,N'8-12-16',6
Go
--测试数据结束
;WITH cte AS (
SELECT *,
ROW_NUMBER() OVER(PARTITION BY nam, dname, pc ORDER BY occ_time) AS
rn,
total / amount AS cnt
FROM #tab
)
--可以把下面的select反注释一下,查询出来看看
--SELECT * FROM cte
DELETE FROM cte WHERE rn>cnt
(2 行受影响)
大概意思是上面的数据,张三需要删除两条,李四不需要删除记录,对吧