62,016
社区成员
发帖
与我相关
我的任务
分享
create table #t
(
ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
S_ID VARCHAR(50) NOT NULL,
COURSE_NAME VARCHAR(20) NOT NULL,
STATE BIT NOT NULL
)
insert into #t
values
('1900560013','A',0),
('1900560013','A',1),
('1900930116','A',1),
('1900930116','A',1),
('1900930116','A',1),
('1900930116','B',1),
('1900930116','C',0),
('1900930116','B',0),
('1900930116','C',1)
--先删除S_ID,COURSE_NAME,[STATE] 完全一样的数据,只剩下一条
--上面的insert语句中3,4,5 S_ID,COURSE_NAME,[STATE]都一样,所以就剩下了3
delete from #t
where ID not in
(
select min(id)
from #t
group by S_ID,COURSE_NAME,[STATE]
)
select * from #t
--这个地方就按照你的要求删除S_ID,COURSE_NAME一样,但[STATE]=1的数据
delete from #t
where ID in
(
select t.ID from #t t
inner join
(
select S_ID,COURSE_NAME from #t
group by S_ID,COURSE_NAME
having COUNT(*)>1
) as tt on t.s_id=tt.s_id and t.course_name=tt.course_name
where t.state=1
)
select * from #t
drop table #t