A股上市公司传智教育(股票代码 003032)旗下技术交流社区北京昌平校区

 找回密码
 加入黑马

QQ登录

只需一步,快速开始

© 于驭龙 中级黑马   /  2013-8-12 01:59  /  1489 人查看  /  2 人回复  /   0 人收藏 转载请遵从CC协议 禁止商业使用本文

一个表还好用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)
求破!

2 个回复

倒序浏览
楼主是这个意思?
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, 下载次数: 19)

jt.jpg
回复 使用道具 举报
本帖最后由 咖喱猫 于 2013-8-12 09:38 编辑

  1. <P>
  2. --动态方法
  3. begin
  4. DECLARE @name  varchar(8000);
  5. set @name='select 0'
  6. select @name =@name+',sum( case 民族 when '''+ 民族+ ''' then 人数 else 0 end) ['+民族+'] '
  7. from
  8. (select count(Student.mzId)as 人数,Minzu.name as 民族 from Minzu
  9. INNER JOIN
  10. dbo.Student ON dbo.Minzu.id = dbo.Student.mzId
  11. group by Minzu.name )as e1

  12. select @name=@name+' from(
  13. (select count(Student.mzId)as 人数,Minzu.name as 民族 from Minzu
  14. INNER JOIN
  15. dbo.Student ON dbo.Minzu.id = dbo.Student.mzId
  16. group by Minzu.name ))as e1 '
  17. end
  18. exec(@name</P>
  19. <P>
  20. --静态方法
  21. select sum( case 民族 when '藏族' then 人数 else 0 end) [藏族],
  22. sum( case 民族 when '朝鲜族' then 人数 else 0 end) [朝鲜族],
  23. sum( case 民族 when '汉族' then 人数 else 0 end) [汉族],
  24. sum( case 民族 when '苗族' then 人数 else 0 end) [苗族],
  25. sum( case 民族 when '维吾尔族' then 人数 else 0 end) [维吾尔族]
  26. from(
  27. (select count(Student.mzId)as 人数,Minzu.name as 民族 from Minzu
  28. INNER JOIN
  29. dbo.Student ON dbo.Minzu.id = dbo.Student.mzId
  30. group by Minzu.name ))as e1</P>
复制代码

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

回复 使用道具 举报
您需要登录后才可以回帖 登录 | 加入黑马