有一个表它有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
select 备注 count(积分) from table where name='a' group by 备注
select f.times,f.names,(case when) as 综合 from jifen as f group by f.times ,f.names 这个还没写完 前面的 日期跟name都查出来了,()里面的用case去判断实现
create table #1 ( [ID] [int] IDENTITY(1,1) NOT NULL, [createDate] [date] NULL, [name] [nvarchar](50) NULL, [comment] [nvarchar](50) NULL, [point] [int] NULL ) goinsert 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]) bdrop table #1
private 我要写模糊查询要怎么写呀
什么意思,你是说备注一列要模糊查询吗?可以再最里层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列的值不是固定的?
select count(积分) as 积分 时间 name 备注 from tbl group by 时间,name,备注
写错误应是sum select sum(积分) as 积分 时间 name 备注 from tbl group by 时间,name,备注
是说 备注像privateaaaa 这样的都查询出来
selectc.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') ) cgroup by c.name1, c.dateTime1
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 ) bwhere b.beiZhu in ('public','private','qunfa')group by b.name1, b.dateTime1, b.beiZhu )c group by c.name1, c.dateTime1
create table #1
(
[ID] [int] IDENTITY(1,1) NOT NULL,
[createDate] [date] NULL,
[name] [nvarchar](50) NULL,
[comment] [nvarchar](50) NULL,
[point] [int] NULL
)
goinsert 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]) bdrop table #1
private 我要写模糊查询要怎么写呀
,(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列的值不是固定的?
from tbl group by 时间,name,备注
select sum(积分) as 积分 时间 name 备注
from tbl group by 时间,name,备注
是说 备注像privateaaaa 这样的都查询出来
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')
) cgroup by
c.name1,
c.dateTime1
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
) bwhere
b.beiZhu in ('public','private','qunfa')group by
b.name1,
b.dateTime1,
b.beiZhu
)c
group by
c.name1,
c.dateTime1