今天在问题提问区看到一个问题
数据库里有一个表，主要有两个字段 [姓名]，[技能]
可能的数据如下
[姓名] [技能]
甲 技能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) --申明变量， 也就是输入参数
2SET @temp='a,b,c,d,e,f,g,h,aa' --为变量赋值
3WITH 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),
9bb AS
10(
11 SELECT a,d,MAX(b)AS b FROM aa GROUP BY a,d
12),
13cc AS --得到处理结果
14(
15 SELECT a,d,SUM(b) OVER (PARTITION BY a) AS b FROM bb
16),
17dd(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),
23ff AS --匹配数据
24(
25 SELECT a,b,d FROM cc WHERE cc.b=(SELECT MAX(dd.b) FROM dd)
26),
27ee AS --验证数据正确行，二次过滤
28(
29 SELECT *,COUNT(a) OVER (PARTITION BY a) AS c FROM ff WHERE @temp LIKE '%'+d+'%'
30)
31SELECT a,d FROM ee --得到结果
简单试了一下暂时没有发现什么问题
就是有点复杂，传入参数是以‘，’号隔开的技能
posted on 2008-07-19 11:20
KindSoul 阅读(973)
评论(15) 编辑 收藏
发表评论
--测试数据
DECLARE @t TABLE(UserName varchar(10) ,Skill varchar(10))
INSERT @t
SELECT '甲','技能1'
UNION ALL SELECT '甲','技能2'
UNION ALL SELECT '甲','技能3'
UNION ALL SELECT '乙','技能1'
UNION ALL SELECT '乙','技能2'
UNION ALL SELECT '乙','技能4'
UNION ALL SELECT '丙','技能2'
UNION ALL SELECT '丙','技能3'
UNION ALL SELECT '丁','技能1'
UNION ALL SELECT '丁','技能4'
SELECT t1.* FROM @t AS t1
LEFT JOIN @t AS t2 ON t2.UserName=t1.UserName
WHERE t1.Skill='技能1' AND t2.Skill='技能2'
UNION
SELECT t2.* FROM @t AS t1
LEFT JOIN @t AS t2 ON t2.UserName=t1.UserName
WHERE t1.Skill='技能1' AND t2.Skill='技能2'
ORDER BY t1.UserName
有这么夸张吗？很简单的的问题啊
ls的写的有点繁琐，明明不用左联结的
SELECT t1.* FROM @t AS t1
JOIN @t AS t2 ON t2.UserName=t1.UserName
WHERE (t1.Skill='技能1' AND t2.Skill='技能2')
OR (t1.Skill='技能2' AND t2.Skill='技能1')
This is a standard SQL query. The problem can be solved by using
1. Having clause
2. Count on having clause.
The variation can be around the input parameters. Alternatively there may be other methods, but we should try to use the simple one which is easy to undestand and maintain.
The solution above is no better than one of answers in the original post.
ERIC
简单问题复杂话了。
--> --> (Andy)生成测试数据 2008-07-19
Set Nocount On
declare @1 table([c1] nvarchar(1),[c2] nvarchar(3))
Insert @1
select N'甲',N'技能1' union all
select N'甲',N'技能2' union all
select N'甲',N'技能3' union all
select N'乙',N'技能1' union all
select N'乙',N'技能2' union all
select N'乙',N'技能4' union all
select N'丙',N'技能2' union all
select N'丙',N'技能3' union all
select N'丁',N'技能1' union all
select N'丁',N'技能4'
Select * from @1 a Where
Exists(Select 1 From @1 Where c1=a.c1 And c2 In('技能1','技能2') Having Count(*)=2)
可以用子查询不
select 用户名,技能 from
人员技能表
where 用户名 in
(select 用户名 from (select 用户名,count(*) AS count from 人员技能表 where 技能 in('技能1','技能2') group by 用户名) AS 人员 where count>=2 )
我试了一下楼上的,用谓词的查询.改成这样才正确.Set Nocount On
declare @1 table([c1] nvarchar(1),[c2] nvarchar(3))
Insert @1
select N'甲',N'技能1' union all
select N'甲',N'技能2' union all
select N'甲',N'技能3' union all
select N'乙',N'技能1' union all
select N'乙',N'技能2' union all
select N'乙',N'技能4' union all
select N'丙',N'技能2' union all
select N'丙',N'技能3' union all
select N'丁',N'技能1' union all
select N'丁',N'技能4'
Select * from @1 a Where
Exists(Select 1 From @1 Where c1=a.c1 And c2 In('技能1','技能2') Having Count(*)=2) and c2 In('技能1','技能2')
因为在谓词的子句中只是按名字匹配的,则如果谓词成功后即可以筛选出来,这样就会将不在技能1和技能2的记录查出来了.另外,这个也可以改成in的形式,不列出来了,稍改一下就行了.
BTW:最近学了数据库系统原理,这些东西在上面都有.另外,要想深入的理解,还需要点离散数学的知识.否则那些表达式很难看懂....努力补充中....
select *
from T a
where
exists (select 1
from T
where [姓名] = a.[姓名] and [技能] = '技能1')
and
exists (select 1
from T
where [姓名] = a.[姓名] and [技能] = '技能2')
SELECT a.[姓名]
FROM T a
INNER JOIN
(
select '技能1' as [技能] union all select '技能2'
) b
on a.[技能] = b.[技能]
group by a.[姓名]
having count(distinct a.[技能]) = 2
@这样如何？
如果他让你查询同时掌握技能1技能2技能3的呢。
其实我考虑的主要是拓展性，你们以上写的方法都可行，但尽限于一次查询，或这只限于查询两个变量的。如果变量多了的呢。尤其是我们要考虑的做的这个功能肯定要和程序想对应的，程序中的选择和筛选肯定是多样变化的。
--引用--------------------------------------------------
KindSoul: 其实我考虑的主要是拓展性，你们以上写的方法都可行，但尽限于一次查询，或这只限于查询两个变量的。如果变量多了的呢。尤其是我们要考虑的做的这个功能肯定要和程序想对应的，程序中的选择和筛选肯定是多样变化的。
--------------------------------------------------------
呵呵，用子查询应该可以实现多次使用的，
@Shinn
没有想到用子查询拓展性的方法。可以给我说一下么