if object_id('tb') is not null
drop table tb
go
create table tb(carNo varchar(50),createdate datetime,numb decimal(18,2))
insert into tb select '001','2008-1-1',10
insert into tb select '002','2008-1-4',5.5
insert into tb select '003','2008-1-5',21.11
insert into tb select '001','2008-1-5',72.1
insert into tb select '001','2008-1-14',89.11
insert into tb select '002','2008-1-15',5.11
insert into tb select '008','2008-1-28',44
insert into tb select '001','2008-2-5',89
go
if object_id('p_tb_select') is not null
drop proc p_tb_select
go
create proc p_tb_select
@startdate datetime,
@enddate datetime
as
begin
declare @sql varchar(8000)
while @startdate<@enddate
begin
select @sql=isnull(@sql,'')+'sum(case when convert(varchar(10),createdate,120)='''+convert(varchar(10),@startdate,120)+''' then numb else 0 end) as ['+convert(varchar(10),@startdate,120)+'数量],'
set @startdate=@startdate+1
end
set @sql=left(@sql,len(@sql)-1)
exec('select carNo,'+@sql+' from tb group by carno')
end
go
exec p_tb_select '2008-01-01','2008-02-01'
carNo 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数量
001 10.00 0.00 0.00 0.00 72.10 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 89.11 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
002 0.00 0.00 0.00 5.50 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 5.11 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
003 0.00 0.00 0.00 0.00 21.11 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
008 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 44.00 0.00 0.00 0.00
drop table tb
go
create table tb(carNo varchar(50),createdate datetime,numb decimal(18,2))
insert into tb select '001','2008-1-1',10
insert into tb select '002','2008-1-4',5.5
insert into tb select '003','2008-1-5',21.11
insert into tb select '001','2008-1-5',72.1
insert into tb select '001','2008-1-14',89.11
insert into tb select '002','2008-1-15',5.11
insert into tb select '008','2008-1-28',44
insert into tb select '001','2008-2-5',89
go
if object_id('p_tb_select') is not null
drop proc p_tb_select
go
create proc p_tb_select
@startdate datetime,
@enddate datetime
as
begin
declare @sql varchar(8000)
while @startdate<@enddate
begin
select @sql=isnull(@sql,'')+'sum(case when convert(varchar(10),createdate,120)='''+convert(varchar(10),@startdate,120)+''' then numb else 0 end) as ['+convert(varchar(10),@startdate,120)+'数量],'
set @startdate=@startdate+1
end
set @sql=left(@sql,len(@sql)-1)
exec('select carNo,'+@sql+' from tb group by carno')
end
go
exec p_tb_select '2008-01-01','2008-02-01'
carNo 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数量
001 10.00 0.00 0.00 0.00 72.10 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 89.11 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
002 0.00 0.00 0.00 5.50 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 5.11 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
003 0.00 0.00 0.00 0.00 21.11 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
008 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 44.00 0.00 0.00 0.00
解决方案 »
- 第二个贴..还是存储过程..好吧.我是小白 不要见笑...
- 【求押宝游戏赔率算法】
- 时间显示格式问题
- 如将一查询结果导出到一个excel里
- [求助]统计多条信息之和!
- 【【【【【【 请教zjcxc(邹建) ( )和zhangzs8896(小二) 等高手~~~~】】】】】】
- msserver2000中的表systypes中的字段xtype和type有什么区别?
- SQL合并问题
- 单表取最新不重复用户,并合并此表中用户的所有记录中的一个字段(字符串)值
- 求下面这组数据分组的解决办法
- 一个 delphi通过webservice 用java调用存储过程的问题
- 在导入mssql2005数据时,出现多步ole错误,盼望高手赐教.
if object_id('tb') is not null
drop table tb
go
create table tb(carNo varchar(50),createdate datetime,numb decimal(18,2))
insert into tb select '001','2008-1-1',10
insert into tb select '002','2008-1-4',5.5
insert into tb select '003','2008-1-5',21.11
insert into tb select '001','2008-1-5',72.1
insert into tb select '001','2008-1-14',89.11
insert into tb select '002','2008-1-15',5.11
insert into tb select '008','2008-1-28',44
insert into tb select '001','2008-2-5',89
go
if object_id('p_tb_select') is not null
drop proc p_tb_select
go
create proc p_tb_select
@startdate datetime,
@enddate datetime
as
begin
declare @sql varchar(8000)
while @startdate<@enddate
begin
select @sql=isnull(@sql,'')+'sum(case when convert(varchar(10),createdate,120)='''+convert(varchar(10),@startdate,120)+''' then numb else 0 end) as ['+case when datediff(dd,@startdate,@enddate)=1 then datename(mm,@startdate)+'月月底数量' else datename(mm,@startdate)+'月'+datename(dd,@startdate) +'号数量' end +'],'
set @startdate=@startdate+1
end
set @sql=left(@sql,len(@sql)-1)
exec('select carNo,'+@sql+' from tb group by carno')
end
go
exec p_tb_select '2008-01-01','2008-02-01'
请问有什么好的方法吗? 谢谢了!!!!!
@startdate datetime,
@enddate datetime
as
begin
declare @sql varchar(8000)
while @startdate<@enddate
begin
select @sql=isnull(@sql,'')+'sum(case when convert(varchar(10),createdate,120)='''+convert(varchar(10),@startdate,120)+''' then numb else 0 end) as ['+case when datediff(dd,@startdate,@enddate)=1 then datename(mm,@startdate)+'月月底数量' else datename(mm,@startdate)+'月'+datename(dd,@startdate) +'号数量' end +'],'
set @startdate=@startdate+1
end
set @sql=left(@sql,len(@sql)-1)
exec('select carNo,
sum(case when datepart(dd,createdate) between 1 and 10 then numb else 0 end) as [上旬],
sum(case when datepart(dd,createdate) between 11 and 20 then numb else 0 end) as [中旬],
sum(case when datepart(dd,createdate) between 21 and 31 then numb else 0 end) as [下旬],'+@sql+' from tb group by carno')
end
go
exec p_tb_select '2008-01-01','2008-02-01'