sql 查询日记1
今天在问题提问区看到一个问题
简单试了一下暂时没有发现什么问题
就是有点复杂,传入参数是以‘,’号隔开的技能
数据库里有一个表,主要有两个字段 [姓名],[技能]
可能的数据如下
[姓名] [技能]
甲 技能1
甲 技能2
甲 技能3
乙 技能1
乙 技能2
乙 技能4
丙 技能2
丙 技能3
丁 技能1
丁 技能4
......
现在我想找到拥有技能1,同时还拥有技能2的人。
希望能够通过一个sql语句得到这样的结果
[姓名] [技能]
甲 技能1
甲 技能2
乙 技能1
乙 技能2
丙拥有技能2,但是没有技能1,不能显示出来。
丁只有技能1没有技能2也不能显示。
看了其他人回答,使我冒出一个用递归来查新的想法
代码如下:
1
DECLARE @temp VARCHAR(20) --申明变量, 也就是输入参数
2
SET @temp='a,b,c,d,e,f,g,h,aa' --为变量赋值
3
WITH aa(a,b,c,d) AS --处理数据库中数据
4
(
5
SELECT benji,ASCII(shangji),1,shangji FROM [dengji]
6
UNION ALL
7
SELECT benji,b+ASCII(SUBSTRING(shangji,c+1,1)),c+1,shangji FROM aa ,dengji WHERE dengji.[benji]=aa.a AND dengji.[shangji]=aa.d AND c<len(shangji)
8
),
9
bb AS
10
(
11
SELECT a,d,MAX(b)AS b FROM aa GROUP BY a,d
12
),
13
cc AS --得到处理结果
14
(
15
SELECT a,d,SUM(b) OVER (PARTITION BY a) AS b FROM bb
16
),
17
dd(b,a) AS --处理传入参数
18
(
19
SELECT ASCII(@temp),1
20
UNION ALL
21
SELECT b+ASCII(SUBSTRING(REPLACE(@temp,',',''),a+1,1)),a+1 FROM dd WHERE a<len(REPLACE(@temp,',',''))
22
),
23
ff AS --匹配数据
24
(
25
SELECT a,b,d FROM cc WHERE cc.b=(SELECT MAX(dd.b) FROM dd)
26
),
27
ee AS --验证数据正确行,二次过滤
28
(
29
SELECT *,COUNT(a) OVER (PARTITION BY a) AS c FROM ff WHERE @temp LIKE '%'+d+'%'
30
)
31
SELECT a,d FROM ee --得到结果
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
4
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
5
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
6
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
7
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
8
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
9
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
10
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
11
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
12
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
13
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
14
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
15
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
16
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
17
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
18
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
19
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
20
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
21
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
22
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
23
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
24
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
25
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
26
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
27
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
28
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
29
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
30
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
31
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
简单试了一下暂时没有发现什么问题
就是有点复杂,传入参数是以‘,’号隔开的技能