产品编号 产品名称 销售时间 数量
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' , 30DECLARE @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) TSET @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 行受影响)
*/
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' , 30DECLARE @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) TSET @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 行受影响)
*/
insert into tb values(1992, 200)
insert into tb values(1993, 300)
insert into tb values(1994, 270)
godeclare @output varchar(8000)
select @output = coalesce(@output + ',' , '') + cast([year] as varchar) + ',' + cast(num as varchar) from tb
print @outputdrop table tb/*
1992,200,1993,300,1994,270
*/
insert into tb values(1992, 200)
insert into tb values(1993, 300)
insert into tb values(1994, 270)
godeclare @output varchar(8000)
select @output = coalesce(@output + ' ' , '') + cast([year] as varchar) + ' ' + cast(num as varchar) from tb
print @outputdrop table tb/*
1992 200 1993 300 1994 270
*/
person ttime Iotype
001 8:00 1
001 12:00 2
001 13:00 1
001 17:00 2
002 8:00 1
002 12:00 2 转换成表test2
Wpid person in Out
1 001 8:00 12:00
2 001 13:00 17:00
3 002 8:00 12:00
最好使用一句SQL语句搞顶(不要使用变量或函数,游标之类),,,不胜感激--------------------------------------------------------------------
--我将数据略做修改,8:00改为08:00
--使用sql server 2005的row_number函数可以直接得到前面的序,sql server 2000要麻烦一些.
create table tb (person varchar(10), ttime varchar(10),Iotype int)
insert into tb
select '001' ,'08:00',1 union all
select '001' ,'12:00',2 union all
select '001' ,'13:00',1 union all
select '001' ,'17:00',2 union all
select '002' ,'08:00',1 union all
select '002' ,'12:00',2 select Wpid = row_number() over(order by m.person , m.ttime) , m.person , m.ttime [in], n.ttime [out] from
(select px = (select count(*) from tb where person = a.person and ttime < a.ttime) + 1 , * from tb a) m,
(select px = (select count(*) from tb where person = a.person and ttime < a.ttime) + 1 , * from tb a) n
where m.person = n.person and m.px = n.px - 1 and m.px%2 = 1drop table tb/*
Wpid person in out
-------------------- ---------- ---------- ----------
1 001 08:00 12:00
2 001 13:00 17:00
3 002 08:00 12:00(3 行受影响)
*/-------------------------------------------------------------------------------------------sql server 2000中的写法.
create table tb (person varchar(10), ttime varchar(10),Iotype int)
insert into tb
select '001' ,'08:00',1 union all
select '001' ,'12:00',2 union all
select '001' ,'13:00',1 union all
select '001' ,'17:00',2 union all
select '002' ,'08:00',1 union all
select '002' ,'12:00',2 SELECT Wpid=(SELECT COUNT(1) FROM
(
select m.person , m.ttime [in], n.ttime [out] from
(select px = (select count(*) from tb where person = a.person and ttime < a.ttime) + 1 , * from tb a) m,
(select px = (select count(*) from tb where person = a.person and ttime < a.ttime) + 1 , * from tb a) n
where m.person = n.person and m.px = n.px - 1 and m.px%2 = 1
) t1 WHERE (person < t2.person) or (person = t2.person and [in] < t2.[in])) + 1 , * from
(
select m.person , m.ttime [in], n.ttime [out] from
(select px = (select count(*) from tb where person = a.person and ttime < a.ttime) + 1 , * from tb a) m,
(select px = (select count(*) from tb where person = a.person and ttime < a.ttime) + 1 , * from tb a) n
where m.person = n.person and m.px = n.px - 1 and m.px%2 = 1
) t2
drop table tb/*
Wpid person in out
-------------------- ---------- ---------- ----------
1 001 08:00 12:00
2 001 13:00 17:00
3 002 08:00 12:00(3 行受影响)
*/
create table tb([year] int,NUM int)
insert into tb values(1992, 200)
insert into tb values(1993, 300)
insert into tb values(1994, 270)
insert into tb values(1995, 200)
insert into tb values(1996, 300)
insert into tb values(1997, 270)
goselect
max(case (px-1)%3 when 0 then [year] else 0 end) year1,
max(case (px-1)%3 when 0 then [num] else 0 end) num1,
max(case (px-1)%3 when 1 then [year] else 0 end) year2,
max(case (px-1)%3 when 1 then [num] else 0 end) num2,
max(case (px-1)%3 when 2 then [year] else 0 end) year3,
max(case (px-1)%3 when 2 then [num] else 0 end) num3
from
(
select * , px = (select count(1) from tb where [year] < t.[year]) + 1 from tb t
) m
group by (px-1)/3drop table tb/*
year1 num1 year2 num2 year3 num3
----------- ----------- ----------- ----------- ----------- -----------
1992 200 1993 300 1994 270
1995 200 1996 300 1997 270(所影响的行数为 2 行)
*/
insert into tb values(1992, 200)
insert into tb values(1993, 300)
insert into tb values(1994, 270)
insert into tb values(1995, 200)
insert into tb values(1996, 300)
insert into tb values(1997, 270)
go--如果能按year区分每行的大小,可如下操作.
select
max(case (px-1)%3 when 0 then [year] else 0 end) year1,
max(case (px-1)%3 when 0 then [num] else 0 end) num1,
max(case (px-1)%3 when 1 then [year] else 0 end) year2,
max(case (px-1)%3 when 1 then [num] else 0 end) num2,
max(case (px-1)%3 when 2 then [year] else 0 end) year3,
max(case (px-1)%3 when 2 then [num] else 0 end) num3
from
(
select * , px = (select count(1) from tb where [year] < t.[year]) + 1 from tb t
) m
group by (px-1)/3--如果不能区分大小,则需要使用临时表
select * , px = identity(int,1,1) into tmp from tb
select
max(case (px-1)%3 when 0 then [year] else 0 end) year1,
max(case (px-1)%3 when 0 then [num] else 0 end) num1,
max(case (px-1)%3 when 1 then [year] else 0 end) year2,
max(case (px-1)%3 when 1 then [num] else 0 end) num2,
max(case (px-1)%3 when 2 then [year] else 0 end) year3,
max(case (px-1)%3 when 2 then [num] else 0 end) num3
from tmp
group by (px-1)/3
drop table tb,tmp/*
year1 num1 year2 num2 year3 num3
----------- ----------- ----------- ----------- ----------- -----------
1992 200 1993 300 1994 270
1995 200 1996 300 1997 270(所影响的行数为 2 行)
*/
insert into tb
select '1992',200 union all
select '1993', 300 union all
select '1994', 270 union all
select '1995', 8000 union all
select '1996', 900select year1=t1.year,num1=t1.num,
year2=t2.year,num2=t2.num,
year3=t3.year,num3=t3.num
from (select year,num,id=(select count(1) from tb where year<=a.year) from tb a) t1
left join (select year,num,id=(select count(1) from tb where year<=a.year) from tb a) t2 on t2.id=t1.id+1
left join (select year,num,id=(select count(1) from tb where year<=a.year) from tb a) t3 on t3.id=t1.id+2
where t1.year%3=0
/*
year1 num1 year2 num2 year3 num3
-----------------------------------------------
1992 200 1993 300 1994 270
1995 8000 1996 900 NULL NULL
*/drop table tb