结构
1 ID 2 date 3 state
数据
1 2010-1-1 1
1 2010-1-2 0
1 2010-1-3 1
2 2010-1-1 0
2 2010-1-2 0
2 2010-1-3 1 需要列出
ID 2010-1-1 2010-1-2 2010-1-3 汇总
1 1 0 1 2
2 0 0 1 1 存储过程
ALTER proc [dbo].[XF_HUIZONG]
@sdate as datetime,
@edate as datetime
as
if exists (select * from sysobjects where id = object_id(N'[dbo].[HuiZong]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[HuiZong]--如果date不确定,用动态SQL。
declare @sql varchar(8000)
set @sql = 'select person_id,card_no,person_no,person_name,type_no,dept_id '
select @sql = @sql + ' , max(case date when ''' + date + ''' then eat else 0 end) [' + date + ']'
from (select convert(varchar(10),dateadd(dd,num,@sdate),120) date from (select isnull((select count(1) from sysobjects where id<t.id),0) as num from sysobjects t) a where dateadd(dd,num,@sdate)<=@edate) as a
set @sql = @sql + ' , sum(eat) 匯總 into HuiZong from V_XFDaly group by person_id,card_no,person_no,person_name,type_no,dept_id'
exec(@sql) 但有个问题
就是我 @sdate = 2010-01-01 @edate = 2010-01-02
结果出现
ID 2010-1-1 2010-1-2 汇总
1 1 0 2
2 0 0 1 想问下怎么改才能改汇总的数值
1 ID 2 date 3 state
数据
1 2010-1-1 1
1 2010-1-2 0
1 2010-1-3 1
2 2010-1-1 0
2 2010-1-2 0
2 2010-1-3 1 需要列出
ID 2010-1-1 2010-1-2 2010-1-3 汇总
1 1 0 1 2
2 0 0 1 1 存储过程
ALTER proc [dbo].[XF_HUIZONG]
@sdate as datetime,
@edate as datetime
as
if exists (select * from sysobjects where id = object_id(N'[dbo].[HuiZong]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[HuiZong]--如果date不确定,用动态SQL。
declare @sql varchar(8000)
set @sql = 'select person_id,card_no,person_no,person_name,type_no,dept_id '
select @sql = @sql + ' , max(case date when ''' + date + ''' then eat else 0 end) [' + date + ']'
from (select convert(varchar(10),dateadd(dd,num,@sdate),120) date from (select isnull((select count(1) from sysobjects where id<t.id),0) as num from sysobjects t) a where dateadd(dd,num,@sdate)<=@edate) as a
set @sql = @sql + ' , sum(eat) 匯總 into HuiZong from V_XFDaly group by person_id,card_no,person_no,person_name,type_no,dept_id'
exec(@sql) 但有个问题
就是我 @sdate = 2010-01-01 @edate = 2010-01-02
结果出现
ID 2010-1-1 2010-1-2 汇总
1 1 0 2
2 0 0 1 想问下怎么改才能改汇总的数值
exec(@sql)
在WHERE 后加条件
declare @sql varchar(8000)
set @sql = 'select person_id,card_no,person_no,person_name,type_no,dept_id '
select @sql = @sql + ' , max(case date when ''' + date + ''' then eat else 0 end) [' + date + ']'
from (select convert(varchar(10),dateadd(dd,num,@sdate),120) date from (select isnull((select count(1) from sysobjects where id<t.id),0) as num from sysobjects t) a where dateadd(dd,num,@sdate)<=@edate) as a
set @sql = @sql + ' , sum(eat) 匯總 into HuiZong from V_XFDaly where date between ''' + @sdate + ''' and ''' + @edate + '''group by person_id,card_no,person_no,person_name,type_no,dept_id'
exec(@sql)
好像date没转
能否写下 谢谢
create table tb(ID int, date datetime, state int)
insert into tb values(1 , '2010-1-1' , 1 )
insert into tb values(1 , '2010-1-2' , 0 )
insert into tb values(1 , '2010-1-3' , 1 )
insert into tb values(2 , '2010-1-1' , 0 )
insert into tb values(2 , '2010-1-2' , 0 )
insert into tb values(2 , '2010-1-3' , 1 )
godeclare @sdate datetime
declare @edate datetime
set @sdate = '2010-01-01'
set @edate = '2010-01-02'declare @sql varchar(8000)
set @sql = 'select id '
select @sql = @sql + ' , max(case date when ''' + date + ''' then state else 0 end) [' + date + ']'
from (select convert(varchar(10),dateadd(dd,num,@sdate),120) date from (select isnull((select count(1) from sysobjects where id<t.id),0) as num from sysobjects t) a where dateadd(dd,num,@sdate)<=@edate) as a
set @sql = @sql + ',sum(state) 汇总 from tb where date between ''' + convert(varchar(10),@sdate,120) + ''' and ''' + convert(varchar(10),@edate,120) + ''' group by id'
exec(@sql) drop table tb/*
id 2010-01-01 2010-01-02 汇总
----------- ----------- ----------- -----------
1 1 0 1
2 0 0 0*/--这是用存储过程实现的.
create table tb(ID int, date datetime, state int)
insert into tb values(1 , '2010-1-1' , 1 )
insert into tb values(1 , '2010-1-2' , 0 )
insert into tb values(1 , '2010-1-3' , 1 )
insert into tb values(2 , '2010-1-1' , 0 )
insert into tb values(2 , '2010-1-2' , 0 )
insert into tb values(2 , '2010-1-3' , 1 )
gocreate proc my_proc @sdate datetime,@edate datetime
as
begin
declare @sql varchar(8000)
set @sql = 'select id '
select @sql = @sql + ' , max(case date when ''' + date + ''' then state else 0 end) [' + date + ']'
from (select convert(varchar(10),dateadd(dd,num,@sdate),120) date from (select isnull((select count(1) from sysobjects where id<t.id),0) as num from sysobjects t) a where dateadd(dd,num,@sdate)<=@edate) as a
set @sql = @sql + ',sum(state) 汇总 from tb where date between ''' + convert(varchar(10),@sdate,120) + ''' and ''' + convert(varchar(10),@edate,120) + ''' group by id'
exec(@sql)
end
goexec my_proc '2010-01-01' , '2010-01-02'
drop table tb
drop proc my_proc/*
id 2010-01-01 2010-01-02 汇总
----------- ----------- ----------- -----------
1 1 0 1
2 0 0 0*/
create table tb(ID int, date datetime, state int)
insert into tb values(1 , '2010-1-1' , 1 )
insert into tb values(1 , '2010-1-2' , 0 )
insert into tb values(1 , '2010-1-3' , 1 )
insert into tb values(2 , '2010-1-1' , 0 )
insert into tb values(2 , '2010-1-2' , 0 )
insert into tb values(2 , '2010-1-3' , 1 )
godeclare @sdate datetime
declare @edate datetime
set @sdate = '2010-01-01'
set @edate = '2010-01-02'declare @sql varchar(8000)
set @sql = 'select id '
select @sql = @sql + ' , max(case date when ''' + date + ''' then state else 0 end) [' + date + ']'
from (select convert(varchar(10),date,120) as date from (select distinct date from tb where
date between '' + convert(varchar(10),@sdate,120) +'' and '' + convert(varchar(10),@edate,120) + '') a) a
set @sql=@sql+',sum(state) ss from tb
where date between ''' + convert(varchar(10),@sdate,120) +''' and ''' + convert(varchar(10),@edate,120) + ''' group by id'
exec(@sql)/*
id 2010-01-01 2010-01-02 汇总
----------- ----------- ----------- -----------
1 1 0 1
2 0 0 0*/