请用sql回答我,谢谢大家了
create table student
(
stuid char(3) primary key,
stuname varchar(20)not null,
stusex varchar(5)not null,
stuclass varchar(20)not null,
stuage smallint,
)
create table kecheng
(
kcid char(3)primary key,
kcname varchar(50),
kcteacher varchar(20)
)
create table xuankebiao
(
stuid char(3),
kcid char(3)
)
insert into student values('001','zhangsan','man','001',20)
insert into student values('002','lisi','woman','001',24)
insert into student values('003','wangwu','man','001',22)
insert into student values('004','zhouliu','woman','001',18)
insert into kecheng values('001','语文','aa')
insert into kecheng values('002','数学','bb')
insert into kecheng values('003','英语','cc')
insert into kecheng values('004','逻辑','dd')
insert into kecheng values('005','生物','ee')
insert into xuankebiao values('001','001')
insert into xuankebiao values('001','002')
insert into xuankebiao values('001','003')
insert into xuankebiao values('001','004')
求各位大哥大姐的帮忙解决一下
create table student
(
stuid char(3) primary key,
stuname varchar(20)not null,
stusex varchar(5)not null,
stuclass varchar(20)not null,
stuage smallint,
)
create table kecheng
(
kcid char(3)primary key,
kcname varchar(50),
kcteacher varchar(20)
)
create table xuankebiao
(
stuid char(3),
kcid char(3)
)
insert into student values('001','zhangsan','man','001',20)
insert into student values('002','lisi','woman','001',24)
insert into student values('003','wangwu','man','001',22)
insert into student values('004','zhouliu','woman','001',18)
insert into kecheng values('001','语文','aa')
insert into kecheng values('002','数学','bb')
insert into kecheng values('003','英语','cc')
insert into kecheng values('004','逻辑','dd')
insert into kecheng values('005','生物','ee')
insert into xuankebiao values('001','001')
insert into xuankebiao values('001','002')
insert into xuankebiao values('001','003')
insert into xuankebiao values('001','004')
求各位大哥大姐的帮忙解决一下
解决方案 »
- |zyciis| 一段SQL语句,更新分数排名,那如何得出最后分数
- 急求多表联合查询语句
- 存储过程中的like问题,困扰多时,高手解决!
- where条件、group by all 和 rollup不能同时使用,那我怎么实现这个效果呢?
- SQL大大大难题……分类检索
- 关于在Windows Xp下设置SQL Server数据库别名的问题
- 求一sql分页存储过程
- ddbms
- sql server存储过程问题:(奇怪)
- 建表时表名可以是变量吗?
- SQLSERVER2000数据库恢复问题,我死的心都有了。。。求解!
- sql server management studio express和sql server的区别
go
create table student
(
stuid char(3) primary key,
stuname varchar(20)not null,
stusex varchar(5)not null,
stuclass varchar(20)not null,
stuage smallint,
)
if object_id('kecheng') is not null drop table kecheng
go
create table kecheng
(
kcid char(3)primary key,
kcname varchar(50),
kcteacher varchar(20)
)
if object_id('xuankebiao') is not null drop table xuankebiao
go
create table xuankebiao
(
stuid char(3),
kcid char(3)
)
insert into student values('001','zhangsan','man','001',20)
insert into student values('002','lisi','woman','001',24)
insert into student values('003','wangwu','man','001',22)
insert into student values('004','zhouliu','woman','001',18)
insert into kecheng values('001','语文','aa')
insert into kecheng values('002','数学','bb')
insert into kecheng values('003','英语','cc')
insert into kecheng values('004','逻辑','dd')
insert into kecheng values('005','生物','ee')
insert into xuankebiao values('001','001')
insert into xuankebiao values('001','002')
insert into xuankebiao values('001','003')
insert into xuankebiao values('001','004')
insert into xuankebiao values('002','001')
insert into xuankebiao values('002','002')
insert into xuankebiao values('003','003')
insert into xuankebiao values('003','004');with cte as(
select distinct s.*,k.*
from student s join xuankebiao x on s.stuid=x.stuid
join kecheng k on x.kcid=k.kcid
)
select distinct stuid,stuname
,kcname=stuff((select ','+kcname from cte where stuid=t.stuid for xml path('')),1,1,'')
from cte t/*
stuid stuname kcname
------------------------------------------
001 zhangsan 语文,数学,英语,逻辑
002 lisi 语文,数学
003 wangwu 英语,逻辑
*/
use tempdb;
/*
create table student
(
stuid char(3) primary key,
stuname varchar(20)not null,
stusex varchar(5)not null,
stuclass varchar(20)not null,
stuage smallint,
)
create table kecheng
(
kcid char(3)primary key,
kcname varchar(50),
kcteacher varchar(20)
)
create table xuankebiao
(
stuid char(3),
kcid char(3)
)
insert into student values('001','zhangsan','man','001',20)
insert into student values('002','lisi','woman','001',24)
insert into student values('003','wangwu','man','001',22)
insert into student values('004','zhouliu','woman','001',18)
insert into kecheng values('001','语文','aa')
insert into kecheng values('002','数学','bb')
insert into kecheng values('003','英语','cc')
insert into kecheng values('004','逻辑','dd')
insert into kecheng values('005','生物','ee')
insert into xuankebiao values('001','001')
insert into xuankebiao values('001','002')
insert into xuankebiao values('001','003')
insert into xuankebiao values('001','004')
insert into xuankebiao values('002','001')
insert into xuankebiao values('002','002')
insert into xuankebiao values('003','003')
insert into xuankebiao values('003','004')
*/
select distinct t1.stuid,t1.stuname,
kcname=stuff((select ','+kcname from
(
select s.stuid,s.stuname,k.kcname
from kecheng as k
join xuankebiao as x on x.kcid = k.kcid
join student as s on s.stuid = x.stuid
)
as t2 where stuid=t1.stuid for xml path('')),1,1,'')
from
(
select s.stuid,s.stuname,k.kcname
from kecheng as k
join xuankebiao as x on x.kcid = k.kcid
join student as s on s.stuid = x.stuid
) as t1;