日期:2014-05-18  浏览次数:20398 次

求一存储或语句的写法
表A     --表A为销售记录表,记录销售状况

ID   PID     PRICE     QUANTITY     CID
1     1001   30           10                 A1
2     1002   40           10                 A2
3     1001   50           10                 A1
4     1001   80           10                 A2

表B     --表B为客户表

ID       CID
1         A1
2         A2
...
55       A55


表C   --表C为商品表

ID     PID           PNAME       PRICE
1       1001         IBM           50
2       1002         HP             25

表D   --为查询结果,根据商品表中每行的记录,结合客户编号,要记录任何一款商品,所有客户的销售单价

ID   PID     PNAME     PRICE       PRICE_A1         PRICE_A2         ...   PRICE_A55
1     1001   IBM         50             30                       80                   ...   NULL
2     1002   HP           30             NULL                   40                   ...   NULL


--注,price_a1,price_a2   应动态生成的,因客户资料会随时变化,本表的字段应能随机产生

------解决方案--------------------
---Try
Declare @sql Varchar(8000)
Set @sql= ' '
Select @sql=@sql+ ',Max(Case When A.PID= ' ' '+rtrim(A.PID)+ ' ' ' And A.CID= ' ' '+A.CID+ ' ' ' Then A.PRICE Else NULL End) As [PRICE_ '+A.CID+ '] '
From (Select PID,CID,MIN(PRICE) As PRICE From A Group By PID,CID) As A Group By A.PID,A.CID
Print @sql
Exec( 'Select C.ID,C.PID,C.PNAME,C.PRICE '+@sql+ 'From C Left Join
(Select PID,CID,MIN(PRICE) As PRICE From A Group By PID,CID) As A On C.PID=A.PID Group By C.ID,C.PID,C.PNAME,C.PRICE Order By C.ID ')
------解决方案--------------------


create table A(id int identity(1,1),pid int,price int,qty int,cid varchar(10))
insert into A select 1001,30,10, 'A1 '
insert into A select 1002,40,10, 'A2 '
insert into A select 1001,50,10, 'A1 '
insert into A select 1001,80,10, 'A2 '

create table B(id int identity(1,1),cid varchar(10))
insert into B select 'A1 '
insert into B select 'A2 '
insert into B select 'A3 '
insert into B select 'A4 '
insert into B select 'A5 '
insert into B select 'A6 '
insert into B select 'A7 '
insert into B select 'A8 '

create table C(id int identity(1,1),pid int,pname varchar(10),price int)
insert into C select 1001, 'IBM ',50
insert into C select 1002, 'HP ',25