DECLARE @TB TABLE(ID VARCHAR(6), objectID INT)
INSERT @TB
SELECT 's0001', 2 UNION ALL
SELECT 's0002', 2 UNION ALL
SELECT 's0001', 1 UNION ALL
SELECT 's0003', 3 UNION ALL
SELECT 's0002', 1SELECT ID FROM @TB WHERE objectID in (1,2) group by ID HAVING COUNT(*)=2
/*
ID
------
s0001
s0002
*/
INSERT @TB
SELECT 's0001', 2 UNION ALL
SELECT 's0002', 2 UNION ALL
SELECT 's0001', 1 UNION ALL
SELECT 's0003', 3 UNION ALL
SELECT 's0002', 1SELECT ID FROM @TB WHERE objectID in (1,2) group by ID HAVING COUNT(*)=2
/*
ID
------
s0001
s0002
*/
DECLARE @TB TABLE(ID VARCHAR(6), objectID INT)
INSERT @TB
SELECT 's0001', 2 UNION ALL
SELECT 's0002', 2 UNION ALL
SELECT 's0001', 2 UNION ALL
SELECT 's0003', 3 UNION ALL
SELECT 's0002', 1SELECT ID FROM @TB WHERE objectID in (1,2) group by ID HAVING COUNT(*)=2改成如下好些
DECLARE @TB TABLE(ID VARCHAR(6), objectID INT)
INSERT @TB
SELECT 's0001', 2 UNION ALL
SELECT 's0002', 2 UNION ALL
SELECT 's0001', 2 UNION ALL
SELECT 's0003', 3 UNION ALL
SELECT 's0002', 1
//修改过
SELECT ID FROM (select id,objectid from @TB group by id,objectid) a WHERE objectID in (1,2) group by ID HAVING COUNT(*)=2
(
select distinct id from stuinfo where objectid = 1
union all
select distinct id from stuinfo where objectid = 2
) t
group by id having count(*) = 2
INSERT @TB
SELECT 's0001', 2 UNION ALL
SELECT 's0002', 2 UNION ALL
SELECT 's0001', 2 UNION ALL
SELECT 's0003', 3 UNION ALL
SELECT 's0002', 1
select id from
(
select distinct id from @TB where objectid = 1
union all
select distinct id from @TB where objectid = 2
) t
group by id having count(*) = 2
/*
id
------
s0002(所影响的行数为 1 行)
*/
SELECT id FROM 表名 WHERE 课程名 in (1,2) group by id HAVING COUNT(*)=2
就可以了。