ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 由一个博问学到的SQL查询方法 (一道多对多关系查询的面试题)

由一个博问学到的SQL查询方法 (一道多对多关系查询的面试题)

原创 Linux操作系统 作者:iSQlServer 时间:2010-12-07 13:52:59 0 删除 编辑

多对对关系查询

 悬赏分:5 [已解决问题] 浏览:52 次
00

学生表  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


这就已经是问题所需要的正确答案了,由于是自己打草稿写着玩,代码相当混乱,多表查询写的也不规范,而且我只是个小菜鸟,欢迎高手写出来简洁,优雅的查询语句供我学习。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16436858/viewspace-681073/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-10-17

  • 博文量
    1319
  • 访问量
    2079673