SELECT id,姓名,到岗天数,count(订单id) as 订单数量
FROM TEST1
LEFT JOIN (
SELECT id,count(*) AS 到岗天数 FROM (
SELECT id,concat(年,月,日) AS 到岗日期 FROM TEST1)
WHERE 到岗日期 BETWEEN '2018-8-1' AND '2018-8-31'
GROUP BY id) AS a ON TEST1.id=a.id
GROUP BY TEST1.id
HAVING 订单数量>100;
select
a.`司机id`,
a.`司机名称`,
count(distinct a.`日`) as `到岗天数`,
count(distinct a.`订单id`) as `订单完成数`
from 表名 a
where a.`年`='2018'
and a.`月`='8'
group by
a.`司机id`,
a.`司机名称`having count(distinct a.`日`) >25
SELECT
e.id,
e.name,
a.attendance_days AS 到岗天数,
o.order_count AS 订单完成数
FROM employee e
-- 步骤1：计算员工在2018年8月的到岗天数
INNER JOIN (
SELECT
employee_id,
COUNT(DISTINCT date) AS attendance_days
FROM attendance
WHERE date BETWEEN '2018-08-01' AND '2018-08-31'
GROUP BY employee_id
HAVING COUNT(DISTINCT date) > 25
) a ON e.id = a.employee_id
-- 步骤2：计算员工在2018年8月完成的订单数
INNER JOIN (
SELECT
employee_id,
COUNT(*) AS order_count
FROM orders
WHERE order_date BETWEEN '2018-08-01' AND '2018-08-31'
AND status = 'completed'
GROUP BY employee_id
HAVING COUNT(*) > 100
) o ON e.id = o.employee_id;
扫码加好友，拉您进群
全部版块
我的主页
收藏
