一张表里有两个字段ShipQty,ShipDate
其中shipqty的值都是1,ShipDate是时间内容有不同的日期,例如:
shipqty ShipDate
1 2004-2-1
1 2004-2-24
1 2004-3-5
1 2004-4-7
1 2004-4-28要实现以下要求:
Shipqty(2004-2月) Shipqty(2004-3月) Shipqty(2004-4月)
2 1 2
就是从表中查询出按月为单位统计出Shipqty,例如我的查询条件是2004-2-1到2004-2-28
则把shipqty相加起来,其中2月份的记录有两个所以结果shipqty等于2,,在线等.
其中shipqty的值都是1,ShipDate是时间内容有不同的日期,例如:
shipqty ShipDate
1 2004-2-1
1 2004-2-24
1 2004-3-5
1 2004-4-7
1 2004-4-28要实现以下要求:
Shipqty(2004-2月) Shipqty(2004-3月) Shipqty(2004-4月)
2 1 2
就是从表中查询出按月为单位统计出Shipqty,例如我的查询条件是2004-2-1到2004-2-28
则把shipqty相加起来,其中2月份的记录有两个所以结果shipqty等于2,,在线等.
解决方案 »
- 求一触发器,实现在insert,update时对表的操作。
- 关于报表SQL查询时返回条件为指定字符开头的问题!
- 临时表在函数里的写法
- if (select count(*) from table_name where 条件) 与 if exists(select * from table_name where 条件) 哪个效率更高?
- 数据库 每次打开总是显示(正在恢复),过一会就好了
- 关于数据库导出ID重复的问题
- 急问``~~~~~~大家都进来看看
- 求一sql语句,万望帮忙啊~~
- 怎样取过程返回的一个数据集?
- 怎么样知道数据库的访问量?
- 如何捕获删除记录出错的 返回值
- 在执行存储过程中,如何获取其返回值?
Shipqty(2004-2月) Shipqty(2004-3月) Shipqty(2004-4月) Shipqty(2004-5月)......
insert into @t
select 1,'2004-2-1'
union all
select 1,'2004-2-24'
union all
select 1,'2004-3-5'
union all
select 1,'2004-4-7'
union all
select 1,'2004-4-28'select * from @t
select substring(task,1,6) 月份,count(substring(task,1,6))次数
from @t
group by substring(task,1,6)
谢谢,你的结果是:
月份 次数
---------- -----------
2004-2 2
2004-3 1
2004-4 2
不过要的是横向排列像这样
月份2004-2 月份2004-3 月份2004-4
2 1 2
substring(task,1,6),当月份是双位的时候会有问题
你看看吧
insert into @t
select 1,'2004-2-1'
union all
select 1,'2004-2-24'
union all
select 1,'2004-3-5'
union all
select 1,'2004-4-7'
union all
select 1,'2004-4-28'select sum(CID),Y,M from (select CID,year(Task) As Y,month(Task) As M from @t)t group by Y,M
create table t (CID int,Task datetime)
insert into t
select 1,'2004-2-1'
union all
select 1,'2004-2-24'
union all
select 1,'2004-3-5'
union all
select 1,'2004-4-7'
union all
select 1,'2004-4-28'
declare @T_SQL as varchar(8000)
set @T_SQL=''
select @T_SQL=@T_SQL + 'max(case when M=''' +M +''' then Task else 0 end )as ''' + +M +''','
from
(select distinct cast(year(Task) as varchar)+'年' +cast(month(Task) as varchar)+'月' As M from t) T
set @T_SQL='select ' + left(@T_SQL,len(@T_SQL)-1) + ' from (select sum(CID)as Task,M from (select CID,cast(year(Task) as varchar)+''年'' +cast(month(Task) as varchar)+''月'' As M from t)t group by M) Z group by M'
exec (@T_SQL)
set @T_SQL=''
select @T_SQL=@T_SQL + 'max(case when M=''' +M +''' then Task else 0 end )as ''' + +M +''','
from
(select distinct cast(year(Task) as varchar)+'年' +cast(month(Task) as varchar)+'月' As M from t) T
set @T_SQL='select ' + left(@T_SQL,len(@T_SQL)-1) + ' from (select sum(CID)as Task,M from (select CID,cast(year(Task) as varchar)+''年'' +cast(month(Task) as varchar)+''月'' As M from t)t group by M) Z '
exec (@T_SQL)
table group by convert(varchar(6),ShipDate,112)
where shipdate between '2004-2-1' and '2004-4-28'
group by cast(year(shipdate) as varchar(4))+'-'+cast(month(shipdate) as varchar(2))
正解,好強啊
用你的方法可行,但是要的是显示连续月份
像:
2004年2月 2004年3月 2004年4月 2004年4月 2004年5月 ..............
假如shipdate字段里没有2004年5月的时候也让它显示出来,就是说显示出来的字段是按月递增,这样能办到吗?
注:传进来的参数时间是2004-2-1 至2005-3-10,那么列数就应该有14列即2004-2-1至2005-3-10有14个月
如果查询时间是:2004-2-1 至2005-3-10,那么列数就应该有14列即2004-2-1至2005-3-10有14个月2004年2月 2004年3月 2004年4月 2004年5月 2004年6月 ..............2005年3月
2 1 2 0 0 0
insert into t
select 1,'2004-2-1'
union all
select 1,'2004-2-24'
union all
select 1,'2004-3-5'
union all
select 1,'2004-4-7'
union all
select 1,'2004-4-28'
create proc Test_TT(@begin datetime,@end datetime)
as
declare @T_SQL varchar(8000)
create table #t (M varchar(20))
set @T_SQL=''
while datediff(mm,@begin,@end)>=0
begin
insert #t select cast(year(@begin) as varchar)+'年' +cast(month(@begin) as varchar)+'月'
set @begin=dateadd(mm,1,@begin)
end
select @T_SQL=@T_SQL + 'sum(case when M=''' + M + ''' then isnull(CID,0) else 0 end) as ''' + M +''','
from
(select T.M,A.CID
from #t T,(select sum(CID)as CID ,cast(year(Task) as varchar)+'年' +cast(month(Task) as varchar)+'月' as Task from T group by cast(year(Task) as varchar)+'年' +cast(month(Task) as varchar)+'月') A
where T.M *= A.Task) z
set @T_SQL='select ' + left(@T_SQL,len(@T_SQL)-1) + 'from (select T.M,A.CID from #t T,(select sum(CID)as CID ,cast(year(Task) as varchar)+''年'' +cast(month(Task) as varchar)+''月'' as Task from T group by cast(year(Task) as varchar)+''年'' +cast(month(Task) as varchar)+''月'') A where T.M *= A.Task) z'
exec (@T_SQL)exec Test_TT '2004-2-1','2005-3-10'
insert into #
select 1,'2004-2-1'
union all
select 1,'2004-2-24'
union all
select 1,'2004-3-5'
union all
select 1,'2004-4-7'
union all
select 1,'2004-4-28'
union all
select 1,'2004-5-28' select * from #declare @t1 table(task_gp varchar(8))
declare @s varchar(8000)declare cursor1 cursor for select convert(varchar(6),Task,112) from # group by convert(varchar(6),Task,112)
open cursor1
fetch next from cursor1 into @s
while @@fetch_status = 0
begin
insert into @t1
select @s
fetch next from cursor1 into @s
endclose cursor1
deallocate cursor1declare @s1 varchar(8000)
declare @s2 varchar(8000)select @s1 = ''select @s1 = @s1 + ',sum(case convert(varchar(6),Task,112) when '''+ task_gp + ''' then 1 else 0 end)' + ' as ''Task (' + task_gp + '月)''' from @t1select @s1 = stuff(@s1 , 1 , 1,'')
select @s1 = 'select ' + @s1 + ' from # '
exec(@s1)
drop table #
http://life.fzylw.com/info/info.php?id=1186