34,838
社区成员




con1 con2 con3 ...
A 11 111 ...
B 22 222 ...
declare @t table (con1 varchar(10),con2 int)
insert into @t select 'a', 11
insert into @t select 'b', 22
insert into @t select 'c', 33
insert into @t select 'a', 111
insert into @t select 'b', 222
insert into @t select 'c', 333
select *,id=identity(int,1,1) into #3tt from @t order by Con1
select t.*,tt.con2 from #3tt t inner join (select * from #3tt where id%2=0) tt on t.con1=tt.con1 where t.id%2=1
drop table #3tt
/*
a 11 1 111
b 22 3 222
c 333 5 33
*/
create table os(con1 varchar(10),con2 int)
insert into os select 'A',11
insert into os select 'B',22
insert into os select 'C',33
insert into os select 'A',111
insert into os select 'B',222
insert into os select 'C',333
select id=identity(int,1,1),* into # from os
DECLARE @SQL VARCHAR(8000),@i int
SET @SQL='SELECT con1'
set @i=1
SELECT @SQL=@SQL+',MAX(CASE WHEN PX='+rtrim(px)+' THEN con2 ELSE 0 END ) con'+ltrim(@i),@i=@i+1
FROM(select distinct px=(select count(1) from # where con1=a.con1 and id<=a.id) from # a)b
SET @SQL=@SQL+' FROM (select px=(select count(1) from # where con1=a.con1 and id<=a.id),* from # a)b GROUP BY con1'
EXEC (@SQL)
产品编号 产品名称 销售时间 数量
1501 A 2007-11-10 50
1501 A 2007-11-12 20
1502 B 2007-11-11 30
1502 B 2007-11-12 20
1502 B 2007-11-13 40
1503 C 2007-11-10 40
1524 C 2007-11-12 30
怎么用SQL语句得到下面的结果:
产品编号 产品名称 销售时间 数量 销售时间 数量 销售时间 数量
1501 A 2007-11-10 50 2007-11-12 20
1501 B 2007-11-11 30 2007-11-12 20 2007-11-13 40
1502 C 2007-11-10 40 2007-11-12 30
create table tb(产品编号 varchar(10) ,产品名称 varchar(10),销售时间 datetime,数量 int)
insert tb select '1501' , 'A' , '2007-11-10' , 50
insert tb select '1501' , 'A' , '2007-11-12' , 20
insert tb select '1502' , 'B' , '2007-11-11' , 30
insert tb select '1502' , 'B' , '2007-11-12' , 20
insert tb select '1502' , 'B' , '2007-11-13' , 40
insert tb select '1503' , 'C' , '2007-11-10' , 40
insert tb select '1503' , 'C' , '2007-11-12' , 30
DECLARE @SQL VARCHAR(8000)
SET @SQL='SELECT 产品编号,产品名称'
SELECT @SQL=@SQL+',MAX(CASE WHEN PX='+rtrim(px)+' THEN CONVERT(CHAR(10),销售时间,120) ELSE '''' END ) [销售时间],
SUM(CASE WHEN PX='+rtrim(px)+' THEN 数量 ELSE 0 END ) [数量]'
FROM(SELECT DISTINCT PX=(SELECT COUNT(*) FROM tb where a.产品编号=产品编号 and 产品名称=a.产品名称 and 销售时间<a.销售时间)+1 FROM tb a) T
SET @SQL=@SQL+' FROM (SELECT *,PX=(SELECT COUNT(*) FROM tb where a.产品编号=产品编号 and 产品名称=a.产品名称 and 销售时间<a.销售时间)+1 FROM tb a) T GROUP BY 产品编号,产品名称'
EXEC (@SQL)
DROP TABLE tb
/*
产品编号 产品名称 销售时间 数量 销售时间 数量 销售时间 数量
---------- ---------- ---------- ----------- ---------- ----------- ---------- -----------
1501 A 2007-11-10 50 2007-11-12 20 0
1502 B 2007-11-11 30 2007-11-12 20 2007-11-13 40
1503 C 2007-11-10 40 2007-11-12 30 0
(3 行受影响)
*/