我想实现一个关键字搜索的网页,有三个输入框,然后我建了一个表SELECT SourceId FROM T_Entities WHERE (Context LIKE @Zone) AND (TypeId = 1)
SELECT SourceId FROM T_Entities WHERE (Context LIKE @Peroson) AND (TypeId = 2)
SELECT SourceId FROM T_Entities WHERE (Context LIKE @Organization) AND (TypeId = 3)我想把这三个select语句得到的SourceId取交集,请问要怎么写sql语句,别告诉我直接把后面and起来,这样就啥都搜不到了,因为这三个框分别对应不同的TypeId
SELECT SourceId FROM T_Entities WHERE (Context LIKE @Peroson) AND (TypeId = 2)
SELECT SourceId FROM T_Entities WHERE (Context LIKE @Organization) AND (TypeId = 3)我想把这三个select语句得到的SourceId取交集,请问要怎么写sql语句,别告诉我直接把后面and起来,这样就啥都搜不到了,因为这三个框分别对应不同的TypeId
SELECT t1.SourceId
FROM T_Entities t1,
(SELECT SourceId FROM T_Entities WHERE (Context LIKE @Peroson) AND (TypeId = 2)) t2,
(SELECT SourceId FROM T_Entities WHERE (Context LIKE @Organization) AND (TypeId = 3)) t3
WHERE (Context LIKE @Zone) AND (TypeId = 1)
and t1.SourceId=t2.SourceId
and t1.SourceId=t3.SourceId
from
(SELECT SourceId FROM T_Entities WHERE (Context LIKE @Zone) AND (TypeId = 1)) a,
(SELECT SourceId FROM T_Entities WHERE (Context LIKE @Peroson) AND (TypeId = 2)) b,
(SELECT SourceId FROM T_Entities WHERE (Context LIKE @Organization) AND (TypeId = 3)) c
where
a.SourceId=b.SourceId and a.SourceId=c.SourceId
intersect
SELECT SourceId FROM T_Entities WHERE (Context LIKE @Peroson) AND (TypeId = 2)
intersect
SELECT SourceId FROM T_Entities WHERE (Context LIKE @Organization) AND (TypeId = 3)
@Zone VARCHAR(100),
@Peroson VARCHAR(100),
@Organization VARCHAR(100)
SELECT
@Zone = 'a',
@Peroson = 'b',
@Organization = 'c'SELECT SourceId FROM T_Entities WHERE (Context LIKE '%'+@Zone+'%') AND (TypeId = 1)
INTERSECT
SELECT SourceId FROM T_Entities WHERE (Context LIKE '%'+@Peroson+'%') AND (TypeId = 2)
INTERSECT
SELECT SourceId FROM T_Entities WHERE (Context LIKE '%'+@Organization+'%') AND (TypeId = 3)--#1.用like时应该加上'%%',否则相当于=
--#2.INTERSECT A和B的并(A交B), 会排除重复值
from
(
SELECT SourceId FROM T_Entities WHERE (Context LIKE @Zone) AND (TypeId = 1)
union all
SELECT SourceId FROM T_Entities WHERE (Context LIKE @Peroson) AND (TypeId = 2)
union all
SELECT SourceId FROM T_Entities WHERE (Context LIKE @Organization) AND (TypeId = 3)
) a
group by SourceId having count(SourceId)=3--要求每一个查询中不能有重复的值,否则不能这么计算
SELECT SourceId FROM T_Entities a WHERE (Context LIKE @Zone) AND (TypeId = 1)
and exists(SELECT 1 FROM T_Entities b WHERE (Context LIKE @Peroson) AND (TypeId = 2)
and a.SourceId =b.SourceId )
and exists(SELECT SourceId FROM T_Entities c WHERE (Context LIKE @Organization) AND (TypeId = 3) and a.SourceId =c.SourceId )另外#7楼 的每个里面加个distinct应该也可以的
1楼说的对!!既然对应不同的typeid,那怎么可能有交集假设有一条记录是三个查询交集中的一条记录,那这条记录必须满足typieid = 1 且 typieid = 2 且 typieid = 3 因为一个值不能等于1又等于2又等于三,所以不存在这样的记录3楼得查询结果集必然为空
7楼得查询叫合集,不是交集,谢谢
IF OBJECT_ID('[T_Entities]') IS NOT NULL
DROP TABLE [T_Entities]
GO
CREATE TABLE [T_Entities] ([SourceId] [int],[Context] [nvarchar](10),[TypeId] [int])
INSERT INTO [T_Entities]
SELECT '1','aabb','1' UNION ALL
SELECT '1','aabc','1' UNION ALL
SELECT '1','aabc','2' UNION ALL
SELECT '1','aacd','3'
-->SQL查询如下:
DECLARE
@Zone VARCHAR(100),
@Peroson VARCHAR(100),
@Organization VARCHAR(100)
SELECT
@Zone = '%a%',
@Peroson = '%b%',
@Organization = '%c%'SELECT SourceId
FROM [T_Entities]
WHERE ((Context LIKE @Zone) AND (TypeId = 1))
OR ((Context LIKE @Peroson) AND (TypeId = 2))
OR ((Context LIKE @Organization) AND (TypeId = 3))
GROUP BY SourceId
HAVING COUNT(DISTINCT TypeID)=3
/*
SourceId
-----------
1(1 行受影响)
*/
指定合并多个结果集并将其作为单个结果集返回。
ALL
将全部行并入结果中。其中包括重复行。如果未指定该参数,则删除重复行。EXCEPT (差集)
从 EXCEPT 操作数左边的查询中返回右边的查询未返回的所有非重复值。
INTERSECT (交集)
返回 INTERSECT 操作数左右两边的两个查询均返回的所有非重复值。
(
SELECT SourceId FROM T_Entities WHERE (Context LIKE @Organization) AND (TypeId = 3))