比如说 一表 id name description 查询关键字有三: keyword1,keyword2,keyword3 要求结果集: 首先是 在name中既包括keyword1,又包括keyword2,还包括keyword3的 (第一部分)
其次是 在name中包括keyword1,或者keyword2,或者keyword3的 (第二部分)
再次是 在description中包括keyword1或者keyword2或者keyword3的 (第三部分)
三个部分分别各自按ID ASC 求一存储过程,希望实现起来简单明了,效率高,
我想实现的比这个要复杂很多,
我现在的思路是 希望通过一条复杂语句查处,结果集中包括两个表示列, 列1表示属于第几部分,列二表示此记录匹配到的关键字数量,
按列1 asc,列2 desc, [id] asc
不知道能不能实现
其次是 在name中包括keyword1,或者keyword2,或者keyword3的 (第二部分)
再次是 在description中包括keyword1或者keyword2或者keyword3的 (第三部分)
三个部分分别各自按ID ASC 求一存储过程,希望实现起来简单明了,效率高,
我想实现的比这个要复杂很多,
我现在的思路是 希望通过一条复杂语句查处,结果集中包括两个表示列, 列1表示属于第几部分,列二表示此记录匹配到的关键字数量,
按列1 asc,列2 desc, [id] asc
不知道能不能实现
select * from tb where charindex(@keyword1,name)>0 and charindex(@keyword2,name)>0 and charindex(@keyword3,name)>0
union
select * from tb where charindex(@keword1,name)>0 or (charindex(@keyword2,name)>0 or charindex(@keyword3,name)>0)
union
select * from tb where charindex(@keword1,description)>0 or (charindex(@keyword2,description)>0 or charindex(@keyword3,description)>0)
)A order by id
INSERT @t SELECT 'aabbbcc','aaa'
UNION ALL SELECT 'aabb',''
UNION ALL SELECT 'def','a'
UNION ALL SELECT 'ett','abc'DECLARE @k1 VARCHAR(10),@k2 VARCHAR(10),@k3 VARCHAR(10)
SELECT @k1='a',@k2='b',@k3='c'SELECT id,name,description,idx=0,COUNT(v) cnt FROM @t a
INNER JOIN
(SELECT @k1 v UNION SELECT @k2 UNION SELECT @k3 ) b
ON CHARINDEX(v,name)>0
GROUP BY id,name,description
HAVING COUNT(DISTINCT v)>=3
UNION
SELECT id,name,description,idx=1,COUNT(v) FROM @t a
INNER JOIN
(SELECT @k1 v UNION SELECT @k2 UNION SELECT @k3 ) b
ON CHARINDEX(v,name)>0
GROUP BY id,name,description
HAVING COUNT(DISTINCT v) BETWEEN 1 AND 2
UNION
SELECT id,name,description,idx=2,COUNT(v) FROM @t a
INNER JOIN
(SELECT @k1 v UNION SELECT @k2 UNION SELECT @k3 ) b
ON CHARINDEX(v,name)=0 AND CHARINDEX(v,description)>0
GROUP BY id,name,description
ORDER BY idx,cnt desc,id
/*
1 aabbbcc aaa 0 3
2 aabb 1 2
4 ett abc 2 3
3 def a 2 1
*/
INSERT @t SELECT 'aabbbcc','aaa'
UNION ALL SELECT 'aabb',''
UNION ALL SELECT 'def','a'
UNION ALL SELECT 'ett','abc'DECLARE @k1 VARCHAR(10),@k2 VARCHAR(10),@k3 VARCHAR(10)
SELECT @k1='a',@k2='b',@k3='c'SELECT id,name,description,idx=0,COUNT(v) cnt FROM @t a
INNER JOIN
(SELECT @k1 v UNION SELECT @k2 UNION SELECT @k3 ) b
ON CHARINDEX(v,name)>0
GROUP BY id,name,description
HAVING COUNT(DISTINCT v)>=3
UNION
SELECT id,name,description,idx=1,COUNT(v) FROM @t a
INNER JOIN
(SELECT @k1 v UNION SELECT @k2 UNION SELECT @k3 ) b
ON CHARINDEX(v,name)>0
GROUP BY id,name,description
HAVING COUNT(DISTINCT v) BETWEEN 1 AND 2
UNION
SELECT id,name,description,idx=2,COUNT(v) FROM @t a
INNER JOIN
(SELECT @k1 v UNION SELECT @k2 UNION SELECT @k3 ) b
ON CHARINDEX(v,name)=0 AND CHARINDEX(v,description)>0
GROUP BY id,name,description
ORDER BY idx,cnt desc,id
/*
1 aabbbcc aaa 0 3
2 aabb 1 2
4 ett abc 2 3
3 def a 2 1
*/
INSERT @t SELECT 'aabbbcc','aaa'
UNION ALL SELECT 'aabb',''
UNION ALL SELECT 'def','a'
UNION ALL SELECT 'ett','abc'
UNION ALL SELECT 'ett','rtete'DECLARE @k1 VARCHAR(10),@k2 VARCHAR(10),@k3 VARCHAR(10)
SELECT @k1='a',@k2='b',@k3='c'
SELECT * FROM @t
/*source data
1 aabbbcc aaa
2 aabb
3 def a
4 ett abc
5 ett rtete
*/
----------------------------------------
/*
联合加行的写法
*/
SELECT id,name,description,idx=0,COUNT(v) cnt FROM @t a
INNER JOIN
(SELECT @k1 v UNION SELECT @k2 UNION SELECT @k3 ) b
ON CHARINDEX(v,name)>0
GROUP BY id,name,description
HAVING COUNT(DISTINCT v)>=3
UNION
SELECT id,name,description,idx=1,COUNT(v) FROM @t a
INNER JOIN
(SELECT @k1 v UNION SELECT @k2 UNION SELECT @k3 ) b
ON CHARINDEX(v,name)>0
GROUP BY id,name,description
HAVING COUNT(DISTINCT v) BETWEEN 1 AND 2
UNION
SELECT id,name,description,idx=2,COUNT(v) FROM @t a
INNER JOIN
(SELECT @k1 v UNION SELECT @k2 UNION SELECT @k3 ) b
ON CHARINDEX(v,name)=0 AND CHARINDEX(v,description)>0
GROUP BY id,name,description
ORDER BY idx,cnt desc,id
/*
1 aabbbcc aaa 0 3
2 aabb 1 2
4 ett abc 2 3
3 def a 2 1
*/
--------------------------------------------
/*
连接加列的写法
*/
SELECT id,name,description,
idx=
case when count(b.v)=0 then
case when count(c.v)>0 then 2
else null end
when count(b.v) between 1 and 2 then 1
else 0 end,
cnt=
case when count(b.v)=0 then
count(c.v)
else count(b.v) end
FROM @t a
LEFT JOIN (SELECT @k1 v UNION SELECT @k2 UNION SELECT @k3) b
ON CHARINDEX(b.v,name)>0
LEFT JOIN (SELECT @k1 v UNION SELECT @k2 UNION SELECT @k3) c
ON CHARINDEX(c.v,description)>0
GROUP BY id,name,description
having case when count(b.v)=0 then
case when count(c.v)>0 then 2
else null end
when count(b.v) between 1 and 2 then 1
else 0 end<3
ORDER BY idx,cnt desc,id
/*
1 aabbbcc aaa 0 3
2 aabb 1 2
4 ett abc 2 3
3 def a 2 1
*/