近期碰到一个比较头疼的表设计问题,希望大家一起来讨论该如何来做表设计:
有三个表
Main:
FlightNo Airline FlightDate Con_Weight Con_Volume Act_Weight Set_Weight Con_PltType Act_PltType
c0001 ca 2007-12-12 1000 30 998 998Plt:
FlightNo PltType Quantiry
c0001 C2 2
C0001 C3 3PltType
PltType PltTypeDesc Weight
C2 High 900
C3 Low 890
C5 AKE 300关系如下
Main & Plt 一对多
其中 Main.Con_Weight=Sum(Plt.PltType * PltType.Weight)
Main.Con_PltType=Sum(Plt.PltType)想实现的目标:计算
Select FlightNo,(Main.Con_Weight-Main.Act_Weight),(Main.Con_Weight-Main.Set_Weight),(Main.Con_PltType-Main.Act_PltType ) from Main where FlightNo='C0001'因为PltType表中的记录可能有多条,假如按上面的主表设计方法,那么PltType中有多少条记录,则Main表就要增加多少个字段,显然不是很合理,大家有没有更好的设计方法来解决这个问题,或者是用存储过程来跑出这样的一个报表?
有三个表
Main:
FlightNo Airline FlightDate Con_Weight Con_Volume Act_Weight Set_Weight Con_PltType Act_PltType
c0001 ca 2007-12-12 1000 30 998 998Plt:
FlightNo PltType Quantiry
c0001 C2 2
C0001 C3 3PltType
PltType PltTypeDesc Weight
C2 High 900
C3 Low 890
C5 AKE 300关系如下
Main & Plt 一对多
其中 Main.Con_Weight=Sum(Plt.PltType * PltType.Weight)
Main.Con_PltType=Sum(Plt.PltType)想实现的目标:计算
Select FlightNo,(Main.Con_Weight-Main.Act_Weight),(Main.Con_Weight-Main.Set_Weight),(Main.Con_PltType-Main.Act_PltType ) from Main where FlightNo='C0001'因为PltType表中的记录可能有多条,假如按上面的主表设计方法,那么PltType中有多少条记录,则Main表就要增加多少个字段,显然不是很合理,大家有没有更好的设计方法来解决这个问题,或者是用存储过程来跑出这样的一个报表?
Plt表记录航班中具体用到的几种板型
PltType表是记录一共有多少种不同的板型想统计一下某个航班某段时间内协议重量与实际重量,协议板型与实际板型之间的差别。不知我这样的描述是否清楚?
create table dd_PltType ( -- 定义各种板型的标准, 字典表
PltType int primary key,
PltTypeDesc varchar(200)
Weight numeric(10)
)
-- C2 High 900
-- C3 Low 890
-- C5 AKE 300 create table Main ( -- 航班总体的信息
id int identity(1,1) primary key
,FlightNo
,Airline
,FlightDate
-- ,Con_Weight -- 协议重量
-- ,Con_Plt -- 协议板型
-- ,Act_Weight -- 实际占用的重量
-- ,Act_Plt -- 实际用的板型
,Set_Weight -- 结算的重量
)create table Main_ext_ConPlt ( -- 航班板型协议信息
id -- FK to Main
,PltType
,Weight
,Quantiry
)create table Main_ext_ActPlt ( -- 航班板型实际信息
id -- FK to Main
,PltType
,Weight
,Quantiry
)
PltType int primary key,
PltTypeDesc varchar(200)
Weight numeric(10)
)
-- C2 High 900
-- C3 Low 890
-- C5 AKE 300 create table Main ( -- 航班总体的信息
id int identity(1,1) primary key
,FlightNo
,Airline
,FlightDate
-- ,Con_Weight -- 协议重量
-- ,Con_Plt -- 协议板型
-- ,Act_Weight -- 实际占用的重量
-- ,Act_Plt -- 实际用的板型
,Set_Weight -- 结算的重量
)create table Main_ext_ConPlt ( -- 航班板型协议信息
id -- FK to Main
,PltType
-- ,Weight --这个重量是取自字典表的
,Quantiry
)create table Main_ext_ActPlt ( -- 航班板型实际信息
id -- FK to Main
,PltType
-- ,Weight 这个重量是取自字典表的
,Quantiry
)--问题是当我想分析某个航班的协议与实际之间的Weight 及 PltType的差别时好象有点复杂呢
select PltType,sum(Quantity) from Main_ext_ConPlt group by PltType --这里是多条记录 要转换成横向一条记录
select PltType,sum(Quantity) from Main_ext_ActPlt group by PltType --这里是多条记录 要转换成横向一条记录
PltType int primary key,
PltTypeDesc varchar(200)
Weight numeric(10)
)
-- C2 High 900
-- C3 Low 890
-- C5 AKE 300 create table Main ( -- 航班总体的信息
id int identity(1,1) primary key
,FlightNo
,Airline
,FlightDate
-- ,Con_Weight -- 协议重量
-- ,Con_Plt -- 协议板型
-- ,Act_Weight -- 实际占用的重量
-- ,Act_Plt -- 实际用的板型
,Set_Weight -- 结算的重量
)create table Main_ext_ConPlt ( -- 航班板型协议信息
id -- FK to Main
,PltType
-- ,Weight --这个重量是取自字典表的
,Quantiry
)create table Main_ext_ActPlt ( -- 航班板型实际信息
id -- FK to Main
,PltType
-- ,Weight 这个重量是取自字典表的
,Quantiry
)--问题是当我想分析某个航班的协议与实际之间的Weight 及 PltType的差别时好象有点复杂呢
select PltType,sum(Quantity) from Main_ext_ConPlt group by PltType --这里是多条记录 要转换成横向一条记录
select PltType,sum(Quantity) from Main_ext_ActPlt group by PltType --这里是多条记录 要转换成横向一条记录
得到的是某个具体航班的各种协议板型汇总,select PltType,sum(Quantity) from Main_ext_ActPlt where id = @id group by PltType
得到的是某个具体航班的各种实际板型汇总,select distinct pltType
from (select pltType from Main_ext_ConPlt where id = @id
union
select pltType from Main_ext_ActPlt where id = @id )
得到的是该航班涉及的所有板型列表,组织起来,就得到了指定航班的各种板型协议与实际的对比select p.pltType, c.conQuantity, a.actQuantity
from (
select distinct pltType
from (select pltType from Main_ext_ConPlt where id = @id union
select pltType from Main_ext_ActPlt where id = @id )
) as p
left join (
select PltType,conQuantity=sum(Quantity) from Main_ext_ConPlt where id = @id group by PltType
) as c on c.PltType=p.PltType
left join (
select PltType,actQuantity=sum(Quantity) from Main_ext_ActPlt where id = @id group by PltType
) as a on a.PltType=p.PltType
select p.pltType, c.conQuantity, a.actQuantity --这个查询是取自SELECT子表,但语法好象有问题
from (
select distinct pltType
from (select pltType from Main_ext_ConPlt where id = @id union
select pltType from Main_ext_ActPlt where id = @id )--这个结尾的)应该去掉吗?
) as p
left join (
select PltType,conQuantity=sum(Quantity) from Main_ext_ConPlt where id = @id group by PltType
) as c on c.PltType=p.PltType
left join (
select PltType,actQuantity=sum(Quantity) from Main_ext_ActPlt where id = @id group by PltType
) as a on a.PltType=p.PltType
--以前从来没有注意过获取SELECT子查询中的结果有重复的字段怎么处理,能否麻烦大侠把这个语句调通了?
PltType int primary key,
PltTypeDesc varchar(200)
Weight numeric(10)
)
-- C2 High 900
-- C3 Low 890
-- C5 AKE 300 create table Main ( -- 航班总体的信息
id int identity(1,1) primary key
,FlightNo
,Airline
,FlightDate
-- ,Con_Weight -- 协议重量
-- ,Con_Plt -- 协议板型
-- ,Act_Weight -- 实际占用的重量
-- ,Act_Plt -- 实际用的板型
,Set_Weight -- 结算的重量
)create table Main_ext_ConPlt ( -- 航班板型协议信息
id -- FK to Main
,PltType
-- ,Weight --这个重量是取自字典表的
,Quantiry
)create table Main_ext_ActPlt ( -- 航班板型实际信息
id -- FK to Main
,PltType
-- ,Weight 这个重量是取自字典表的
,Quantiry
)--问题是当我想分析某个航班的协议与实际之间的Weight 及 PltType的差别时好象有点复杂呢
select PltType,sum(Quantity) from Main_ext_ConPlt group by PltType --这里是多条记录 要转换成横向一条记录
select PltType,sum(Quantity) from Main_ext_ActPlt group by PltType --这里是多条记录 要转换成横向一条记录
PltType int primary key,
PltTypeDesc varchar(200)
Weight numeric(10)
)
-- C2 High 900
-- C3 Low 890
-- C5 AKE 300 create table Main ( -- 航班总体的信息
id int identity(1,1) primary key
,FlightNo
,Airline
,FlightDate
-- ,Con_Weight -- 协议重量
-- ,Con_Plt -- 协议板型
-- ,Act_Weight -- 实际占用的重量
-- ,Act_Plt -- 实际用的板型
,Set_Weight -- 结算的重量
)create table Main_ext_ConPlt ( -- 航班板型协议信息
id -- FK to Main
,PltType
-- ,Weight --这个重量是取自字典表的
,Quantiry
)create table Main_ext_ActPlt ( -- 航班板型实际信息
id -- FK to Main
,PltType
-- ,Weight 这个重量是取自字典表的
,Quantiry
)--问题是当我想分析某个航班的协议与实际之间的Weight 及 PltType的差别时好象有点复杂呢
select PltType,sum(Quantity) from Main_ext_ConPlt group by PltType --这里是多条记录 要转换成横向一条记录
select PltType,sum(Quantity) from Main_ext_ActPlt group by PltType --这里是多条记录 要转换成横向一条记录
PltType int primary key,
PltTypeDesc varchar(200)
Weight numeric(10)
)
-- C2 High 900
-- C3 Low 890
-- C5 AKE 300 create table Main ( -- 航班总体的信息
id int identity(1,1) primary key
,FlightNo
,Airline
,FlightDate
-- ,Con_Weight -- 协议重量
-- ,Con_Plt -- 协议板型
-- ,Act_Weight -- 实际占用的重量
-- ,Act_Plt -- 实际用的板型
,Set_Weight -- 结算的重量
)create table Main_ext_ConPlt ( -- 航班板型协议信息
id -- FK to Main
,PltType
-- ,Weight --这个重量是取自字典表的
,Quantiry
)create table Main_ext_ActPlt ( -- 航班板型实际信息
id -- FK to Main
,PltType
-- ,Weight 这个重量是取自字典表的
,Quantiry
)
select PltType,sum(Quantity) from Main_ext_ConPlt group by PltType --这里是多条记录 要转换成横向一条记录
select PltType,sum(Quantity) from Main_ext_ActPlt group by PltType --这里是多条记录 要转换成横向一条记录
declare @id intselect p.pltType, c.conQuantity, a.actQuantity
from (
select distinct pltType
from (select pltType from Main_ext_ConPlt where id = @id union
select pltType from Main_ext_ActPlt where id = @id ) as a -- 此处更正
) as p
left join (
select PltType,conQuantity=sum(Quantiry) from Main_ext_ConPlt where id = @id group by PltType -- 原拼写错误
) as c on c.PltType=p.PltType
left join (
select PltType,actQuantity=sum(Quantiry) from Main_ext_ActPlt where id = @id group by PltType -- 原拼写错误
) as a on a.PltType=p.PltType
goSorry, every one.