黑马程序员技术交流社区

标题: SQL行列倒转问题 [打印本页]

---

作者: 于驭龙    时间: 2013-8-12 01:59
标题: SQL行列倒转问题
一个表还好用CASE处理
但是关联表上处理就不行了 ..实在不懂原理~~

create database T69

go
use T69
go
create table Score
(
   id int primary key identity(1,1),
   [sid] int not null,
   Java int ,
   [Sql] int

)

go

create table Minzu
(
    id int primary key identity(1,1),
    name varchar(50) not null

)

go
create table Student
(
   id int primary key identity(1,1),
   name varchar(20) not null,
   age int not null,
   mzId int not null,
   
)

go
alter table score
add constraint fk_score_sid foreign key ([sid])
references student(id)

alter table student
add constraint fk_student_mzid foreign key ([mzid])
references minzu(id)
go

insert into Minzu values('维吾尔族')
insert into Minzu values('汉族')
insert into Minzu values('苗族')
insert into Minzu values('藏族')
insert into Minzu values('朝鲜族')
insert into Minzu values('俄罗斯族')
go
insert into Student values('a',20,2)
insert into Student values('b',25,2)
insert into Student values('c',29,3)
insert into Student values('d',20,1)
insert into Student values('e',20,1)
insert into Student values('f',30,1)
insert into Student values('g',35,1)
insert into Student values('h',33,1)
insert into Student values('i',35,3)
insert into Student values('j',18,4)
insert into Student values('k',19,4)
insert into Student values('l',17,4)
insert into Student values('m',40,1)
insert into Student values('n',41,1)
insert into Student values('o',38,1)
insert into Student values('p',27,1)
insert into Student values('q',26,1)
insert into Student values('r',24,5)
insert into Student values('s',24,1)
go
insert into Score values(1,50,60)
insert into Score values(2,89,70)
insert into Score values(3,88,80)
insert into Score values(4,90,45)
insert into Score values(5,90,95)
insert into Score values(6,65,75)
insert into Score values(7,70,60)
insert into Score values(8,72,73)
insert into Score values(9,80,45)
insert into Score values(10,90,100)
insert into Score values(11,100,60)
insert into Score values(12,35,60)
insert into Score values(13,null,60)
insert into Score values(14,null,null)
求破!

---

作者: 小天    时间: 2013-8-12 08:41
楼主是这个意思？
sql语句
select a.id,a.name,a.age,c.name,b.java,b.sql from Student a inner join Score b on a.id=b.sid inner join Minzu c on a.mzid=c.id

jt.jpg (36.76 KB, 下载次数: 13)

下载附件

2013-8-12 08:40 上传

---

作者: 咖喱猫    时间: 2013-8-12 09:35

1. 
   
2. <P>
   
3. --动态方法
   
4. begin
   
5. DECLARE @name  varchar(8000);
   
6. set @name='select 0'
   
7. select @name =@name+',sum( case 民族 when '''+ 民族+ ''' then 人数 else 0 end) ['+民族+'] '
   
8. from
   
9. (select count(Student.mzId)as 人数,Minzu.name as 民族 from Minzu
   
10. INNER JOIN
    
11. dbo.Student ON dbo.Minzu.id = dbo.Student.mzId
    
12. group by Minzu.name )as e1
    
13. 
    
14. select @name=@name+' from(
    
15. (select count(Student.mzId)as 人数,Minzu.name as 民族 from Minzu
    
16. INNER JOIN
    
17. dbo.Student ON dbo.Minzu.id = dbo.Student.mzId
    
18. group by Minzu.name ))as e1 '
    
19. end
    
20. exec(@name</P>
    
21. <P>
    
22. --静态方法
    
23. select sum( case 民族 when '藏族' then 人数 else 0 end) [藏族],
    
24. sum( case 民族 when '朝鲜族' then 人数 else 0 end) [朝鲜族],
    
25. sum( case 民族 when '汉族' then 人数 else 0 end) [汉族],
    
26. sum( case 民族 when '苗族' then 人数 else 0 end) [苗族],
    
27. sum( case 民族 when '维吾尔族' then 人数 else 0 end) [维吾尔族]
    
28. from(
    
29. (select count(Student.mzId)as 人数,Minzu.name as 民族 from Minzu
    
30. INNER JOIN
    
31. dbo.Student ON dbo.Minzu.id = dbo.Student.mzId
    
32. group by Minzu.name ))as e1</P>

复制代码

应该是这样吧，这才叫行列倒转

---

欢迎光临 黑马程序员技术交流社区 (http://bbs.itheima.com/)

黑马程序员IT技术论坛 X3.2