select person,sum(case when do_action ='看电影' then 1 else 0 end) as 看电影次数, sum(case when do_action ='玩游戏 ' then 1 else 0 end) as 玩游戏次数 group by person
select person , sum(case do_action when '看电影' then 1 else 0 end) as 看电影次数, sum(case do_action when '玩游戏' then 1 else 0 end) as 玩游戏次数 from tab_ActionList group by person
select person,看电影次数=sum(case do_action when '看电影' then 1 else 0 end), 玩游戏次数=sum(case do_action when '玩游戏' then 1 else 0 end) from tab_ActionList group by person order by person
select person, sum(case when do_action ='看电影' then 1 else 0 end) as 看电影次数, sum(case when do_action ='玩游戏' then 1 else 0 end) as 玩游戏次数 group by person
--------------------------------- -- Author: htl258(Tony) -- Date : 2009-08-04 09:54:49 --------------------------------- --> 生成测试数据表:tab_ActionListIf not object_id('[tab_ActionList]') is null Drop table [tab_ActionList] Go Create table [tab_ActionList]([Id] int,[date_time] Datetime,[person] nvarchar(2),[do_action] nvarchar(3)) Insert tab_ActionList Select 1,'2009-6-22','小张','看电影' union all Select 2,'2009-6-22','小张','玩游戏' union all Select 3,'2009-6-29','小张','玩游戏' union all Select 4,'2009-6-1','小王','看电影' Go --Select * from tab_ActionList-->SQL查询如下:select person , sum(case do_action when '看电影' then 1 else 0 end) as 看电影次数, sum(case do_action when '玩游戏' then 1 else 0 end) as 玩游戏次数 from tab_ActionList group by person /* person 看电影次数 玩游戏次数 ------ ----------- ----------- 小王 1 0 小张 1 2(2 行受影响) */
---------------------------------------------------------------- -- Author :fredrickhu(小F 向高手学习) -- Date :2009-08-04 09:55:39 ---------------------------------------------------------------- --> 测试数据:[tab_ActionList] if object_id('[tab_ActionList]') is not null drop table [tab_ActionList] create table [tab_ActionList]([Id] int,[date_time] datetime,[person] varchar(4),[do_action] varchar(6)) insert [tab_ActionList] select 1,'2009-6-22','小张','看电影' union all select 2,'2009-6-22','小张','玩游戏' union all select 3,'2009-6-29','小张','玩游戏' union all select 4,'2009-6-1','小王','看电影' --------------开始查询--------------------------select person , sum(case do_action when '看电影' then 1 else 0 end) as 看电影次数, sum(case do_action when '玩游戏' then 1 else 0 end) as 玩游戏次数 from tab_ActionList group by person ----------------结果---------------------------- /* person 看电影次数 玩游戏次数 ------ ----------- ----------- 小王 1 0 小张 1 2(所影响的行数为 2 行)*/
declare @tab_ActionList table(Id int, date_time datetime, person varchar(10), do_action varchar(10))
insert @tab_ActionList select 4 ,'2009-6-1' ,'小王' ,'看电影 'select person,月份=convert(varchar(7),date_time,120),看电影次数=sum(case do_action when '看电影' then 1 else 0 end), 玩游戏次数=sum(case do_action when '玩游戏' then 1 else 0 end) from @tab_ActionList group by person,convert(varchar(7),date_time,120) order by person,convert(varchar(7),date_time,120)/* person 月份 看电影次数 玩游戏次数 ---------- ------- ----------- ----------- 小王 2009-06 1 0 小张 2009-06 1 2(所影响的行数为 2 行) */
还需要加一个判断月份的 where语句吧?
select person , sum(case do_action when '看电影' then 1 else 0 end) as 看电影次数, sum(case do_action when '玩游戏' then 1 else 0 end) as 玩游戏次数 from tab_ActionList group by person
declare @tab_ActionList table(Id int, date_time datetime, person varchar(10), do_action varchar(10))
insert @tab_ActionList select 4 ,'2009-6-1' ,'小王' ,'看电影 'select person,月份=convert(varchar(7),date_time,120),看电影次数=sum(case do_action when '看电影' then 1 else 0 end), 玩游戏次数=sum(case do_action when '玩游戏' then 1 else 0 end) --加条件 from @tab_ActionList where datediff(mm,date_time,'2009-06-01') =0 group by person,convert(varchar(7),date_time,120) order by person,convert(varchar(7),date_time,120)/* person 月份 看电影次数 玩游戏次数 ---------- ------- ----------- ----------- 小王 2009-06 1 0 小张 2009-06 1 2(所影响的行数为 2 行) */
If not object_id('[tab_ActionList]') is null Drop table [tab_ActionList] Go Create table [tab_ActionList]([Id] int,[date_time] Datetime,[person] nvarchar(2),[do_action] nvarchar(3)) Insert tab_ActionList Select 1,'2009-6-22','小张','看电影' union all Select 2,'2009-6-22','小张','玩游戏' union all Select 3,'2009-6-29','小张','玩游戏' union all Select 4,'2009-6-1','小王','看电影' Go --Select * from tab_ActionList-->SQL查询如下:select person , sum(case do_action when '看电影' then 1 else 0 end) as 看电影次数, sum(case do_action when '玩游戏' then 1 else 0 end) as 玩游戏次数 from tab_ActionList where datepart(mm,[date_time])=6--6月份 group by person
需要限制月份的话这样---------------------------------------------------------------- -- Author :fredrickhu(小F 向高手学习) -- Date :2009-08-04 09:55:39 ---------------------------------------------------------------- --> 测试数据:[tab_ActionList] if object_id('[tab_ActionList]') is not null drop table [tab_ActionList] create table [tab_ActionList]([Id] int,[date_time] datetime,[person] varchar(4),[do_action] varchar(6)) insert [tab_ActionList] select 1,'2009-6-22','小张','看电影' union all select 2,'2009-6-22','小张','玩游戏' union all select 3,'2009-6-29','小张','玩游戏' union all select 4,'2009-6-1','小王','看电影' --------------开始查询--------------------------select person , sum(case do_action when '看电影' then 1 else 0 end) as 看电影次数, sum(case do_action when '玩游戏' then 1 else 0 end) as 玩游戏次数 from tab_ActionList where datename(mm,[date_time])=6 group by person ----------------结果---------------------------- /* person 看电影次数 玩游戏次数 ------ ----------- ----------- 小王 1 0 小张 1 2(所影响的行数为 2 行)*/
--------------------------------- -- Author: Beirut(贝鲁特) -- Comment:小爱 -- Date : 2009-08-04 10:02:49 ---------------------------------if object_id('tb') is not null drop table tb go create table tb([Id] int,[date_time] datetime,[person] varchar(4),[do_action] varchar(6)) go insert tb select 1,'2009-6-22','小张','看电影' union all select 2,'2009-6-22','小张','玩游戏' union all select 3,'2009-6-29','小张','玩游戏' union all select 4,'2009-6-1','小王','看电影'SELECT [person], SUM(CASE [do_action] WHEN '看电影' THEN 1 ELSE 0 END) AS '看电影', SUM(CASE [do_action] WHEN '玩游戏' THEN 1 ELSE 0 END) AS '玩游戏' FROM tb GROUP BY [person] --[do_action] 不止看电影和玩游戏两种使用动态SQL --SQL 2000 DECLARE @SQL VARCHAR(4000) SET @SQL='SELECT [person]' SELECT @SQL=@SQL+',SUM(CASE [do_action] WHEN '''+ [do_action] +''' THEN 1 ELSE 0 END ) AS ['+do_action+']' FROM (SELECT DISTINCT [do_action] FROM tb) AS a SET @SQL=@SQL+'FROM tb GROUP BY [person] ' EXEC(@SQL) /*person 看电影 玩游戏 ------ ----------- ----------- 小王 1 0 小张 1 2(2 行受影响)*/
sum(case when do_action ='玩游戏 ' then 1 else 0 end) as 玩游戏次数
group by person
sum(case do_action when '看电影' then 1 else 0 end) as 看电影次数,
sum(case do_action when '玩游戏' then 1 else 0 end) as 玩游戏次数
from tab_ActionList
group by person
玩游戏次数=sum(case do_action when '玩游戏' then 1 else 0 end)
from tab_ActionList
group by person
order by person
sum(case when do_action ='看电影' then 1 else 0 end) as 看电影次数,
sum(case when do_action ='玩游戏' then 1 else 0 end) as 玩游戏次数
group by person
---------------------------------
-- Author: htl258(Tony)
-- Date : 2009-08-04 09:54:49
---------------------------------
--> 生成测试数据表:tab_ActionListIf not object_id('[tab_ActionList]') is null
Drop table [tab_ActionList]
Go
Create table [tab_ActionList]([Id] int,[date_time] Datetime,[person] nvarchar(2),[do_action] nvarchar(3))
Insert tab_ActionList
Select 1,'2009-6-22','小张','看电影' union all
Select 2,'2009-6-22','小张','玩游戏' union all
Select 3,'2009-6-29','小张','玩游戏' union all
Select 4,'2009-6-1','小王','看电影'
Go
--Select * from tab_ActionList-->SQL查询如下:select person ,
sum(case do_action when '看电影' then 1 else 0 end) as 看电影次数,
sum(case do_action when '玩游戏' then 1 else 0 end) as 玩游戏次数
from tab_ActionList
group by person
/*
person 看电影次数 玩游戏次数
------ ----------- -----------
小王 1 0
小张 1 2(2 行受影响)
*/
-- Author :fredrickhu(小F 向高手学习)
-- Date :2009-08-04 09:55:39
----------------------------------------------------------------
--> 测试数据:[tab_ActionList]
if object_id('[tab_ActionList]') is not null drop table [tab_ActionList]
create table [tab_ActionList]([Id] int,[date_time] datetime,[person] varchar(4),[do_action] varchar(6))
insert [tab_ActionList]
select 1,'2009-6-22','小张','看电影' union all
select 2,'2009-6-22','小张','玩游戏' union all
select 3,'2009-6-29','小张','玩游戏' union all
select 4,'2009-6-1','小王','看电影'
--------------开始查询--------------------------select person ,
sum(case do_action when '看电影' then 1 else 0 end) as 看电影次数,
sum(case do_action when '玩游戏' then 1 else 0 end) as 玩游戏次数
from tab_ActionList
group by person
----------------结果----------------------------
/*
person 看电影次数 玩游戏次数
------ ----------- -----------
小王 1 0
小张 1 2(所影响的行数为 2 行)*/
insert @tab_ActionList select 1 ,'2009-6-22' ,'小张' ,'看电影'
insert @tab_ActionList select 2 ,'2009-6-22' ,'小张' ,'玩游戏'
insert @tab_ActionList select 3 ,'2009-6-29' ,'小张' ,'玩游戏'
insert @tab_ActionList select 4 ,'2009-6-1' ,'小王' ,'看电影 'select person,月份=convert(varchar(7),date_time,120),看电影次数=sum(case do_action when '看电影' then 1 else 0 end),
玩游戏次数=sum(case do_action when '玩游戏' then 1 else 0 end)
from @tab_ActionList
group by person,convert(varchar(7),date_time,120)
order by person,convert(varchar(7),date_time,120)/*
person 月份 看电影次数 玩游戏次数
---------- ------- ----------- -----------
小王 2009-06 1 0
小张 2009-06 1 2(所影响的行数为 2 行)
*/
sum(case do_action when '看电影' then 1 else 0 end) as 看电影次数,
sum(case do_action when '玩游戏' then 1 else 0 end) as 玩游戏次数
from tab_ActionList
group by person
insert @tab_ActionList select 1 ,'2009-6-22' ,'小张' ,'看电影'
insert @tab_ActionList select 2 ,'2009-6-22' ,'小张' ,'玩游戏'
insert @tab_ActionList select 3 ,'2009-6-29' ,'小张' ,'玩游戏'
insert @tab_ActionList select 4 ,'2009-6-1' ,'小王' ,'看电影 'select person,月份=convert(varchar(7),date_time,120),看电影次数=sum(case do_action when '看电影' then 1 else 0 end),
玩游戏次数=sum(case do_action when '玩游戏' then 1 else 0 end)
--加条件
from @tab_ActionList where datediff(mm,date_time,'2009-06-01') =0
group by person,convert(varchar(7),date_time,120)
order by person,convert(varchar(7),date_time,120)/*
person 月份 看电影次数 玩游戏次数
---------- ------- ----------- -----------
小王 2009-06 1 0
小张 2009-06 1 2(所影响的行数为 2 行)
*/
Drop table [tab_ActionList]
Go
Create table [tab_ActionList]([Id] int,[date_time] Datetime,[person] nvarchar(2),[do_action] nvarchar(3))
Insert tab_ActionList
Select 1,'2009-6-22','小张','看电影' union all
Select 2,'2009-6-22','小张','玩游戏' union all
Select 3,'2009-6-29','小张','玩游戏' union all
Select 4,'2009-6-1','小王','看电影'
Go
--Select * from tab_ActionList-->SQL查询如下:select person ,
sum(case do_action when '看电影' then 1 else 0 end) as 看电影次数,
sum(case do_action when '玩游戏' then 1 else 0 end) as 玩游戏次数
from tab_ActionList where datepart(mm,[date_time])=6--6月份
group by person
-- Author :fredrickhu(小F 向高手学习)
-- Date :2009-08-04 09:55:39
----------------------------------------------------------------
--> 测试数据:[tab_ActionList]
if object_id('[tab_ActionList]') is not null drop table [tab_ActionList]
create table [tab_ActionList]([Id] int,[date_time] datetime,[person] varchar(4),[do_action] varchar(6))
insert [tab_ActionList]
select 1,'2009-6-22','小张','看电影' union all
select 2,'2009-6-22','小张','玩游戏' union all
select 3,'2009-6-29','小张','玩游戏' union all
select 4,'2009-6-1','小王','看电影'
--------------开始查询--------------------------select person ,
sum(case do_action when '看电影' then 1 else 0 end) as 看电影次数,
sum(case do_action when '玩游戏' then 1 else 0 end) as 玩游戏次数
from tab_ActionList where datename(mm,[date_time])=6
group by person
----------------结果----------------------------
/*
person 看电影次数 玩游戏次数
------ ----------- -----------
小王 1 0
小张 1 2(所影响的行数为 2 行)*/
-- Author: Beirut(贝鲁特)
-- Comment:小爱
-- Date : 2009-08-04 10:02:49
---------------------------------if object_id('tb') is not null drop table tb
go
create table tb([Id] int,[date_time] datetime,[person] varchar(4),[do_action] varchar(6))
go
insert tb
select 1,'2009-6-22','小张','看电影' union all
select 2,'2009-6-22','小张','玩游戏' union all
select 3,'2009-6-29','小张','玩游戏' union all
select 4,'2009-6-1','小王','看电影'SELECT [person],
SUM(CASE [do_action] WHEN '看电影' THEN 1 ELSE 0 END) AS '看电影',
SUM(CASE [do_action] WHEN '玩游戏' THEN 1 ELSE 0 END) AS '玩游戏'
FROM tb
GROUP BY [person]
--[do_action] 不止看电影和玩游戏两种使用动态SQL
--SQL 2000
DECLARE @SQL VARCHAR(4000)
SET @SQL='SELECT [person]'
SELECT @SQL=@SQL+',SUM(CASE [do_action] WHEN '''+ [do_action] +''' THEN 1 ELSE 0 END ) AS ['+do_action+']'
FROM (SELECT DISTINCT [do_action] FROM tb) AS a
SET @SQL=@SQL+'FROM tb GROUP BY [person] '
EXEC(@SQL)
/*person 看电影 玩游戏
------ ----------- -----------
小王 1 0
小张 1 2(2 行受影响)*/