您的位置：程序门 -> ms-sql server -> 基础类

求sql语句，进来的帮忙顶一下，谢谢。

[收藏此页] [打印本页]选择字色：背景色：字体：[大][中][小]

求sql语句，进来的帮忙顶一下，谢谢。[已结贴,结贴人:blackmeit]

发表于：2008-01-22 17:15:06 楼主

取上个月价格信息，即12月份每天的价格信息

样本数据：
declare @a table（productid int,price decimal（8,2),pricedate datetime)
insert @a select 1,1.20,'2007-12-01' union all
select 1,1.50,'2007-12-02' union all
select 2,1.50,'2007-12-01' union all
select 2,1.80,'2007-12-02'

declare @b table（productid int,productname varchar（20))
insert @b select 1,'product1' union all
select 2,'product2'

productid price pricedate
----------- --------------------------------------- -----------------------
1 1.20 2007-12-01 00:00:00.000
1 1.50 2007-12-02 00:00:00.000
2 1.50 2007-12-01 00:00:00.000
2 1.80 2007-12-02 00:00:00.000

productid productname
----------- --------------------
1 product1
2 product2

期望结果（）
productid productname 2007-12-01 2007-12-02 2007-12-03 … 2007-12-31
1 product1 1.20 1.50 1.50 1.50
2 product2 1.50 1.80 1.80 1.80

若当天没有数据，就使用前一天的数据，月份列最好能动态生成。

发表于：2008-01-22 17:17:021楼得分:2

又是行列转换的

发表于：2008-01-22 17:17:142楼得分:2

若当天没有数据，就使用前一天的数据

这个够麻烦.

发表于：2008-01-22 17:18:403楼得分:2

先把行列转换的代码贴出来吧

发表于：2008-01-22 17:18:484楼得分:2

帮顶一下，

发表于：2008-01-22 17:20:095楼得分:2

老乌龟去贴了。

发表于：2008-01-22 17:21:216楼得分:10

那我帖了?

sql code

/*
普通行列转换
（爱新觉罗.毓华 2007-11-18于海南三亚)

假设有张学生成绩表（tb)如下:
name subject result
张三 语文　　74
张三 数学　　83
张三 物理　　93
李四 语文　　74
李四 数学　　84
李四 物理　　94
*/

-------------------------------------------------------------------------
/*
想变成 
姓名         语文        数学        物理          
---------- ----------- ----------- ----------- 
李四         74          84          94
张三         74          83          93
*/

create table tb
（
   name    varchar（10) ,
   subject varchar（10) ,
   result  int
)

insert into tb（name , subject , result) values（'张三' , '语文' , 74)
insert into tb（name , subject , result) values（'张三' , '数学' , 83)
insert into tb（name , subject , result) values（'张三' , '物理' , 93)
insert into tb（name , subject , result) values（'李四' , '语文' , 74)
insert into tb（name , subject , result) values（'李四' , '数学' , 84)
insert into tb（name , subject , result) values（'李四' , '物理' , 94)
go

--静态sql,指subject只有语文、数学、物理这三门课程。
select name 姓名,
  max（case subject when '语文' then result else 0 end) 语文,
  max（case subject when '数学' then result else 0 end) 数学,
  max（case subject when '物理' then result else 0 end) 物理
from tb
group by name
/*
姓名         语文        数学        物理          
---------- ----------- ----------- ----------- 
李四         74          84          94
张三         74          83          93
*/

