求一存储或语句的写法
表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
Go
declare @sql varchar(8000)
set @sql= ' '
select @sql=@sql+ ',min(case when A.cid= ' ' '+cid+ ' ' ' then A.price end) as [price_ '+cid+ '] '
from B
select @sql= 'select C.id,C.pid,C.pname,C.price '+@sql+ ' from A,C where A.pid=C.pid group by C.id,C.pid,C.pname,C.price '
exec(@sql)
/*
id pid pname price price_A1 price_A2 price_A3 price_A4 price_A5 price_A6 price_A7 price_A8
----------- ----------- ---------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1 1001 IBM 50 30 80 NULL NULL NULL NULL NULL NULL
2 1002 HP 25 NULL 40 NULL NULL NULL NULL NULL NULL
*/
Go
drop table a,b,c
