原始表中有时间列(dataTime)、名字列(nvchar)、是否本地(bit/bool) 这三个列。
我想通过一条sql语句,统计出 “每个名字、每一天 的本地数量 和非本地数量。”
这样说您可能有些糊涂。举个例子,就像如下两个表,我的目标是将原始数据表中的信息统计为目标表那个样子。原始数据表
时间 名字 是否本地
今天上午 张三 true
今天下午 张三 false
昨天上午 张三 true
昨天下午 张三 true
前天上午 张三 false
前天中午 张三 true
前天下午 张三 false
今天上午 李四 true
今天中午 李四 false
今天下午 李四 false
昨天上午 李四 false
昨天中午 李四 true
昨天下午 李四 true
前天上午 李四 false
前天中午 李四 true
前天下午 李四 false
=============>
目标数据
时间 名字 本地数量 非本地数量 咨询总数
今天 张三 1 1 2
昨天 张三 2 0 2
前天 张三 1 2 3
今天 李四 1 2 3
昨天 李四 2 1 3
前天 李四 1 2 3
注1:“时间”列为标准的datetime类型的列(日月年时分秒),写成昨天今天明天是方便大家理解
注2:“是否本地”列是bit类型,也就是程序中的bool类型。
注3:目标表统计的时间顺序为倒叙(就像上述目标表那个样子->从上到下是今天、昨天、前天。切勿反过来,因为用户更希望先看到最新日期的)。
我想通过一条sql语句,统计出 “每个名字、每一天 的本地数量 和非本地数量。”
这样说您可能有些糊涂。举个例子,就像如下两个表,我的目标是将原始数据表中的信息统计为目标表那个样子。原始数据表
时间 名字 是否本地
今天上午 张三 true
今天下午 张三 false
昨天上午 张三 true
昨天下午 张三 true
前天上午 张三 false
前天中午 张三 true
前天下午 张三 false
今天上午 李四 true
今天中午 李四 false
今天下午 李四 false
昨天上午 李四 false
昨天中午 李四 true
昨天下午 李四 true
前天上午 李四 false
前天中午 李四 true
前天下午 李四 false
=============>
目标数据
时间 名字 本地数量 非本地数量 咨询总数
今天 张三 1 1 2
昨天 张三 2 0 2
前天 张三 1 2 3
今天 李四 1 2 3
昨天 李四 2 1 3
前天 李四 1 2 3
注1:“时间”列为标准的datetime类型的列(日月年时分秒),写成昨天今天明天是方便大家理解
注2:“是否本地”列是bit类型,也就是程序中的bool类型。
注3:目标表统计的时间顺序为倒叙(就像上述目标表那个样子->从上到下是今天、昨天、前天。切勿反过来,因为用户更希望先看到最新日期的)。
本地数量=sum(case when 是否本地='是' then 1 else 0 end),
非本地数量=sum(case when 是否本地='否' then 1 else 0 end),
咨询总数=count(*)
from tb group by 时间,名字
本地数量=sum(case when 是否本地='是' then 1 else 0 end),
非本地数量=sum(case when 是否本地='否' then 1 else 0 end),
咨询总数=count(*)
from tb group by convert(varchar(10),时间,120),名字
order by 2,1
sum(case when...),
sum(case when ...),
sum(case when ...)
from tb
group by ...
insert into tb select '今天上午','张三','true '
insert into tb select '今天下午','张三','false'
insert into tb select '昨天上午','张三','true'
insert into tb select '昨天下午','张三','true'
insert into tb select '前天上午','张三','false'
insert into tb select '前天中午','张三','true'
insert into tb select '前天下午','张三','false'
insert into tb select '今天上午','李四','true'
insert into tb select '今天中午','李四','false'
insert into tb select '今天下午','李四','false'
insert into tb select '昨天上午','李四','false'
insert into tb select '昨天中午','李四','true'
insert into tb select '昨天下午','李四','true'
insert into tb select '前天上午','李四','false'
insert into tb select '前天中午','李四','true'
insert into tb select '前天下午','李四','false'
go
select left(时间,2)时间,名字,
sum(case when 是否本地='true' then 1 else 0 end)本地数量,
sum(case when 是否本地='true' then 0 else 1 end)非本地数量
from tb group by left(时间,2),名字
/*
时间 名字 本地数量 非本地数量
---- ---------- ----------- -----------
今天 李四 1 2
前天 李四 1 2
昨天 李四 2 1
今天 张三 1 1
前天 张三 1 2
昨天 张三 2 0(6 行受影响)*/
go
drop table tb
insert into tb select '今天上午','张三','true '
insert into tb select '今天下午','张三','false'
insert into tb select '昨天上午','张三','true'
insert into tb select '昨天下午','张三','true'
insert into tb select '前天上午','张三','false'
insert into tb select '前天中午','张三','true'
insert into tb select '前天下午','张三','false'
insert into tb select '今天上午','李四','true'
insert into tb select '今天中午','李四','false'
insert into tb select '今天下午','李四','false'
insert into tb select '昨天上午','李四','false'
insert into tb select '昨天中午','李四','true'
insert into tb select '昨天下午','李四','true'
insert into tb select '前天上午','李四','false'
insert into tb select '前天中午','李四','true'
insert into tb select '前天下午','李四','false'
go
select left(时间,2)时间,名字,
sum(case when 是否本地='true' then 1 else 0 end)本地数量,
sum(case when 是否本地='true' then 0 else 1 end)非本地数量,
count(*)咨询总数
from tb group by left(时间,2),名字
/*
时间 名字 本地数量 非本地数量 咨询总数
---- ---------- ----------- ----------- -----------
今天 李四 1 2 3
前天 李四 1 2 3
昨天 李四 2 1 3
今天 张三 1 1 2
前天 张三 1 2 3
昨天 张三 2 0 2(6 行受影响)*/
go
drop table tb
convert(varchar(10),时间,120),名字,
sum(case when 是否本地='true' then 1 else 0 end) as 本地数量,
sum(case when 是否本地='false' then 1 else 0 end) as 非本地数量,
count(1) as 咨询总数
from
tb
group by
convert(varchar(10),时间,120),名字
order by
名字,convert(varchar(10),时间,120) desc
insert into tb select '2011-12-07 01:30','张三',1
insert into tb select '2011-12-07 21:30','张三',0
insert into tb select '2011-12-06 01:30','张三',1
insert into tb select '2011-12-06 21:30','张三',1
insert into tb select '2011-12-05 21:30','张三',0
insert into tb select '2011-12-05 21:30','张三',1
insert into tb select '2011-12-05 21:30','张三',0
insert into tb select '2011-12-07 21:30','李四',1
insert into tb select '2011-12-07 21:30','李四',0
insert into tb select '2011-12-06 21:30','李四',0
insert into tb select '2011-12-06 21:30','李四',0
insert into tb select '2011-12-06 21:30','李四',1
insert into tb select '2011-12-05 21:30','李四',1
insert into tb select '2011-12-05 21:30','李四',0
insert into tb select '2011-12-05 21:30','李四',1
insert into tb select '2011-12-05 21:30','李四',0
go
select convert(varchar(10),时间,120)时间,名字,
sum(case when 是否本地=1 then 1 else 0 end)本地数量,
sum(case when 是否本地=1 then 0 else 1 end)非本地数量,
count(*)咨询总数
from tb group by convert(varchar(10),时间,120),名字
order by 名字,1 desc
/*
时间 名字 本地数量 非本地数量 咨询总数
---------- ---------- ----------- ----------- -----------
2011-12-07 李四 1 1 2
2011-12-06 李四 1 2 3
2011-12-05 李四 2 2 4
2011-12-07 张三 1 1 2
2011-12-06 张三 2 0 2
2011-12-05 张三 1 2 3(6 行受影响)*/
go
drop table tb
SELECT
CONVERT(VARCHAR(10),时间列,120) AS 日期,名字,
SUM(是否本地*1) AS 本地数量,
SUM(1-是否本地*1) AS 非本地数量,
COUNT(1) AS 咨询总数
FROM table1
WHERE DATEDIFF(d,时间列,GETDATE())<=2
GROUP BY CONVERT(VARCHAR(10),时间列,120)
ORDER BY 名字,日期 desc
if object_id('tb','U') is not null
drop table tb
go
create table tb
(
时间 varchar(10),
名字 varchar(10),
是否本地 varchar(10)
)
go
insert into tb
select '今天上午','张三','true' union all
select '今天上午','张三','false' union all
select '昨天上午','张三','true' union all
select '昨天下午','张三','true' union all
select '前天上午','张三','false' union all
select '前天中午','张三','true' union all
select '前天下午','张三','false' union all
select '今天上午','李四','true' union all
select '今天中午','李四','false' union all
select '今天下午','李四','false' union all
select '昨天上午','李四','false' union all
select '昨天中午','李四','true' union all
select '昨天下午','李四','true' union all
select '前天上午','李四','false' union all
select '前天中午','李四','true' union all
select '前天下午','李四','false'
go
select substring(时间,1,2),名字,
本地数量=sum(case when 是否本地='true' then 1 else 0 end),
非本地数量=sum(case when 是否本地='false' then 1 else 0 end)
from tb group by substring(时间,1,2),名字
order by 名字,case substring(时间,1,2) when '今天' then 1 when '今天' then 2 when '前天' then 3 end
go
/*
名字 本地数量 非本地数量
---- ---------- ----------- -----------
昨天 李四 2 1
今天 李四 1 2
前天 李四 1 2
昨天 张三 2 0
今天 张三 1 1
前天 张三 1 2(6 行受影响)
*/
--给第一个加个别名
if object_id('tb','U') is not null
drop table tb
go
create table tb
(
时间 varchar(10),
名字 varchar(10),
是否本地 varchar(10)
)
go
insert into tb
select '今天上午','张三','true' union all
select '今天上午','张三','false' union all
select '昨天上午','张三','true' union all
select '昨天下午','张三','true' union all
select '前天上午','张三','false' union all
select '前天中午','张三','true' union all
select '前天下午','张三','false' union all
select '今天上午','李四','true' union all
select '今天中午','李四','false' union all
select '今天下午','李四','false' union all
select '昨天上午','李四','false' union all
select '昨天中午','李四','true' union all
select '昨天下午','李四','true' union all
select '前天上午','李四','false' union all
select '前天中午','李四','true' union all
select '前天下午','李四','false'
go
select 时间=substring(时间,1,2),名字,
本地数量=sum(case when 是否本地='true' then 1 else 0 end),
非本地数量=sum(case when 是否本地='false' then 1 else 0 end)
from tb group by substring(时间,1,2),名字
order by 名字,case substring(时间,1,2) when '今天' then 1 when '今天' then 2 when '前天' then 3 end
go
/*
时间 名字 本地数量 非本地数量
---- ---------- ----------- -----------
昨天 李四 2 1
今天 李四 1 2
前天 李四 1 2
昨天 张三 2 0
今天 张三 1 1
前天 张三 1 2(6 行受影响)
*/