delete t from t_user_role t where exists(select * from t_user_role where t.user_id=USER_id and role_id=1) and exists(select * from t_user_role where t.user_id=USER_id and role_id=2) and exists(select * from t_user_role where t.user_id=USER_id and role_id=3 )
---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2010-03-20 16:42:06 -- Version: -- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) -- Nov 24 2008 13:01:59 -- Copyright (c) 1988-2005 Microsoft Corporation -- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3) -- ---------------------------------------------------------------- --> 测试数据:[t_user_role] if object_id('[t_user_role]') is not null drop table [t_user_role] go create table [t_user_role]([user_id] int,[role_id] int) insert [t_user_role] select 1,1 union all select 1,2 union all select 1,3 union all select 2,1 union all select 2,3 union all select 3,2 union all select 4,2 union all select 4,1 union all select 4,3 --------------开始查询-------------------------- delete from t_user_role where role_id in(1,2,3) and user_id in (select user_id from t_user_role group by user_id having count(1)>=3)select * from t_user_role----------------结果---------------------------- /* user_id role_id ----------- ----------- 2 1 2 3 3 2(3 行受影响)*/
楼主描述得还不够清楚,看样子,应该要 count(distinct role_id)=3
delete from [t_user_role] where [role_id] in(1,2,3) AND [user_id] IN(SELECT [user_id] FROM [t_user_role] where [role_id] in(1,2,3) GROUP BY [user_id] HAVING COUNT(DISTINCT [role_id])=3)SELECT * FROM [t_user_role] user_id role_id ----------- ----------- 2 1 2 3 3 2(3 行受影响)
create table #t_user_role ([user_id] int, role_id int) insert into #t_user_role select 1,1 union all select 1,2 union all select 1,3 union all select 2,1 union all select 2,3 union all select 3,2 union all select 4,2 union all select 4,1 union all select 4,3delete a from #t_user_role a where exists(select 1 from #t_user_role where a.[user_id]=[user_id] and role_id=1 ) and exists(select 1 from #t_user_role where a.[user_id]=[user_id] and role_id=2) and exists(select 1 from #t_user_role where a.[user_id]=[user_id] and role_id=3)select * from #t_user_roleuser_id role_id 2 1 2 3 3 2
delete * from t_user_role where user_id = 1 or user_id = 2
from t_user_role t
where exists(select * from t_user_role where t.user_id=USER_id and role_id=1)
and exists(select * from t_user_role where t.user_id=USER_id and role_id=2)
and exists(select * from t_user_role where t.user_id=USER_id and role_id=3 )
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-03-20 16:42:06
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[t_user_role]
if object_id('[t_user_role]') is not null drop table [t_user_role]
go
create table [t_user_role]([user_id] int,[role_id] int)
insert [t_user_role]
select 1,1 union all
select 1,2 union all
select 1,3 union all
select 2,1 union all
select 2,3 union all
select 3,2 union all
select 4,2 union all
select 4,1 union all
select 4,3
--------------开始查询--------------------------
delete from
t_user_role
where
role_id in(1,2,3)
and
user_id in (select user_id from t_user_role group by user_id having count(1)>=3)select * from t_user_role----------------结果----------------------------
/* user_id role_id
----------- -----------
2 1
2 3
3 2(3 行受影响)*/
where [role_id] in(1,2,3)
AND [user_id] IN(SELECT [user_id] FROM [t_user_role] where [role_id] in(1,2,3) GROUP BY [user_id] HAVING COUNT(DISTINCT [role_id])=3)SELECT * FROM [t_user_role]
user_id role_id
----------- -----------
2 1
2 3
3 2(3 行受影响)
create table #t_user_role
([user_id] int,
role_id int)
insert into #t_user_role
select 1,1 union all
select 1,2 union all
select 1,3 union all
select 2,1 union all
select 2,3 union all
select 3,2 union all
select 4,2 union all
select 4,1 union all
select 4,3delete a from #t_user_role a
where exists(select 1 from #t_user_role where a.[user_id]=[user_id] and role_id=1 )
and exists(select 1 from #t_user_role where a.[user_id]=[user_id] and role_id=2)
and exists(select 1 from #t_user_role where a.[user_id]=[user_id] and role_id=3)select * from #t_user_roleuser_id role_id
2 1
2 3
3 2