22,207
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE [dbo].[test88](
[id] [int] IDENTITY(1,1) NOT NULL, --ID 自增长
[isType] [int] NULL, --类型 1增加 2减少
[fraction] [int] NULL, --分数
[operateTime] [datetime] NULL --时间
(
select count(id) as idcount,CONVERT(varchar(10),operateTime,20) as operateTime
from test88 group by CONVERT(varchar(10),operateTime,20)
select
(select SUM(fraction) from test88
where isType=1 and
CONVERT(varchar(10),a.operateTime,20)=CONVERT(varchar(10),operateTime,20)) 增加,
(select SUM(fraction) from test88
where isType=2 and
CONVERT(varchar(10),a.operateTime,20)=CONVERT(varchar(10),operateTime,20)) 增加,
[operateTime] 时间 from test88 a
这样查就行
select sum(case when isType=1 then fraction else 0 end) '增加',
sum(case when isType=2 then fraction else 0 end) '减少',
convert(varchar(10),operateTime,20) '时间'
from test88
group by convert(varchar(10),operateTime,20)