走过路过，进来看下

rui90102 2010-09-07 03:33:50

有一个表它有4个字段
时间 name 备注积分
2010-5-6 a private 50
2010-5-6 a public 30
2010-5-6 b public 40
2010-5-7 a qunfa 100

现在我要统计每天，哪个人，私聊了多少积分，公开聊了多少积分，群聊了多少积分

显示出来是这样的
日期 name 私聊/群聊/公开
2010-5-6 a 50/0/30
2010-5-6 b 0/0/40
2010-5-7 a 0/100/0

...全文

215 16 打赏收藏转发到动态举报

写回复

用AI写文章

16 条回复

切换为时间正序

请发表友善的回复…

发表回复

icehaopan 2010-09-07

打赏
举报

select
c.name1,
c.dateTime1,
sum(c.siYouDe),
sum(c.gongGongDe),
sum(c.qunFaDe)
from
(select
b.name1,
b.dateTime1,

case b.beiZhu when 'private' then sum(b.jiFen) else 0
end siYouDe,
case b.beiZhu when 'public' then sum(b.jiFen) else 0
end gongGongDe,
case b.beiZhu when 'qunfa' then sum(b.jiFen) else 0
end qunFaDe
from
(select a.name as name1, a.dateTimes as dateTime1 , a.beiZhu as beiZhu, a.jiFen as jiFen
from test a
) b

where
b.beiZhu in ('public','private','qunfa')

group by
b.name1,
b.dateTime1,
b.beiZhu
)c
group by
c.name1,
c.dateTime1

icehaopan 2010-09-07

打赏
举报

select

c.name1,
c.dateTime1,
sum(c.siYouDe) as siYouDe ,
sum(c.gongGongDe) as gongGongDe,
sum(c.qunFaDe) as qunFaDe
from

((select a.name as name1, a.dateTimes as dateTime1, a.jiFen as siYouDe , 0 as gongGongDe , 0 as qunFaDe
from test a
where a.beiZhu = 'private'
)
union
(
select a.name as name1, a.dateTimes as dateTime1, 0 as siYouDe ,a.jiFen as gongGongDe , 0 as qunFaDe
from test a
where a.beiZhu = 'public'

)
union
(
select a.name as name1, a.dateTimes as dateTime1, 0 as siYouDe ,0 as gongGongDe,a.jiFen as qunFaDe
from test a
where a.beiZhu = 'qunfa'

)
) c

group by
c.name1,
c.dateTime1

symbol_bc 2010-09-07

打赏
举报

那你怎么区别私聊，公开聊，还是群聊呢，哪个字段标示。

rui90102 2010-09-07

打赏
举报

[Quote=引用 10 楼 symbol_bc 的回复:]

什么意思，你是说备注一列要模糊查询吗？可以再最里层
SQL code

select *
,(case comment when 'private' then point end ) 'private'
,(case comment when 'public' then point end ) 'public'
,(case comment when 'qunfa' then point e……
[/Quote]

是说备注像privateaaaa 这样的都查询出来

lthaoyue 2010-09-07

打赏
举报

写错误应是sum
select sum(积分) as 积分时间 name 备注
from tbl group by 时间,name,备注

lthaoyue 2010-09-07

打赏
举报

select count(积分) as 积分时间 name 备注
from tbl group by 时间,name,备注

symbol_bc 2010-09-07

打赏
举报

什么意思，你是说备注一列要模糊查询吗？可以再最里层



select *

,(case comment when 'private' then point end ) 'private'

,(case comment when 'public' then point end ) 'public'

,(case comment when 'qunfa' then point end ) 'qunfa' from #1

where comment like ''

还是说comment列的值不是固定的？

rui90102 2010-09-07

打赏
举报

[Quote=引用 7 楼 symbol_bc 的回复:]

SQL code

create table #1
(
[ID] [int] IDENTITY(1,1) NOT NULL,
[createDate] [date] NULL,
[name] [nvarchar](50) NULL,
[comment] [nvarchar](50) NULL,
[point] [int] NULL
)
go

insert i……
[/Quote]

private 我要写模糊查询要怎么写呀

andyflyzhao 2010-09-07

打赏
举报

楼上很好很强大！

symbol_bc 2010-09-07

打赏
举报



create table #1 

(

[ID] [int] IDENTITY(1,1) NOT NULL,

	[createDate] [date] NULL,

	[name] [nvarchar](50) NULL,

	[comment] [nvarchar](50) NULL,

	[point] [int] NULL

)

go



insert into #1 (createdate ,[name],comment,point) values ('2010-05-06','a','private',50)

insert into #1 (createdate ,[name],comment,point) values ('2010-05-06','a','public',30)

insert into #1 (createdate ,[name],comment,point) values ('2010-05-06','b','public',40)

insert into #1 (createdate ,[name],comment,point) values ('2010-05-07','a','qunfa',100)



select createdate,[name], 

cast(isnull([private],0) as nvarchar) + '/' + cast(isnull([public],0) as nvarchar) + '/' + cast(isnull([qunfa],0) as nvarchar)

from (

select createdate,[name],

SUM([private]) 'private',SUM([public]) 'public' ,SUM([qunfa]) 'qunfa'

from (select *

,(case comment when 'private' then point end ) 'private'

,(case comment when 'public' then point end ) 'public'

,(case comment when 'qunfa' then point end ) 'qunfa' from #1) a

group by createdate,[name]) b



drop table #1