悬赏分：5 [已解决问题] 浏览:52 次学生表 t1
|
Stu_id |
Stu_name |
|
1 |
张三 |
|
2 |
李四 |
|
3 |
王五 |
课程表 t2
|
Co_id |
Co_name |
|
1 |
Java |
|
2 |
C# |
|
3 |
C |
|
4 |
C++ |
|
5 |
Javascript |
关系表 t3
|
Stu_id |
Co_id |
|
1 |
1 |
|
1 |
4 |
|
2 |
1 |
|
2 |
3 |
|
2 |
5 |
前几天去面试被问了一道关于数据库的问题。到现在也没找到答案。
题目是这样的：有学生表、课程表和关系表，查出学生所选的课程。
我当时就想通过连接查询。当然这样的话会出现重复出现同一个学生姓名的情况。如：
|
张三 |
Java |
|
张三 |
C++ |
可是题目要求出现的情况是：张三 java,c++
请问这个SQL该如何写。
]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]荡漾的分割线]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]
当时看到这个博问后，仔细想了以前学的SQL知识，发现似乎只能先把查询结果赋值给变量，然后再查询一次。但这个题目的意思显然不是这样做的，于是我把问题发到闪存区期望高手解答。今天上班后打开看了下，发现已经有了答案，于是在自己数据库实验了下，结果答案不完整直接运行失败。于是我仔细看了下答案内容，发现了2个不认识的东东 stuff()函数 和 for xml path。之后我就去网上搜索关于这2个的意义。
stuff(str1,start,length,str2)比较简单，是一个可以设置起始位置的替换字符串函数。
str1是原始字符串。
start是要替换的起始位置。
length是要替换原始字符串内容的长度。
str2是替换的新内容。
例子：stuff('12345',2,2,'000') 的结果为'100045'，既'12345'的第二位'2'开始，长度为二，既'23'，替换为'000'，得到结果'100045'。当然也有其他的简单方法可以做到同样效果
for xml path则复杂一些，是将查询到的结果生成XML数据。具体用法可以去这里看http://www.docin.com/p-73893088.html
大致上对for xml path似懂非懂后我就开始尝试。
先是通过几次尝试后写出 select ','+co_name from t2 where co_id=1 or co_id=2 for xml path('') 查询得到 ".java.C#"
然后确定了方向，我需要通过 t3 表中的 stu_id 找出相关联的 co_id，然后通过上面的语句就能得到正确的答案。
解题思路正确了，得到答案不过是水到渠成的问题，通过多次尝试，得到语句
select distinct stu_id,(select convert(varchar(20),co_id)+',' from t3 where a.stu_id=stu_id for xml path('')) from t3 a
成功按照要求查出了学生ID对应的课程ID，之后将学生ID换成学生Name
select distinct stu_name,(select convert(varchar(20),co_id)+',' from t3 where a.stu_id=stu_id for xml path('')) from t3 a,t1 b where a.stu_id=b.stu_id
再之后将课程ID换成课程Name，并且使用stuff()函数将第一个','替换为空
select distinct stu_name,stuff((select ','+co_name from t3,t2 where a.stu_id=stu_id and t3.co_id=t2.co_id for xml path('')),1,1,'') from t3 a,t1 b where a.stu_id=b.stu_id
这就已经是问题所需要的正确答案了，由于是自己打草稿写着玩，代码相当混乱，多表查询写的也不规范，而且我只是个小菜鸟，欢迎高手写出来简洁，优雅的查询语句供我学习。
谢谢！