本文详细介绍了如何使用SQL动态生成技术解决复杂查询问题，并提供了字符串检索的解决方案，包括如何构建函数、处理分隔符和执行高效查询。涵盖了动态SQL生成、字符串匹配等关键点。
最近，在论坛中，遇到了不少比较难的sql问题，虽然自己都能解决，但发现过几天后，就记不起来了，也忘记解决的方法了。
所以，觉得有必要记录下来，这样以后再次碰到这类问题，也能从中获取解答的思路。
1、求一SQL语句。
http://bbs.youkuaiyun.com/topics/390496661
create table #tab ( col1 char(10), col2 char(10), item char(10), num int, [Date] varchar(10)) insert #tab values('AAA','BBB','A',50,'2013-06-10') insert #tab values('ABB','BGG','B',30,'2013-06-10') insert #tab values('AAA','BBB','C',80,'2013-06-13')
我的解法：
create table tab ( col1 char(10), col2 char(10), item char(10), num int, [Date] varchar(10) ) insert tab values('AAA','BBB','A',50,'2013-06-10') insert tab values('ABB','BGG','B',30,'2013-06-10') insert tab values('AAA','BBB','C',80,'2013-06-13') --动态生成sql语句 declare @start_date varchar(10) = '2013-06-01', @end_date varchar(10) = '2013-06-30'; declare @date varchar(10), @sql varchar(max) = '', @sql1 varchar(8000), @sql2 varchar(8000); set @date = @start_date; set @sql1 = 'select case when rownum = 1 then col1 else '''' end as col1, case when rownum = 1 then col2 else '''' end as col2, item' set @sql2 = 'select col1,col2,item,row_number() over(partition by col1,col2 order by item) as rownum' while @date <= @end_date begin set @sql1 = @sql1 + ',v_' + REPLACE( right(@date,5),'-','') + ' as ''' + CAST(DATEPART(month,@date) as varchar) + '/' + CAST(DATEPART(day,@date) as varchar) +''''; set @sql2 = @sql2 + ',SUM(case when date =''' + @date + ''' then num else 0 end) as v_' + REPLACE( right(@date,5),'-','') set @date = CONVERT(varchar(10),dateadd(day,1,@date),120) end set @sql = @sql1 + ' from (' + @sql2 + ' from tab group by col1,col2,item' + ') v' --生产的动态sql语句 select @sql exec(@sql)
上面由于是动态生成语句，所以不能用局部的临时表，所以建了一个表。
下面是动态生成的sql语句，经过了格式化：
select case when rownum = 1 then col1 else '' end as col1, case when rownum = 1 then col2 else '' end as col2, item, v_0601 as '6/1',v_0602 as '6/2',v_0603 as '6/3', v_0604 as '6/4',v_0605 as '6/5', v_0606 as '6/6',v_0607 as '6/7', v_0608 as '6/8',v_0609 as '6/9', v_0610 as '6/10',v_0611 as '6/11', v_0612 as '6/12',v_0613 as '6/13', v_0614 as '6/14',v_0615 as '6/15', v_0616 as '6/16',v_0617 as '6/17', v_0618 as '6/18',v_0619 as '6/19', v_0620 as '6/20',v_0621 as '6/21', v_0622 as '6/22',v_0623 as '6/23', v_0624 as '6/24',v_0625 as '6/25', v_0626 as '6/26',v_0627 as '6/27', v_0628 as '6/28',v_0629 as '6/29', v_0630 as '6/30' from ( select col1,col2,item, row_number() over(partition by col1,col2 order by item) as rownum, SUM(case when date ='2013-06-01' then num else 0 end) as v_0601, SUM(case when date ='2013-06-02' then num else 0 end) as v_0602, SUM(case when date ='2013-06-03' then num else 0 end) as v_0603, SUM(case when date ='2013-06-04' then num else 0 end) as v_0604, SUM(case when date ='2013-06-05' then num else 0 end) as v_0605, SUM(case when date ='2013-06-06' then num else 0 end) as v_0606, SUM(case when date ='2013-06-07' then num else 0 end) as v_0607, SUM(case when date ='2013-06-08' then num else 0 end) as v_0608, SUM(case when date ='2013-06-09' then num else 0 end) as v_0609, SUM(case when date ='2013-06-10' then num else 0 end) as v_0610, SUM(case when date ='2013-06-11' then num else 0 end) as v_0611, SUM(case when date ='2013-06-12' then num else 0 end) as v_0612, SUM(case when date ='2013-06-13' then num else 0 end) as v_0613, SUM(case when date ='2013-06-14' then num else 0 end) as v_0614, SUM(case when date ='2013-06-15' then num else 0 end) as v_0615, SUM(case when date ='2013-06-16' then num else 0 end) as v_0616, SUM(case when date ='2013-06-17' then num else 0 end) as v_0617, SUM(case when date ='2013-06-18' then num else 0 end) as v_0618, SUM(case when date ='2013-06-19' then num else 0 end) as v_0619, SUM(case when date ='2013-06-20' then num else 0 end) as v_0620, SUM(case when date ='2013-06-21' then num else 0 end) as v_0621, SUM(case when date ='2013-06-22' then num else 0 end) as v_0622, SUM(case when date ='2013-06-23' then num else 0 end) as v_0623, SUM(case when date ='2013-06-24' then num else 0 end) as v_0624, SUM(case when date ='2013-06-25' then num else 0 end) as v_0625, SUM(case when date ='2013-06-26' then num else 0 end) as v_0626, SUM(case when date ='2013-06-27' then num else 0 end) as v_0627, SUM(case when date ='2013-06-28' then num else 0 end) as v_0628, SUM(case when date ='2013-06-29' then num else 0 end) as v_0629, SUM(case when date ='2013-06-30' then num else 0 end) as v_0630 from tab group by col1,col2,item ) v
2、字符串检索问题：http://bbs.youkuaiyun.com/topics/390608926
这是 http://bbs.youkuaiyun.com/topics/390530288 问题的 一个变种
表
ID IndexArr
1 1,2,3,4,5
2 55,6,99,87,1000
3 7,567567,567,43,123
IndexArr 是","分割的数字
现在有字符串 '2,34,45,345,867,4,984'
现在要检索的是 IndexArr 中每一个数字都在 字符串中出现过的 结果集。
我的解法：
--1.函数 if exists(select * from sys.objects where name = 'f_splitSTR' and type = 'tf') drop function dbo.f_splitSTR go create function dbo.f_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 --> 测试数据：[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([ID] int,[IndexArr] varchar(19)) insert [tb] select 1,'1,2,3,4,5' union all select 2,'55,6,99,87,1000' union all select 3,'7,567567,567,43,123' union ALL SELECT 4,'2,34,45' --------------开始查询-------------------------- DECLARE @s VARCHAR(1000) SET @s= '2,34,45,345,867,4,984' ;with t as ( select t.ID, t.IndexArr, f.col, --把IndexArr按照分隔符,拆分成了多少个字符串 COUNT(*) over(PARTITION by IndexArr) as split_str_count from tb t cross apply dbo.f_splitSTR(t.IndexArr,',') f ) select t.ID, t.IndexArr from t where charindex(col, ','+@s+',') > 0 group by t.ID, t.IndexArr, t.split_str_count having COUNT(*) = t.split_str_count --比如2,34,45分拆为3个字符串， --那么在经过where条件过滤后，记录数也必须是3 --这样说明了indexarr中的字符串都在@s变量中出现了
3、这个语句怎么写？
http://bbs.youkuaiyun.com/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个都匹配上了，才算为匹配
转载于:https://blog.51cto.com/yupeigu/1367961
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
