select no,type,free,(cast(year(time) as char(4)) + '-' + cast(month(time) as char(2))) as newtime
from
表A
group by
no,type,free,(cast(year(time) as char(4)) + '-' + cast(month(time) as char(2)))
from
表A
group by
no,type,free,(cast(year(time) as char(4)) + '-' + cast(month(time) as char(2)))
=1 则是按条计算啊 包月的 只算一次
按条计算的当然要一条条相加啊 我这边数据量好大的
有人帮帮忙吗?
select [no],type,(case type when 0 then fee
when 1 then sum(fee) end),(cast(year(time) as char(4)) + '-' + cast(month(time) as char(2)))
from a
group by no,type,fee,(cast(year(time) as char(4)) + '-' + cast(month(time) as char(2)))
Create Table t
([NO.] varchar(5),
TYPE Varchar(1),
FEE int,
[TIME] datetime
)
--插入数据 Insert t Values('1234', '0', 500, '2004-10-10 10:00:00')
Insert t Values('1234', '0', 500, '2004-10-10 11:00:00')
Insert t Values('1234', '0', 500, '2004-10-11 9:00:00')
Insert t Values('1234', '0', 500, '2004-11-1 9:00:00')
Insert t Values('1234', '0', 500, '2004-11-2 10:00:00')
Insert t Values('1235', '1', 10, '2004-10-11 10:00:00')
Insert t Values('1235', '1', 10, '2004-10-12 10:23:23')
Insert t Values('1235', '1', 10, '2004-10-13 5:00:23')
Insert t Values('1235', '1', 10, '2004-11-12 12:12:12')
Insert t Values('1235', '1', 10, '2004-11-13 13:15:20')
GO--测试
Select t_.[NO.],t_.TYPE,SUM(t_.FEE) AS FEE,t_.TIME_
from (Select [NO.],TYPE,FEE,(RIGHT(LEFT([TIME],10),4)+ ' ' + LEFT([TIME],2)) AS TIME_ from t) AS t_
where type=1
group by t_.[NO.],t_.TYPE,t_.TIME_
UNION ALL
Select t_.[NO.],t_.TYPE,MAX(t_.FEE) AS FEE,t_.TIME_
from (Select [NO.],TYPE,FEE,(RIGHT(LEFT([TIME],10),4)+ ' ' + LEFT([TIME],2)) AS TIME_ from t) AS t_
where type=0
group by t_.[NO.],t_.TYPE,t_.TIME_
--删除测试环境
Truncate table t
Drop table t
GO
--测试Select t_.[NO.],t_.TYPE,SUM(t_.FEE) AS FEE,t_.TIME_
from (Select [NO.],TYPE,FEE,(cast(year([TIME]) as varchar) + cast(month([time]) as varchar)) AS TIME_ from t) AS t_
where type=1
group by t_.[NO.],t_.TYPE,t_.TIME_
UNION ALL
Select t_.[NO.],t_.TYPE,MAX(t_.FEE) AS FEE,t_.TIME_
from (Select [NO.],TYPE,FEE,(cast(year([TIME]) as varchar) + cast(month([time]) as varchar)) AS TIME_ from t) AS t_
where type=0
group by t_.[NO.],t_.TYPE,t_.TIME_
Select t_.[NO.],t_.TYPE,
(case t_.type
when 0 then max(t_.fee)
when 1 then sum(t_.fee) end) AS FEE,t_.TIME_
from (Select [NO.],TYPE,FEE,(cast(year([TIME]) as varchar) + cast(month([time]) as varchar)) AS TIME_ from t) AS t_
--where type=0
group by t_.[NO.],t_.TYPE,t_.TIME_
select No,type,fee=(case when type=0 then max(fee) else sum(fee) end),convert(varchar(7),TIME,120) as time from A group by convert(varchar(7),TIME,120),type,No order by fee,time