A表 订单
Id 主键
number 编号
……B表 费用类型
Id 主键
Name 费用名称C表 费用金额
Id 主键
Amount 金额
OrderId 订单表外键
Type 费用类型表外键
现在想要查询出所有订单的所有费用,就像下面这张表订单编号 订单信息A 订单信息B 费用A 费用B ……
00001 XXX XXXX 100 200 ……
00002 XXX XXXX 100 200 ……蓝色是a表内容 红色是b表内容 绿色是c表内容
Id 主键
number 编号
……B表 费用类型
Id 主键
Name 费用名称C表 费用金额
Id 主键
Amount 金额
OrderId 订单表外键
Type 费用类型表外键
现在想要查询出所有订单的所有费用,就像下面这张表订单编号 订单信息A 订单信息B 费用A 费用B ……
00001 XXX XXXX 100 200 ……
00002 XXX XXXX 100 200 ……蓝色是a表内容 红色是b表内容 绿色是c表内容
set @sql = 'select a.number '
select @sql = @sql + ' , max(case b.Name when ''' + name + ''' then b.Name else '''' end) [订单信息' + name + ']'
+ ' , max(case b.Name when ''' + name + ''' then c.Amount else 0 end) [费用' + name + ']'
from (select distinct Name from (select a.number , b.Name , c.Amount from a, b , c where a.id = c.OrderId and c.Type = b.Id)t) as a
set @sql = @sql + ' from a, b , c where a.id = c.OrderId and c.Type = b.Id group by a.number'
exec(@sql)
create table StuInfo
(D_Id int identity(1,1), D_StuInfoId varchar(10), D_Name varchar(10), D_ClassInfoId int)
insert StuInfo
select '20090101', '小李', 1 union all
select '20090102', '小王' ,1 union all
select '20090103', '小张', 1
--课程表
create table CourseInfo
(D_Id int identity(1,1), D_Name varchar(10))
insert CourseInfo
select '语文' union all
select '数学' union all
select '英语'--成绩表
create table 成绩表
(D_Id int identity(1,1), D_StuInfoId int, D_CourseInfoId int,D_Mark int)
insert 成绩表
select 1, 1 ,100 union all
select 1 ,2 ,99 union all
select 2 ,1 ,78 union all
select 2 ,3 ,60
--SQL SERVER 2000 静态SQL
select s.D_Id as 学号 ,
max(case c.D_Name when '语文' then j.D_Mark else 0 end) 语文,
max(case c.D_Name when '数学' then j.D_Mark else 0 end) 数学,
max(case c.D_Name when '英语' then j.D_Mark else 0 end) 英语
from CourseInfo c left join 成绩表 j on c.D_Id=j.D_CourseInfoId right join StuInfo s on s.D_Id=j.D_StuInfoId
group by s.D_Id --SQL SERVER 2000 动态SQL,
declare @sql varchar(8000)
set @sql = 'select s.D_Id as 学号 '
select @sql = @sql + ' , max(case c.D_Name when ''' + D_Name + ''' then j.D_Mark else 0 end) [' + D_Name + ']'
from CourseInfo
set @sql = @sql + ' from CourseInfo c left join 成绩表 j on c.D_Id=j.D_CourseInfoId right join StuInfo s on s.D_Id=j.D_StuInfoId group by s.D_Id '
exec(@sql) /*学号 语文 数学 英语
----------- ----------- ----------- -----------
1 100 99 0
2 78 0 60
3 0 0 0(所影响的行数为 3 行)*/
insert into a values(1,'00001')
insert into a values(2,'00002')
create table B(Id int,Name varchar(10))
insert into b values(1,'A')
insert into b values(2,'B')
create table C(Id int,Amount int,OrderId int,Type int)
insert into c values(1,100,1,1)
insert into c values(2,200,1,2)
insert into c values(3,100,2,1)
insert into c values(4,200,2,2)
go--sql2000 静态,指类型固定为A,Bselect a.number ,
max(case b.name when 'A' then b.name else '' end) [订单信息A],
max(case b.name when 'A' then c.Amount else 0 end) [费用A],
max(case b.name when 'B' then b.name else '' end) [订单信息B],
max(case b.name when 'B' then c.Amount else 0 end) [费用B]
from a, b , c where a.id = c.OrderId and c.Type = b.Id
group by a.number
/*
number 订单信息A 费用A 订单信息B 费用B
---------- ---------- ----------- ---------- -----------
00001 A 100 B 200
00002 A 100 B 200
*/--sql2000 动态,指类型不固定
declare @sql varchar(8000)
set @sql = 'select a.number '
select @sql = @sql + ' , max(case b.Name when ''' + name + ''' then b.Name else '''' end) [订单信息' + name + ']'
+ ' , max(case b.Name when ''' + name + ''' then c.Amount else 0 end) [费用' + name + ']'
from (select distinct Name from (select a.number , b.Name , c.Amount from a, b , c where a.id = c.OrderId and c.Type = b.Id)t) as a
set @sql = @sql + ' from a, b , c where a.id = c.OrderId and c.Type = b.Id group by a.number'
exec(@sql)
/*
number 订单信息A 费用A 订单信息B 费用B
---------- ---------- ----------- ---------- -----------
00001 A 100 B 200
00002 A 100 B 200
*/
drop table a , b , c