我知道如果没有B表的话,可以写成select Date(createtime) as '日期', sum(CASE WHEN type=3 THEN 1 ELSE 0 END) as '移动', sum(CASE WHEN type=2 THEN 1 ELSE 0 END) as'联通' from datetest group by Date(createtime); 可有第二张表后咋写呢?
select createtime 日期, count(case when type='3' then 1 else 0 end) as 移动条数, count(case when type='10' then 1 else 0 end) as 联通条数, count(case when type not in ('3','10') then 1 else 0 end) as 互动条数 from (select type,id,createtime from a union all select type,id,createtime from b) tmp group by createtime
更正: select convert(varchar(10),createtime,120) 日期, count(case when type='3' then 1 else 0 end) as 移动条数, count(case when type='10' then 1 else 0 end) as 联通条数, count(case when type not in ('3','10') then 1 else 0 end) as 互动条数 from (select type,id,createtime from a union all select type,id,createtime from b) tmp group by convert(varchar(10),createtime,120)
对不起,上面还是错的 select convert(varchar(10),createtime,120) 日期, sum(case when type='3' then 1 else 0 end) as 移动条数, sum(case when type='10' then 1 else 0 end) as 联通条数, sum(case when type not in ('3','10') then 1 else 0 end) as 互动条数 from (select type,id,createtime from a union all select type,id,createtime from b) tmp group by convert(varchar(10),createtime,120)
谢谢你呀这么踊跃帮我解答问题,但不对额 互动在B表 B表中typeId 任何数都是 互动
select convert(varchar(10),createtime,120) 日期, sum(case when type='3' then 1 else 0 end) as 移动条数, sum(case when type='10' then 1 else 0 end) as 联通条数, sum(case when type ='互动' then 1 else 0 end) as 互动条数 from (select type,id,createtime from a union all select '互动' as type,id,createtime from b) tmp group by convert(varchar(10),createtime,120)
select convert(varchar(10),createtime,120) 日期, sum(case when type='3' then 1 else 0 end) as 移动条数, sum(case when type='10' then 1 else 0 end) as 联通条数, sum(case when type not in ('3','10') then 1 else 0 end) as 互动条数 from (select type,id,createtime from a union all select '互动' as type,id,createtime from b) tmp group by convert(varchar(10),createtime,120)
select createtime,case type when 3 then cn end 移动条数,case type when 10 then cn end 联通条数,case type when 0 then cn end 互动条数 from ( select convert(varchar(10),createtime,120) createtime,type,count(*) cn from a union all select convert(varchar(10),createtime,120) createtime,0,count(*) cn from b ) a group by createtime
select CONVERT(varchar(10),createtime,120) as '日期', sum(CASE WHEN type=3 AND code='a' THEN 1 ELSE 0 END) as '移动条数', sum(CASE WHEN type=10 AND code='a' THEN 1 ELSE 0 END) as'联通条数', sum(CASE WHEN code='b' THEN 1 ELSE 0 END) as'互动条数' from (SELECT 'a' AS code,* FROM A union all SELECT 'b',* FROM B) C group by CONVERT(varchar(10),createtime,120)
sum(CASE WHEN type=3 THEN 1 ELSE 0 END) as '移动',
sum(CASE WHEN type=2 THEN 1 ELSE 0 END) as'联通'
from datetest
group by Date(createtime);
可有第二张表后咋写呢?
count(case when type='3' then 1 else 0 end) as 移动条数,
count(case when type='10' then 1 else 0 end) as 联通条数,
count(case when type not in ('3','10') then 1 else 0 end) as 互动条数
from
(select type,id,createtime from a
union all
select type,id,createtime from b) tmp
group by createtime
select convert(varchar(10),createtime,120) 日期,
count(case when type='3' then 1 else 0 end) as 移动条数,
count(case when type='10' then 1 else 0 end) as 联通条数,
count(case when type not in ('3','10') then 1 else 0 end) as 互动条数
from
(select type,id,createtime from a
union all
select type,id,createtime from b) tmp
group by convert(varchar(10),createtime,120)
select convert(varchar(10),createtime,120) 日期,
sum(case when type='3' then 1 else 0 end) as 移动条数,
sum(case when type='10' then 1 else 0 end) as 联通条数,
sum(case when type not in ('3','10') then 1 else 0 end) as 互动条数
from
(select type,id,createtime from a
union all
select type,id,createtime from b) tmp
group by convert(varchar(10),createtime,120)
互动在B表 B表中typeId 任何数都是 互动
sum(case when type='3' then 1 else 0 end) as 移动条数,
sum(case when type='10' then 1 else 0 end) as 联通条数,
sum(case when type ='互动' then 1 else 0 end) as 互动条数
from
(select type,id,createtime from a
union all
select '互动' as type,id,createtime from b) tmp
group by convert(varchar(10),createtime,120)
sum(case when type='3' then 1 else 0 end) as 移动条数,
sum(case when type='10' then 1 else 0 end) as 联通条数,
sum(case when type not in ('3','10') then 1 else 0 end) as 互动条数
from
(select type,id,createtime from a
union all
select '互动' as type,id,createtime from b) tmp
group by convert(varchar(10),createtime,120)
from
(
select convert(varchar(10),createtime,120) createtime,type,count(*) cn from a
union all
select convert(varchar(10),createtime,120) createtime,0,count(*) cn from b
) a group by createtime
sum(CASE WHEN type=3 AND code='a' THEN 1 ELSE 0 END) as '移动条数',
sum(CASE WHEN type=10 AND code='a' THEN 1 ELSE 0 END) as'联通条数',
sum(CASE WHEN code='b' THEN 1 ELSE 0 END) as'互动条数'
from
(SELECT 'a' AS code,* FROM A
union all
SELECT 'b',* FROM B) C
group by CONVERT(varchar(10),createtime,120)