我正在尝试让员工在 3 个站点执行扫描并最终计数。 我有两个表:
"员工"选项卡
user_id|first_name|last_name
"统计信息"选项卡
id|user_id|station|product_id
我想得到这个效果:
first_name|last_name|station1|station2|station3
_______________________________________________
John |Doe |2 |0 |5
名字和姓氏可以合并到一个单元格中
我有这样一个怪物,但只为一个员工:(
SELECT
(SELECT COUNT(m1.id) FROM stats m1 WHERE m1.user_id= :user_id AND m1.station=1) AS station1,
(SELECT COUNT(m1.id) FROM stats m1 WHERE m1.user_id= :user_id AND m1.station=2) AS station2,
(SELECT COUNT(m1.id) FROM stats m1 WHERE m1.user_id= :user_id AND m1.station=3) AS station3
FROM stats m LIMIT 1
而不是相关的子查询(在当前的方法中(,您可以使用用户GROUP BY
的条件聚合,并适当地连接表。它的性能也会更高:
SELECT
e.first_name,
e.last_name,
COUNT(CASE WHEN m.station = 1 THEN 1 ELSE NULL END) AS station1,
COUNT(CASE WHEN m.station = 2 THEN 1 ELSE NULL END) AS station2,
COUNT(CASE WHEN m.station = 3 THEN 1 ELSE NULL END) AS station3
FROM stats m
JOIN employees e ON e.user_id = m.user_id
GROUP BY e.first_name, e.last_name
上面的查询将为您提供所有员工(用户(的结果。如果您只想获得特定用户的结果,则只需在其中使用WHERE
子句:
SELECT
e.first_name,
e.last_name,
COUNT(CASE WHEN m.station = 1 THEN 1 ELSE NULL END) AS station1,
COUNT(CASE WHEN m.station = 2 THEN 1 ELSE NULL END) AS station2,
COUNT(CASE WHEN m.station = 3 THEN 1 ELSE NULL END) AS station3
FROM stats m
JOIN employees e ON e.user_id = m.user_id
WHERE e.user_id = :userid
GROUP BY e.first_name, e.last_name
您可以尝试使用条件聚合
SELECT first_name, last_name,
count(case when station=1 then id) as station1,
count(case when station=2 then id) as station2,
count(case when station=3 then id) as station3
from stats inner join employees on employees.user_id=stats.user_id
group by first_name, last_name