最近,在论坛中,遇到了不少比较难的sql问题,虽然自己都能解决,但发现过几天后,就记不起来了,也忘记解决的方法了。
所以,觉得有必要记录下来,这样以后再次碰到这类问题,也能从中获取解答的思路。
这个语句怎么写?
http://bbs.csdn.net/topics/390490832?page=1
我有一张表:CarRule
有下面这些列和数据
ID Keywords
1 时速50%、 不到100%
2 违反禁令标志
3 违反规定停放、拒绝立即驶离、妨碍其他车辆
我要查询这个CarRule表,根据关键字获取ID
例如:机动车行驶超过规定时速50%以上不到100%的 就能获取到 ID=1
机动车违反禁令标志的 就能获取到 ID=2
违反规定停放、临时停车且驾驶人不在现场或驾驶人虽在现场拒绝立即驶离,妨碍其他车辆、行人通行的
就能获取到 ID=3
这个查询我怎么写。
我的解法:
--1.先建立一个函数,通过分隔符来拆分keywords成多个关键字
create function dbo.fn_splitSTR
(
@s varchar(8000), --要分拆的字符串
@split varchar(10) --分隔字符
)
returns @re table( --要返回的临时表
col varchar(1000) --临时表中的列
)
as
begin
declare @len int
set @len = LEN(@split) --分隔符不一定就是一个字符,可能是2个字符
while CHARINDEX(@split,@s) >0
begin
insert into @re
values(left(@s,charindex(@split,@s) - 1))
set @s = STUFF(@s,1,charindex(@split,@s) - 1 + @len ,'') --覆盖:字符串以及分隔符
end
insert into @re values(@s)
return --返回临时表
end
go
--2.建表
DECLARE @CarRule TABLE(id INT,Keywords VARCHAR(100))
INSERT INTO @carrule
VALUES(1,'时速50%、不到100%'),
(2,'违反禁令标志'),
(3,'违反规定停放、拒绝立即驶离、妨碍其他车辆')
;WITH split --拆分关键字
as
(
SELECT c.id,
c.keywords,
f.col
FROM @carrule c
CROSS apply dbo.fn_splitSTR(c.keywords,'、') f
)
--3.第1个查询
SELECT s.id,
s.keywords
FROM split s
INNER JOIN
(
SELECT s.id,
s.keywords,
count(col) AS split_str_count --拆分成了几个关键字
FROM split s
GROUP BY s.id,
s.keywords
) ss
ON s.id = ss.id
WHERE charindex(s.col,'机动车行驶超过规定时速50%以上不到100%的') > 0
GROUP BY s.id,
s.keywords
HAVING count(*) = max(ss.split_str_count) --比如第一条记录拆分成了2个关键词,那么在匹配时要2个都匹配上了,才算为匹配
第2个查询:
DECLARE @CarRule TABLE(id INT,Keywords VARCHAR(100))
INSERT INTO @carrule
VALUES(1,'时速50%、不到100%'),
(2,'违反禁令标志'),
(3,'违反规定停放、拒绝立即驶离、妨碍其他车辆')
;WITH split --拆分关键字
as
(
SELECT c.id,
c.keywords,
f.col
FROM @carrule c
CROSS apply dbo.fn_splitSTR(c.keywords,'、') f
)
--3.
SELECT s.id,
s.keywords
FROM split s
INNER JOIN
(
SELECT s.id,
s.keywords,
count(col) AS split_str_count --拆分成了几个关键字
FROM split s
GROUP BY s.id,
s.keywords
) ss
ON s.id = ss.id
WHERE charindex(s.col,'机动车违反禁令标志的') > 0
GROUP BY s.id,
s.keywords
HAVING count(*) = max(ss.split_str_count) --比如第一条记录拆分成了2个关键词,那么在匹配时要2个都匹配上了,才算为匹配
第3个查询:
DECLARE @CarRule TABLE(id INT,Keywords VARCHAR(100))
INSERT INTO @carrule
VALUES(1,'时速50%、不到100%'),
(2,'违反禁令标志'),
(3,'违反规定停放、拒绝立即驶离、妨碍其他车辆')
;WITH split --拆分关键字
as
(
SELECT c.id,
c.keywords,
f.col
FROM @carrule c
CROSS apply dbo.fn_splitSTR(c.keywords,'、') f
)
--3.
SELECT s.id,
s.keywords
FROM split s
INNER JOIN
(
SELECT s.id,
s.keywords,
count(col) AS split_str_count --拆分成了几个关键字
FROM split s
GROUP BY s.id,
s.keywords
) ss
ON s.id = ss.id
WHERE charindex(s.col,'违反规定停放、临时停车且驾驶人不在现场或驾驶人虽在现场拒绝立即驶离,妨碍其他车辆、行人通行的就能获取到') > 0
GROUP BY s.id,
s.keywords
HAVING count(*) = max(ss.split_str_count) --比如第一条记录拆分成了2个关键词,那么在匹配时要2个都匹配上了,才算为匹配