销售记录:一笔交易中的不同商品会有不同的付款方式,预先设定了一笔交易中不能存在4种以上的付款方式
create table original (transactionNO int,productCode nvarchar(30),
qty int,paymentType nvarchar(30),TransDate datetime)
innsert into original
select 1,'00258975',2,'CASH','2005-01-02'
union select 1,'00344533',1,'MASTERCARD','2005-01-02'
union select 1,'00258421',4,'VISACARD','2005-01-02'
union select 1,'67452123',1,'CASH','2005-01-02'
union select 2,'00344533',1,'MASTERCARD','2005-01-03'
union select 2,'00258421',4,'INTERNALCARD','2005-01-03'
union select 2,'67452123',1,'CASH','2005-01-03'create table present (transactionNO int,qty int,paymentType1 nvarchar(30),paymentType2 nvarchar(30),paymentType3 nvarchar(30),paymentType4 nvarchar(30))将original表中的数据insert到present中
反映出每一笔交易的商品数量,每一笔交易存在的付款方式,
即在present中产生这样的记录:
1,8,'CASH','MASTERCARD','VISACARD',NULL
2,6,'MASTERCARD','INTERNALCARD','CASH',NULL帮忙呀,因为涉及到大量的数据,所以速度不能太慢,谢谢各位了
create table original (transactionNO int,productCode nvarchar(30),
qty int,paymentType nvarchar(30),TransDate datetime)
innsert into original
select 1,'00258975',2,'CASH','2005-01-02'
union select 1,'00344533',1,'MASTERCARD','2005-01-02'
union select 1,'00258421',4,'VISACARD','2005-01-02'
union select 1,'67452123',1,'CASH','2005-01-02'
union select 2,'00344533',1,'MASTERCARD','2005-01-03'
union select 2,'00258421',4,'INTERNALCARD','2005-01-03'
union select 2,'67452123',1,'CASH','2005-01-03'create table present (transactionNO int,qty int,paymentType1 nvarchar(30),paymentType2 nvarchar(30),paymentType3 nvarchar(30),paymentType4 nvarchar(30))将original表中的数据insert到present中
反映出每一笔交易的商品数量,每一笔交易存在的付款方式,
即在present中产生这样的记录:
1,8,'CASH','MASTERCARD','VISACARD',NULL
2,6,'MASTERCARD','INTERNALCARD','CASH',NULL帮忙呀,因为涉及到大量的数据,所以速度不能太慢,谢谢各位了
create table original (transactionNO int,productCode nvarchar(30),
qty int,paymentType nvarchar(30),TransDate datetime)
insert into original
select 1,'00258975',2,'CASH','2005-01-02'
union select 1,'00344533',1,'MASTERCARD','2005-01-02'
union select 1,'00258421',4,'VISACARD','2005-01-02'
union select 1,'67452123',1,'CASH','2005-01-02'union select 2,'00344533',1,'MASTERCARD','2005-01-03'
union select 2,'00258421',4,'INTERNALCARD','2005-01-03'
union select 2,'67452123',1,'CASH','2005-01-03'create table present (transactionNO int,qty int,
paymentType1 nvarchar(30),paymentType2 nvarchar(30),paymentType3 nvarchar(30),paymentType4 nvarchar(30))select distinct transactionNO, qty=(select sum(qty) from original where transactionNO=A.transactionNO),
paymentType1=(select top 1 paymentType from original where transactionNO=A.transactionNO group by paymentType order by paymentType),
paymentType2=(select top 1 paymentType from original where transactionNO=A.transactionNO and paymentType not in
(select top 1 paymentType from original where transactionNO=A.transactionNO group by paymentType order by paymentType)
group by paymentType order by paymentType),
paymentType3=(select top 1 paymentType from original where transactionNO=A.transactionNO and paymentType not in
(select top 2 paymentType from original where transactionNO=A.transactionNO group by paymentType order by paymentType)
group by paymentType order by paymentType),
paymentType4=(select top 1 paymentType from original where transactionNO=A.transactionNO and paymentType not in
(select top 3 paymentType from original where transactionNO=A.transactionNO group by paymentType order by paymentType)
group by paymentType order by paymentType)
from original as A--result
transactionNO qty paymentType1 paymentType2 paymentType3 paymentType4
------------- ----------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
1 8 CASH MASTERCARD VISACARD NULL
2 6 CASH INTERNALCARD MASTERCARD NULL(2 row(s) affected)
不知道在600W条记录跑起来会是什么样子,
这个paymentType不是索引
select distinct transactionNO, paymentType into #T from originalselect transactionNO,
qty=sum(qty),
paymentType1=(select top 1 paymentType from #T where transactionNO=A.transactionNO),
paymentType2=(select top 1 paymentType from #T where transactionNO=A.transactionNO and paymentType not in(
select top 1 paymentType from #T where transactionNO=A.transactionNO
)),
paymentType3=(select top 1 paymentType from #T where transactionNO=A.transactionNO and paymentType not in(
select top 2 paymentType from #T where transactionNO=A.transactionNO
)),
paymentType4=(select top 1 paymentType from #T where transactionNO=A.transactionNO and paymentType not in(
select top 3 paymentType from #T where transactionNO=A.transactionNO
))
from original as A
group by transactionNO
transactionNo为一般索引(唯一)
paymentType为聚合索引速度不错,可以接受
再次感谢天道兄结贴