有一表A
id Bdate name Stopdate
1 2007-01-01 CCC 2007-01-03
1 2007-01-01 DDD 2007-01-02
1 2007-01-01 VV 2007-01-04
1 2007-02-01 TTTTT 2007-02-03
2 2007-01-01 GG 2007-01-04
3 2007-01-02 CCC 2007-01-03
3 2007-01-01 CCC 2007-01-02
4 2007-01-04 YY 2007-01-05 输入1月要得到
日期 CCC DDD VV GG YY
1号 2 1 1 1 0
2号 2 1 1 1 0
3号 1 0 1 1 0
4号 0 0 1 1 1
5号 0 0 0 0 1
. 0 0 0 0 0
. 0 0 0 0 0
31号 0 0 0 0 0
合计 5 2 4 4 2就是根据Bdate(开始日期) 和 Stopdate(结束日期) 求name的次数
id Bdate name Stopdate
1 2007-01-01 CCC 2007-01-03
1 2007-01-01 DDD 2007-01-02
1 2007-01-01 VV 2007-01-04
1 2007-02-01 TTTTT 2007-02-03
2 2007-01-01 GG 2007-01-04
3 2007-01-02 CCC 2007-01-03
3 2007-01-01 CCC 2007-01-02
4 2007-01-04 YY 2007-01-05 输入1月要得到
日期 CCC DDD VV GG YY
1号 2 1 1 1 0
2号 2 1 1 1 0
3号 1 0 1 1 0
4号 0 0 1 1 1
5号 0 0 0 0 1
. 0 0 0 0 0
. 0 0 0 0 0
31号 0 0 0 0 0
合计 5 2 4 4 2就是根据Bdate(开始日期) 和 Stopdate(结束日期) 求name的次数
解决方案 »
- 存储过程里可以包含游标吗
- 求一存储过程
- vb.net 和 sql server 2005连 执行语句的默认超时时间是多少啊?
- 安装后SQL后,为什么会出现Meta Data Services 缺少REPODBC.dll
- 今日问题:MSSQL数据更新,求指教,求思路
- dbcc checkdb('database name',repair_rebuild)
- 求返回结果
- 请教数据库设计高手!
- sql server什么地方记录用户的操作信息!
- informix中怎样将字符串类型转换成整数型?
- 100分跪求SQL 2000数据排序问题!要写成SQL 语句(在查询分析器中能执行)
- 如何实现SQL中向一个表中插入记录,另一个表中同样字段相应值也更新?
insert ta
select 1, '2007-01-01', 'CCC', '2007-01-03'
union all select 1, '2007-01-01', 'DDD', '2007-01-02'
union all select 1, '2007-01-01', 'VV' , '2007-01-04'
union all select 1, '2007-02-01', 'TTTTT', '2007-02-03'
union all select 2, '2007-01-01', 'GG' , '2007-01-04'
union all select 3, '2007-01-02', 'CCC', '2007-01-03'
union all select 3, '2007-01-01', 'CCC', '2007-01-02'
union all select 4, '2007-01-04', 'YY', '2007-01-05'
时间 CCC DDD GG TTTTT VV YY
---------- ----------- ----------- ----------- ----------- ----------- -----------
2007-01-01 2 1 1 0 1 0
2007-01-02 3 1 1 0 1 0
2007-01-03 2 0 1 0 1 0
2007-01-04 0 0 1 0 1 1
2007-01-05 0 0 0 0 0 1
2007-02-01 0 0 0 1 0 0
2007-02-03 0 0 0 1 0 0(所影响的行数为 7 行)
日期 CCC DDD VV GG YY
1号 2 1 1 1 0
2号 2 1 1 1 0
3号 1 0 1 1 0select 时间=case when grouping(时间)=1 then '合计' else 时间 end,
[CCC]=sum(case when name = 'CCC' and 时间 between Bdate and Stopdate then 1 else 0 end),
[DDD]=sum(case when name = 'DDD'and 时间 between Bdate and Stopdate then 1 else 0 end),
[GG]=sum(case when name = 'GG'and 时间 between Bdate and Stopdate then 1 else 0 end),
[TTTTT]=sum(case when name = 'TTTTT' and 时间 between Bdate and Stopdate then 1 else 0 end),
[VV]=sum(case when name = 'VV'and 时间 between Bdate and Stopdate then 1 else 0 end),
[YY]=sum(case when name = 'YY'and 时间 between Bdate and Stopdate then 1 else 0 end)
from
(select convert(varchar(10),Bdate,120) as 时间 from ta
union
select convert(varchar(10),Stopdate,120) from ta)tb,
ta
group by 时间 WITH ROLLUP
时间 CCC DDD GG TTTTT VV YY
---------- ----------- ----------- ----------- ----------- ----------- -----------
2007-01-01 2 1 1 0 1 0
2007-01-02 3 1 1 0 1 0
2007-01-03 2 0 1 0 1 0
2007-01-04 0 0 1 0 1 1
2007-01-05 0 0 0 0 0 1
2007-02-01 0 0 0 1 0 0
2007-02-03 0 0 0 1 0 0
合计 7 2 4 2 4 2(所影响的行数为 8 行)
3 2007-01-02 CCC 2007-01-03
3 2007-01-01 CCC 2007-01-02
记录
号数在Bdate(开始日期) 和 Stopdate(结束日期)
1号 记录满足有2条
2号 记录满足有3条
3号 记录满足有2条
insert A select 1, '2007-01-01', 'CCC', '2007-01-03'
union all select 1, '2007-01-01', 'DDD', '2007-01-02'
union all select 1, '2007-01-01', 'VV', '2007-01-04'
union all select 1, '2007-02-01', 'TTTTT', '2007-02-03'
union all select 2, '2007-01-01', 'GG', '2007-01-04'
union all select 3, '2007-01-02', 'CCC', '2007-01-03'
union all select 3, '2007-01-01', 'CCC', '2007-01-02'
union all select 4, '2007-01-04', 'YY', '2007-01-05' --
declare @Bdate datetime, @name varchar(10), @Stopdate datetime
declare @dt table(name varchar(10), [date] datetime)declare cur cursor for
select Bdate, name, Stopdate from A where convert(char(7), Bdate, 120)='2007-01'
open cur
fetch next from cur into @Bdate, @name, @Stopdate
while @@fetch_status=0
begin
while @Bdate<=@Stopdate
begin
insert @dt select @name, @Bdate
set @Bdate=@Bdate+1
end fetch next from cur into @Bdate, @name, @Stopdate
end
close cur
deallocate cur
--
select name, [date], num=count(*) into #T
from @dt
group by name, [date]
--
declare @sql varchar(8000)
set @sql='select [date],'
select @sql=@sql+quotename(name)+'=sum(case when name='+quotename(name, '''')+' then num else 0 end),'
from #T
group by name
select @sql=left(@sql, len(@sql)-1), @sql=@sql+'from #T group by [date]'
exec(@sql)--
create table #tDate ([date] datetime)
declare @dt1 datetime, @dt2 datetime
select @dt1='2007-01-01', @dt2='2007-01-31'
while @dt1<=@dt2
begin
insert #tDate select @dt1
set @dt1=@dt1+1
end--
select * from #tDate
left join
(
select [date],
[CCC]=sum(case when name='CCC' then num else 0 end),
[DDD]=sum(case when name='DDD' then num else 0 end),
[GG]=sum(case when name='GG' then num else 0 end),
[VV]=sum(case when name='VV' then num else 0 end),
[YY]=sum(case when name='YY' then num else 0 end)
from #T group by [date]
)tmp on #tDate.[date]=tmp.[date]
insert A select 1, '2007-01-01', 'CCC', '2007-01-03'
union all select 1, '2007-01-01', 'DDD', '2007-01-02'
union all select 1, '2007-01-01', 'VV', '2007-01-04'
union all select 1, '2007-02-01', 'TTTTT', '2007-02-03'
union all select 2, '2007-01-01', 'GG', '2007-01-04'
union all select 3, '2007-01-02', 'CCC', '2007-01-03'
union all select 3, '2007-01-01', 'CCC', '2007-01-02'
union all select 4, '2007-01-04', 'YY', '2007-01-05' --
declare @Bdate datetime, @name varchar(10), @Stopdate datetime
declare @dt table(name varchar(10), [date] datetime)declare cur cursor for
select Bdate, name, Stopdate from A where convert(char(7), Bdate, 120)='2007-01'
open cur
fetch next from cur into @Bdate, @name, @Stopdate
while @@fetch_status=0
begin
while @Bdate<=@Stopdate
begin
insert @dt select @name, @Bdate
set @Bdate=@Bdate+1
end fetch next from cur into @Bdate, @name, @Stopdate
end
close cur
deallocate cur--
select name, [date], num=count(*) into #T
from @dt
group by name, [date]--
create table #TDate ([date] datetime)
declare @dt1 datetime, @dt2 datetime
select @dt1='2007-01-01', @dt2='2007-01-31'
while @dt1<=@dt2
begin
insert #TDate select @dt1
set @dt1=@dt1+1
end--
declare @sql varchar(8000)
set @sql='select * from #tDate left join( select [date],'
select @sql=@sql+quotename(name)+'=sum(case when name='+quotename(name, '''')+' then num else 0 end),'
from #T
group by name
select @sql=left(@sql, len(@sql)-1), @sql=@sql+'from #T group by [date] ) tmp on #tDate.[date]=tmp.[date]'
exec(@sql)--
drop table A
drop table #T
drop table #TDate
insert into test select 1,'2007-01-01','CCC' ,'2007-01-03'
insert into test select 1,'2007-01-01','DDD' ,'2007-01-02'
insert into test select 1,'2007-01-01','VV' ,'2007-01-04'
insert into test select 1,'2007-02-01','TTTTT','2007-02-03'
insert into test select 2,'2007-01-01','GG' ,'2007-01-04'
insert into test select 3,'2007-01-02','CCC' ,'2007-01-03'
insert into test select 3,'2007-01-01','CCC' ,'2007-01-02'
insert into test select 4,'2007-01-04','YY' ,'2007-01-05'
godeclare @sql varchar(8000)
set @sql=''select @sql=@sql+',['+rtrim(name)+']=sum(case name when '''+rtrim(name)+''' then 1 else 0 end)'
from test group by nameset @sql= 'select isnull(date,''合计'') as date'+@sql
+' from (select rtrim(datepart(dd,Bdate)) as date,* from test where datediff(mm,Bdate,''2007-01-01'')=0) t '
+'group by date with rollup'exec(@sql)
go/*
date CCC DDD GG TTTTT VV YY
------------ ----------- ----------- ----------- ----------- ----------- -----------
1 2 1 1 1 1 0
2 1 0 0 0 0 0
4 0 0 0 0 0 1
合计 3 1 1 1 1 1
*/drop table test
go
create table test(id int,Bdate datetime,name varchar(10),Stopdate datetime)
insert into test select 1,'2007-01-01','CCC' ,'2007-01-03'
insert into test select 1,'2007-01-01','DDD' ,'2007-01-02'
insert into test select 1,'2007-01-01','VV' ,'2007-01-04'
insert into test select 1,'2007-02-01','TTTTT','2007-02-03'
insert into test select 2,'2007-01-01','GG' ,'2007-01-04'
insert into test select 3,'2007-01-02','CCC' ,'2007-01-03'
insert into test select 3,'2007-01-01','CCC' ,'2007-01-02'
insert into test select 4,'2007-01-04','YY' ,'2007-01-05'
godeclare @sql varchar(8000)
set @sql=''select @sql=@sql+',['+rtrim(name)+']=sum(case name when '''+rtrim(name)+''' then 1 else 0 end)'
from test group by nameset @sql= 'select isnull(date,''合计'') as date'+@sql
+' from (select rtrim(datepart(dd,Bdate)) as date,* from test where datediff(mm,Bdate,''2007-01-01'')=0) t '
+'group by date with rollup'exec(@sql)
go/*
date CCC DDD GG TTTTT VV YY
------------ ----------- ----------- ----------- ----------- ----------- -----------
1 2 1 1 1 1 0
2 1 0 0 0 0 0
4 0 0 0 0 0 1
合计 3 1 1 1 1 1
*/drop table test
go
-----------------------------------------------------------------
比较简练,我也常用动态SQL,感觉很过瘾!
是
输入1月要得到
日期 CCC DDD VV GG YY
1号 2 1 1 1 0
2号 2 1 1 1 0
3号 1 0 1 1 0
4号 0 0 1 1 1
5号 0 0 0 0 1
. 0 0 0 0 0
. 0 0 0 0 0
31号 0 0 0 0 0
合计 5 2 4 4 2Top
zjcxc(邹建)的可以但他没考虑 Stopdate(结束日期) CREATE TABLE A(id int, date datetime, name varchar(10))
INSERT A SELECT 1, '2007-01-01', 'CCC'
UNION ALL SELECT 1, '2007-01-01', 'DDD'
UNION ALL SELECT 1, '2007-01-01', 'VV'
UNION ALL SELECT 1, '2007-02-01', 'TTTTT'
UNION ALL SELECT 2, '2007-01-01', 'GG'
UNION ALL SELECT 3, '2007-01-02', 'CCC'
UNION ALL SELECT 3, '2007-01-01', 'CCC'
UNION ALL SELECT 4, '2007-01-04', 'YY'
GO-- 查询的存储过程
CREATE PROC p_qry
@yearmonth int
AS
SET NOCOUNT ON
DECLARE @BeginDate datetime, @EndDate datetime, @row int
SELECT
@BeginDate = CONVERT(datetime, RIGHT(@yearmonth * 100 + 1, 8)),
@EndDate = DATEADD(Month, 1, @BeginDate),
@row = Day(@EndDate - 1)DECLARE @dt TABLE(id int identity(1, 1), date datetime)
SET ROWCOUNT @row
INSERT @dt(date) SELECT NULL FROM syscolumns A, syscolumns B
SET ROWCOUNT 0
UPDATE @dt SET date = DATEADD(Day, id - 1, @BeginDate)SELECT
日期 = CASE GROUPING(D.id)
WHEN 0 THEN CONVERT(varchar(10), D.id) + '号'
ELSE '合计' END,
CCC = ISNULL(SUM(CASE A.name WHEN 'CCC' THEN 1 ELSE 0 END), 0),
DDD = ISNULL(SUM(CASE A.name WHEN 'DDD' THEN 1 ELSE 0 END), 0),
VV = ISNULL(SUM(CASE A.name WHEN 'VV' THEN 1 ELSE 0 END), 0),
GG = ISNULL(SUM(CASE A.name WHEN 'GG' THEN 1 ELSE 0 END), 0),
YY = ISNULL(SUM(CASE A.name WHEN 'YY' THEN 1 ELSE 0 END), 0)
FROM(
SELECT * FROM A
WHERE date > @BeginDate
AND date < @EndDate
)A
RIGHT JOIN @dt D
ON A.date = D.date
GROUP BY D.ID WITH ROLLUP
ORDER BY GROUPING(D.id), D.id
GO-- 调用
EXEC p_qry 200601
GO-- 删除测试
DROP TABLE A
DROP PROC P_qry请问怎么改