SQL´æ´¢¹ý³Ì

CREATE PROCEDURE test1
@jyrqi datetime --ÊäÈëÈÕÆÚ×÷Ϊ´æ´¢¹ý³ÌµÄ²ÎÊý


as

create table table1
(
idno varchar(15),
dept varchar(6),
lbmc varchar(10),
jyrqi datetime,
jyl decimal(9,2)
)
insert into table1(idno,dept,lbmc,jyrqi,jyl) select idno,dept,lbmc,jyrqi,jyl from oil_record 
declare @sql varchar(5000)

set @sql='select idno,'
declare @i int
declare @j datetime
set @i=1
set @j=exec (web_getlastdate())--È¡µÃÊäÈë²ÎÊý×îºóÒ»ÌìÊǼ¸ºÅ
while(@i<=@j)
begin
declare @lyr varchar(10)
set @lyr=convert(varchar(10),datepart(year,@jyrqi))+'-'+convert(varchar(10),datepart(month,@jyrqi))+'-'
set @sql=@sql+'sum(case when jyrqi='''+@lyr+convert(varchar(5),@i)+''' then jyl else 0 end) as n'+convert(varchar(5),@i)+','
set @i=@i+1
end
set @sql=@sql+'sum(jyl) from table1 group by idno'
print @sql
exec(@sql)
drop table table1

go

ÉÏÃæÊÇ´æ´¢¹ý³ÌÖеĴúÂ룬´úÂëÖÐÁÙʱ½¨Á˸ö±í£¬È»ºóͨ¹ýSELECTÓï¾ä°ÑÊý¾ÝÏÔʾ³öÀ´¡£
ÎÊÌâÊÇÏÔʾ³öÀ´µÄÊý¾ÝÊǸù¾ÝÔ·ݵÄÌìÊý£¨28£¬29£¬30£¬31Ì죩À´ÏÔʾµÄ¡£±ÈÈçÊäÈëµÄÊÇ2ÔµĻ°£¬ÏÔʾ³öÀ´µÄÊý¾ÝÖ»ÓÐ28»ò29ÁÐÊý¾Ý£¬ÏÖÔÚÏëÔÚÏÔʾÊý¾ÝÖмÓÈë30ºÍ31ÌìµÄÊý¾Ý£¨ÖµÎª0£©Ôõô¼Ó£¿Ð»Ð»¡£

---

set @sql=@sql+'isnull(sum(jyl),0) from table1 group by idno'

---

½¨Á¢ÁÙʱ±íµÄʱºò¡£¡£ÏÈÅжÏÕâ¸ö±íÊÇ·ñ´æÔÚ¡£¡£¡£·ñÕß³ö´í£¡

---

IF NOT OBJECT_ID('[tb]') IS NULL
  DROP TABLE [tb]

---