有个SQL ,表结构这样
ID NAME
1 1
1 2
1 3
1 4
1 52 2
2 7
2 4
2 53 2
3 6
要找出同时包含 2和4的NAME记录,上面的数据只有ID为1和2的记录才会满足
如果用where name in(2,4)的话,记录ID,3也满足in(2)的条件,会显示出来
这该怎么办呢?还有2,4这个不是固定的,传入的变量,有可能是1,2,13,4,5,6,7,9,101等.求助达人
ID NAME
1 1
1 2
1 3
1 4
1 52 2
2 7
2 4
2 53 2
3 6
要找出同时包含 2和4的NAME记录,上面的数据只有ID为1和2的记录才会满足
如果用where name in(2,4)的话,记录ID,3也满足in(2)的条件,会显示出来
这该怎么办呢?还有2,4这个不是固定的,传入的变量,有可能是1,2,13,4,5,6,7,9,101等.求助达人
FROM tb
WHERE name IN(2,4)
GROUP BY ID
HAVING COUNT(*)>=2
select a.* from
(select * from TB where name=2)a,
(select * from Tb where name=4)b
where a.id=b.id
-- Author: liangCK 小梁 & angellan 兰儿
-- Commen: 小梁 爱 兰儿
-- Date : 2009-05-27 18:03:12
-------------------------------------------
--> 生成测试数据: @T
DECLARE @T TABLE (ID INT,NAME INT)
INSERT INTO @T
SELECT 1,1 UNION ALL
SELECT 1,2 UNION ALL
SELECT 1,3 UNION ALL
SELECT 1,4 UNION ALL
SELECT 1,5 UNION ALL
SELECT 2,2 UNION ALL
SELECT 2,7 UNION ALL
SELECT 2,4 UNION ALL
SELECT 2,5 UNION ALL
SELECT 3,2 UNION ALL
SELECT 3,6--SQL查询如下:SELECT A.*
FROM @T AS A
JOIN (
SELECT ID FROM @T WHERE name IN(2,4) GROUP BY ID HAVING COUNT(*)>=2
) AS B
ON A.ID=B.ID/*
ID NAME
----------- -----------
1 1
1 2
1 3
1 4
1 5
2 2
2 7
2 4
2 5
*/
INSERT @TB
SELECT 1, 1 UNION ALL
SELECT 1, 2 UNION ALL
SELECT 1, 3 UNION ALL
SELECT 1, 4 UNION ALL
SELECT 1, 5 UNION ALL
SELECT 2, 2 UNION ALL
SELECT 2, 7 UNION ALL
SELECT 2, 4 UNION ALL
SELECT 2, 5 UNION ALL
SELECT 3, 2 UNION ALL
SELECT 3, 6SELECT A.*
FROM @TB AS A JOIN (
SELECT ID
FROM @TB
WHERE NAME IN (2,4)
GROUP BY ID
HAVING COUNT(*)=2
) B
ON A.ID=B.ID
/*
ID NAME
----------- -----------
1 1
1 2
1 3
1 4
1 5
2 2
2 7
2 4
2 5
*/
from table ,
(
select count(distinct id),id
from table
where name in('2','4')
group by id
having count(distinct id)>1
) A
where table.ID=A.id
INSERT INTO @T
SELECT 1,1 UNION ALL
SELECT 1,2 UNION ALL
SELECT 1,3 UNION ALL
SELECT 1,4 UNION ALL
SELECT 1,5 UNION ALL
SELECT 2,2 UNION ALL
SELECT 2,5 UNION ALL
SELECT 2,7 UNION ALL
SELECT 2,4 UNION ALL
SELECT 2,5 UNION ALL
SELECT 3,2 UNION ALL
SELECT 3,4 UNION ALL
SELECT 3,6;SELECT ID
FROM @T
WHERE name IN(2,4,5)
GROUP BY ID
HAVING COUNT(*)>=2;
INSERT @TB
SELECT 1, 1 UNION ALL
SELECT 1, 2 UNION ALL
SELECT 1, 3 UNION ALL
SELECT 1, 4 UNION ALL
SELECT 1, 5 UNION ALL
SELECT 2, 2 UNION ALL
SELECT 2, 7 UNION ALL
SELECT 2, 4 UNION ALL
SELECT 2, 5 UNION ALL
SELECT 3, 2 UNION ALL
SELECT 3, 6DECLARE @STR VARCHAR(1000)
SET @STR='2,4,5'
SET @STR=REPLACE(@STR,',',' UNION SELECT ')CREATE TABLE #(ID INT)
INSERT #
EXEC('SELECT ID='+@STR)SELECT A.*
FROM @TB AS A JOIN (
SELECT A.ID
FROM @TB AS A JOIN # AS B ON A.NAME=B.ID
GROUP BY A.ID
HAVING COUNT(*)=(SELECT COUNT(*) FROM #)
) B
ON A.ID=B.IDDROP TABLE #
/*
ID NAME
----------- -----------
1 1
1 2
1 3
1 4
1 5
2 2
2 7
2 4
2 5
*/
-- Author: liangCK 小梁 & angellan 兰儿
-- Commen: 小梁 爱 兰儿
-- Date : 2009-05-27 18:03:12
-------------------------------------------
--> 生成测试数据: @T
DECLARE @T TABLE (ID INT,NAME INT)
INSERT INTO @T
SELECT 1,1 UNION ALL
SELECT 1,2 UNION ALL
SELECT 1,3 UNION ALL
SELECT 1,4 UNION ALL
SELECT 1,5 UNION ALL
SELECT 2,2 UNION ALL
SELECT 2,7 UNION ALL
SELECT 2,4 UNION ALL
SELECT 2,5 UNION ALL
SELECT 3,2 UNION ALL
SELECT 3,6--SQL查询如下:DECLARE @str VARCHAR(500);
SET @str= '2,4'SELECT A.*
FROM @T AS A
JOIN (
SELECT ID FROM @T WHERE name IN(2,4)
GROUP BY ID HAVING COUNT(*)>=LEN(@str)-LEN(REPLACE(@str,',',''))+1
) AS B
ON A.ID=B.ID/*
ID NAME
----------- -----------
1 1
1 2
1 3
1 4
1 5
2 2
2 7
2 4
2 5*/
EXEC('SELECT ID='+@STR)
用了执行SQL变量,但是效率不会下降很多,关键的是后面的
SELECT A.*
FROM @TB AS A JOIN (
SELECT A.ID
FROM @TB AS A JOIN # AS B ON A.NAME=B.ID
GROUP BY A.ID
HAVING COUNT(*)=(SELECT COUNT(*) FROM #)
) B
ON A.ID=B.ID进行真正的表查找的时候,用的是SQL语句(非变量SQL),在11楼的说法中,应该能通过“预编译”这关吧。呵呵