当前位置:首页 » 编程语言 » sqlcase子查询

sqlcase子查询

发布时间: 2023-02-24 23:39:44

㈠ SQL子查询统计

select
count(distinct(b.visit_id))'visit_id次数',

count(case when typeid=14 then 1 else 0)'14出现次数',
count(case when typeid=65 then 1 else 0)'65出现次数' from a
inner join b on a.visit_id=b.visitId

㈡ SQL子查询

select 用户名 ,所处大区, 所处省区,
sum(onty_cnt) as onty_cnt,sum(offty_cnt) as offty_cnt,
sum(overtime) as overtime
from
(select 用户名 ,所处大区, 所处省区, onty_time ,offty_time, 打卡日期,overtime,
case when to_char(onty_time,'hh24:mi') > '09:00' then 1 else 0 end as onty_cnt,
case when to_char(offty_time,'hh24:mi') < '17:20' then 1 else 0 end as offty_cnt
where 省区 = 参数
and 打卡日期 between '2009-01-01' and '2009-01-31' )
group by 用户名 ,所处大区, 所处省区

-------------------------------------
这是按人统计, 如果不要每个人的, 只要全部, 那就:

select 所处大区, 所处省区,
sum(onty_cnt) as onty_cnt,sum(offty_cnt) as offty_cnt,
sum(overtime) as overtime
from
(select 用户名 ,所处大区, 所处省区, onty_time ,offty_time, 打卡日期,overtime,
case when to_char(onty_time,'hh24:mi') > '09:00' then 1 else 0 end as onty_cnt,
case when to_char(offty_time,'hh24:mi') < '17:20' then 1 else 0 end as offty_cnt
where 省区 = 参数
and 打卡日期 between '2009-01-01' and '2009-01-31' )
group by 所处大区, 所处省区

-----------------------------
就是从外面的查询中把用户名去掉!
不明白HI我

热点内容
数据库或且 发布:2025-07-01 21:40:54 浏览:996
pythoneve 发布:2025-07-01 21:33:49 浏览:141
解压玩具怎么做只用水就可以做 发布:2025-07-01 20:52:56 浏览:160
解压放松室 发布:2025-07-01 20:52:43 浏览:124
存储器坏了 发布:2025-07-01 20:46:11 浏览:511
iphone怎么设锁屏密码 发布:2025-07-01 20:46:10 浏览:621
服务器如何处理千万请求 发布:2025-07-01 20:30:53 浏览:334
plsqldeveloperjob 发布:2025-07-01 20:29:59 浏览:805
剪切文件用什么配置 发布:2025-07-01 20:23:07 浏览:44
酷睿2020主要看哪些配置 发布:2025-07-01 20:23:06 浏览:261