--动态sql,指subject不止语文、数学、物理这三门课程。
declare @sql varchar（8000)
set @sql = 'select name as ' + '姓名'
select @sql = @sql + ' , max（case subject when ''' + subject + ''' then result else 0 end) [' + subject + ']'
from （select distinct subject from tb) as a
set @sql = @sql + ' from tb group by name'
ＥＸＥＣ（@sql) 
/*
姓名         数学        物理        语文          
---------- ----------- ----------- ----------- 
李四         84          94          74
张三         83          93          74
*/

-------------------------------------------------------------------
/*加个平均分，总分
姓名         语文        数学        物理        平均分                总分          
---------- ----------- ----------- ----------- -------------------- ----------- 
李四         74          84          94          84.00                252
张三         74          83          93          83.33                250
*/

--静态sql,指subject只有语文、数学、物理这三门课程。
select name 姓名,
  max（case subject when '语文' then result else 0 end) 语文,
  max（case subject when '数学' then result else 0 end) 数学,
  max（case subject when '物理' then result else 0 end) 物理,
  cast（avg（result*1.0) as decimal（18,2)) 平均分,
  sum（result) 总分
from tb
group by name
/*
姓名         语文        数学        物理        平均分                总分          
---------- ----------- ----------- ----------- -------------------- ----------- 
李四         74          84          94          84.00                252
张三         74          83          93          83.33                250
*/

--动态sql,指subject不止语文、数学、物理这三门课程。
declare @sql1 varchar（8000)
set @sql1 = 'select name as ' + '姓名'
select @sql1 = @sql1 + ' , max（case subject when ''' + subject + ''' then result else 0 end) [' + subject + ']'
from （select distinct subject from tb) as a
set @sql1 = @sql1 + ' , cast（avg（result*1.0) as decimal（18,2)) 平均分,sum（result) 总分 from tb group by name'
ＥＸＥＣ（@sql1) 
/*
姓名         数学        物理        语文        平均分                总分          
---------- ----------- ----------- ----------- -------------------- ----------- 
李四         84          94          74          84.00                252
张三         83          93          74          83.33                250
*/

drop table tb    

---------------------------------------------------------
---------------------------------------------------------
/*
如果上述两表互相换一下：即

姓名 语文 数学 物理
张三 74　　83　　93
李四 74　　84　　94

想变成 
name       subject result      
---------- ------- ----------- 
李四         语文      74
李四         数学      84
李四         物理      94
张三         语文      74
张三         数学      83
张三         物理      93
*/

create table tb1
（
   姓名 varchar（10) ,
   语文 int ,
   数学 int ,
   物理 int
)

insert into tb1（姓名 , 语文 , 数学 , 物理) values（'张三',74,83,93)
insert into tb1（姓名 , 语文 , 数学 , 物理) values（'李四',74,84,94)

select * from
（
  select 姓名 as name , subject = '语文' , result = 语文 from tb1 
  union all
  select 姓名 as name , subject = '数学' , result = 数学 from tb1
  union all
  select 姓名 as name , subject = '物理' , result = 物理 from tb1
) t
order by name , case subject when '语文' then 1 when '数学' then 2 when '物理' then 3 when '总分' then 4 end

--------------------------------------------------------------------
/*加个平均分,总分
name       subject     result               
---------- -------    -------------------- 
李四         语文      74.00
李四         数学      84.00
李四         物理      94.00
李四         平均分    84.00
李四         总分      252.00
张三         语文      74.00
张三         数学      83.00
张三         物理      93.00
张三         平均分    83.33
张三         总分      250.00
*/

select * from
（
  select 姓名 as name , subject = '语文' , result = 语文 from tb1 
  union all
  select 姓名 as name , subject = '数学' , result = 数学 from tb1
  union all
  select 姓名 as name , subject = '物理' , result = 物理 from tb1
  union all
  select 姓名 as name , subject = '平均分' , result = cast（（语文 + 数学 + 物理)*1.0/3 as decimal（18,2)) from tb1
  union all
  select 姓名 as name , subject = '总分' , result = 语文 + 数学 + 物理 from tb1
) t
order by name , case subject when '语文' then 1 when '数学' then 2 when '物理' then 3 when '平均分' then 4 when '总分' then 5 end

drop table tb1

发表于：2008-01-22 17:21:477楼得分:2

乌龟的强项，

发表于：2008-01-22 17:30:298楼得分:10

sql code


--结果没有转向的
declare   @a   table（productid   int,price   decimal（8,2),pricedate   datetime) 
insert   @a   select   1,1.20,'2007-12-01'   union   all 
select   1,1.50,'2007-12-02'   union   all 
select   2,1.50,'2007-12-01'   union   all 
select   2,1.80,'2007-12-02'     
  

declare   @b   table（productid   int,productname   varchar（20)) 
insert   @b   select   1,'product1'   union   all   
select   2,'product2'     


declare @d table （
days datetime
)

insert @d
select dateadd（day,i,convert（varchar（7),getdate（),120)+'-01')
from （select 0 as i union all  select 1 union all select 2 union all select 3
union all select 4 union all  select 5 union all select 6 union all select 7
union all select 8 union all  select 9 union all select 10 union all select 11
union all select 12 union all  select 13 union all select 14 union all select 15
union all select 16 union all  select 17 union all select 18 union all select 19
union all select 20 union all  select 21 union all select 22 union all select 23
union all select 24 union all  select 25 union all select 26 union all select 27
union all select 28 union all  select 29 union all select 30 union all select 31
) as t
where dateadd（day,i,convert（varchar（7),getdate（),120)+'-01')<dateadd（month,1,convert（varchar（7),getdate（),120)+'-01')

select b.*,d.*,a.price
from @b b cross join @d d
left join @a a
on a.productid=b.productid 
and a.pricedate=（select max（pricedate) from @a where productid=b.productid and pricedate<=d.days)

发表于：2008-01-22 17:37:559楼得分:10

sql code


--表变量不好玩，改成临时表
create table #a（productid   int,price   decimal（8,2),pricedate   datetime) 

insert   #a   select   1,1.20,'2007-12-01'   union   all 
select   1,1.50,'2007-12-02'   union   all 
select   2,1.50,'2007-12-01'   union   all 
select   2,1.80,'2007-12-02'     
  

create table  #b（productid   int,productname   varchar（20)) 
insert   #b   select   1,'product1'   union   all   
select   2,'product2'     


create table #d （
days datetime
)

insert #d
select dateadd（day,i,convert（varchar（7),getdate（),120)+'-01')
from （select 0 as i union all  select 1 union all select 2 union all select 3
union all select 4 union all  select 5 union all select 6 union all select 7
union all select 8 union all  select 9 union all select 10 union all select 11
union all select 12 union all  select 13 union all select 14 union all select 15
union all select 16 union all  select 17 union all select 18 union all select 19
union all select 20 union all  select 21 union all select 22 union all select 23
union all select 24 union all  select 25 union all select 26 union all select 27
union all select 28 union all  select 29 union all select 30 union all select 31
) as t
where dateadd（day,i,convert（varchar（7),getdate（),120)+'-01')<dateadd（month,1,convert（varchar（7),getdate（),120)+'-01')

declare @sql varchar（8000)
set @sql=''
select @sql=@sql+',max（case d.days when '''+convert（varchar（10),days,120)+''' then a.price end) as ['+convert（varchar（10),days,120)+']'
from #d
order by days

ＥＸＥＣ（'
select b.*'+@sql+'
from #b b cross join #d d
left join #a a
on a.productid=b.productid 
and a.pricedate=（select max（pricedate) from #a where productid=b.productid and pricedate<=d.days)
group by b.productid,b.productname
')

drop table #a,#b,#d

--结果
productid   productname          2008-01-01 2008-01-02 2008-01-03 2008-01-04 2008-01-05 2008-01-06 2008-01-07 2008-01-08 2008-01-09 2008-01-10 2008-01-11 2008-01-12 2008-01-13 2008-01-14 2008-01-15 2008-01-16 2008-01-17 2008-01-18 2008-01-19 2008-01-20 2008-01-21 2008-01-22 2008-01-23 2008-01-24 2008-01-25 2008-01-26 2008-01-27 2008-01-28 2008-01-29 2008-01-30 2008-01-31 
----------- -------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- 
1           product1             1.50       1.50       1.50       1.50       1.50       1.50       1.50       1.50       1.50       1.50       1.50       1.50       1.50       1.50       1.50       1.50       1.50       1.50       1.50       1.50       1.50       1.50       1.50       1.50       1.50       1.50       1.50       1.50       1.50       1.50       1.50
2           product2             1.80       1.80       1.80       1.80       1.80       1.80       1.80       1.80       1.80       1.80       1.80       1.80       1.80       1.80       1.80       1.80       1.80       1.80       1.80       1.80       1.80       1.80       1.80       1.80       1.80       1.80       1.80       1.80       1.80       1.80       1.80

警告: 聚合或其它 set 操作消除了空值。

发表于：2008-01-22 17:40:5210楼得分:2

强强强

发表于：2008-01-22 17:41:1811楼得分:2

顶顶顶

发表于：2008-01-22 17:43:3112楼得分:10

sql code

--上面的错了，重新贴
--表变量不好玩，改成临时表
create table #a（productid   int,price   decimal（8,2),pricedate   datetime) 

insert   #a   select   1,1.20,'2007-12-01'   union   all 
select   1,1.50,'2007-12-02'   union   all 
select   2,1.50,'2007-12-01'   union   all 
select   2,1.80,'2007-12-02'     
  

create table  #b（productid   int,productname   varchar（20)) 
insert   #b   select   1,'product1'   union   all   
select   2,'product2'     


create table #d （
days datetime
)

insert #d
select dateadd（day,i,convert（varchar（7),getdate（),120)+'-01')
from （select 0 as i union all  select 1 union all select 2 union all select 3
union all select 4 union all  select 5 union all select 6 union all select 7
union all select 8 union all  select 9 union all select 10 union all select 11
union all select 12 union all  select 13 union all select 14 union all select 15
union all select 16 union all  select 17 union all select 18 union