求交叉表的SQL
求交叉表的SQL,三个表,结果如下
(物品(假设3个),客户(假设3个),销售发货表(假设9条))
要求结果如4.
-------------------------------------------
1,物品表: UB_Articles
ArticleID ArticleName
INT(4) NVARCHAR(10)
1 物品1
2 物品2
3 物品3
. .
. .
N 物品N
(更多物品)
-------------------------------------------
2,客户表: UB_Customers
CustomerID CustomerName
INT(4) NVARCHAR(50)
1 客户1
2 客户2
3 客户3
. .
. .
N 客户N
(更多客户)
-------------------------------------------
3,销售发货主表: UD_SaleInvoices
SaleInvoiceID ArticleID CustomerID Desp
INT(4) INT(4) INT(4) NVARCHAR(50)
1 1 1 a
2 1 2 b
3 1 3 c
1 2 1 d
2 2 2 e
3 2 3 f
1 3 1 g
2 3 2 h
3 3 3 i
. . . .
. . . .
. . . .
(更多单据)
-------------------------------------------
4,要得到结果:
ArticleName 客户1 1 客户2 2 客户3 3 ........(更多客户)
物品1 a 1 b 2 c 3
物品2 d 1 e 2 f 3
物品3 g 1 h 2 i 3
. . . . . . .
. . . . . . .
. . . . . . .
. . . . . . .
(更多物品)
说明：结果列中的1，2，3表示CustomerID
也就是说在列‘客户1’后的列‘1’指的是客户1的CustomerID
-------------------------------------------
最好给出优化的 SQL
------解决方案--------------------
if object_id( 'pubs..UB_Articles ') is not null
drop table UB_Articles
go
create table UB_Articles(ArticleID INT,ArticleName VARCHAR(10))
insert into UB_Articles(ArticleID,ArticleName) values(1, '物品1 ')
insert into UB_Articles(ArticleID,ArticleName) values(2, '物品2 ')
insert into UB_Articles(ArticleID,ArticleName) values(3, '物品3 ')
go
if object_id( 'pubs..UB_Customers ') is not null
drop table UB_Customers
go
create table UB_Customers(CustomerID int,CustomerName VARCHAR(50))
insert into UB_Customers(CustomerID,CustomerName) values(1, '客户1 ')
insert into UB_Customers(CustomerID,CustomerName) values(2, '客户2 ')
insert into UB_Customers(CustomerID,CustomerName) values(3, '客户3 ')
go
if object_id( 'pubs..UD_SaleInvoices ') is not null
drop table UD_SaleInvoices
go
create table UD_SaleInvoices(SaleInvoiceID int,ArticleID int,CustomerID int,Desp varchar(10))
insert into UD_SaleInvoices(SaleInvoiceID,ArticleID,CustomerID,Desp) values(1,1,1, 'a ')
insert into UD_SaleInvoices(SaleInvoiceID,ArticleID,CustomerID,Desp) values(2,1,2, 'b ')
insert into UD_SaleInvoices(SaleInvoiceID,ArticleID,CustomerID,Desp) values(3,1,3, 'c ')
insert into UD_SaleInvoices(SaleInvoiceID,ArticleID,CustomerID,Desp) values(1,2,1, 'd ')
insert into UD_SaleInvoices(SaleInvoiceID,ArticleID,CustomerID,Desp) values(2,2,2, 'e ')
insert into UD_SaleInvoices(SaleInvoiceID,ArticleID,CustomerID,Desp) values(3,2,3, 'f ')
insert into UD_SaleInvoices(SaleInvoiceID,ArticleID,CustomerID,Desp) values(1,3,1, 'g ')
insert into UD_SaleInvoices(SaleInvoiceID,ArticleID,CustomerID,Desp) values(2,3,2, 'h ')
insert into UD_SaleInvoices(SaleInvoiceID,ArticleID,CustomerID,Desp) values(3,3,3, 'i ')
go
declare @sql varchar(8000)
set @sql = 'select ArticleName '
select @sql = @sql + ' , max(case CustomerName when ' ' ' + CustomerName + ' ' ' then desp end) [ ' + CustomerName + '] '
+ ' , max(case CustomerName when ' ' ' + CustomerName + ' ' ' then SaleInvoiceID end) [ ' + CustomerName + '] '
from (select distinct CustomerName from (select a.ArticleName,b.CustomerName,c.SaleInvoiceID,c.Desp from UB_Articles a, UB_Customers b , UD_SaleInvoices c where a.ArticleID = c.ArticleID and b.CustomerID = c.CustomerID) t) as a
set @sql = @sql + ' from (select a.ArticleName,b.CustomerName,c.SaleInvoiceID,c.Desp from UB_Articles a, UB_Customers b , UD_SaleInvoices c where a.ArticleID = c.ArticleID and b.CustomerID = c.CustomerID) t group by ArticleName '
exec(@sql)
drop table UB_Articles
drop table UB_Customers
drop table UD_SaleInvoices
/*
ArticleName 客户1 客户1 客户2 客户2 客户3 客户3
----------- ---------- ----------- ---------- ----------- ---------- -----------
物品1 a 1 b 2 c 3
物品2 d 1 e 2 f 3
物品3 g 1 h 2 i 3
*/
[解决办法]
--drop table UB_Articles,drop table UB_Customers,UD_SaleInvoices
create table UB_Articles(ArticleID INT,ArticleName VARCHAR(10))
insert into UB_Articles(ArticleID,ArticleName) values(1, '物品1 ')
insert into UB_Articles(ArticleID,ArticleName) values(2, '物品2 ')
insert into UB_Articles(ArticleID,ArticleName) values(3, '物品3 ')
go
create table UB_Customers(CustomerID int,CustomerName VARCHAR(50))
insert into UB_Customers(CustomerID,CustomerName) values(1, '客户1 ')
insert into UB_Customers(CustomerID,CustomerName) values(2, '客户2 ')
insert into UB_Customers(CustomerID,CustomerName) values(3, '客户3 ')
go
create table UD_SaleInvoices(SaleInvoiceID int,ArticleID int,CustomerID int,Desp varchar(10))
insert into UD_SaleInvoices(SaleInvoiceID,ArticleID,CustomerID,Desp) values(1,1,1, 'a ')
insert into UD_SaleInvoices(SaleInvoiceID,ArticleID,CustomerID,Desp) values(2,1,2, 'b ')
insert into UD_SaleInvoices(SaleInvoiceID,ArticleID,CustomerID,Desp) values(3,1,3, 'c ')
insert into UD_SaleInvoices(SaleInvoiceID,ArticleID,CustomerID,Desp) values(1,2,1, 'd ')
insert into UD_SaleInvoices(SaleInvoiceID,ArticleID,CustomerID,Desp) values(2,2,2, 'e ')
insert into UD_SaleInvoices(SaleInvoiceID,ArticleID,CustomerID,Desp) values(3,2,3, 'f ')
insert into UD_SaleInvoices(SaleInvoiceID,ArticleID,CustomerID,Desp) values(1,3,1, 'g ')
insert into UD_SaleInvoices(SaleInvoiceID,ArticleID,CustomerID,Desp) values(2,3,2, 'h ')
insert into UD_SaleInvoices(SaleInvoiceID,ArticleID,CustomerID,Desp) values(3,3,3, 'i ')
--drop table UB_Articles,drop table UB_Customers,UD_SaleInvoices
declare @s varchar(4000)
set @s= ' '
select @s=@s+ ', '+quotename(a.CustomerName)+ '=max( case b.CustomerID when '+rtrim(a.CustomerID)
+ ' then Desp end), '+quotename(a.CustomerID)+ '=max( case b.CustomerID when '+rtrim(a.CustomerID)
+ ' then b.CustomerID end) '
from UB_Customers a
group by a.CustomerName,a.CustomerID order by CustomerID
set @s= 'select a.ArticleName '+@s+ ' from UB_Articles a join UD_SaleInvoices b on a.ArticleID=b.ArticleID group by a.ArticleName '
exec(@s)
ArticleName 客户1 1 客户2 2 客户3 3
----------- ---------- ----------- ---------- ----------- ---------- -----------
物品1 a 1 b 2 c 3
物品2 d 1 e 2 f 3
物品3 g 1 h 2 i 3
警告: 聚合或其他 SET 操作消除了空值。
(3 行受影响)
