mysql 来自多个列的嵌套计数

j0pj023g 于 2022-11-21 发布在 Mysql

关注(0)|答案(1)|浏览(113)

我有以下两张表。
员工

EMPID License  Experience  Salary
----  ------  ----------  ------
ABC1  3256     5 years     $1000
ABC2  1324     10 years    $3000
ABC3  2345     11 years    $2500

机器_控制

MID   MCODE OPRID   SMID   SMCODE  MLOCATION
-------------------------------------------
M1     1     ABC1      NULL    NULL   LOCATION1
M1     2     ABC2      NULL    NULL   LOCATION2
M1     3     NULL      M1      1      LOCATION1
M1     4     ABC1      NULL    NULL   LOCATION3
M1     5     NULL      M1      2      LOCATION2

查找以下输出。请注意，对于给定EMPID的no_machines和No_locations，应同时计算主索引键（MID，MCODE）和辅助索引键（SMID，SMCODE）。MID和MCODE是表的主键。

EMPID  License Experience No_Machines  No_Locations
--------------------------------------------------
ABC1    3256      5 years     3              2
ABC2    1324      10 years    2              1
ABC3    2345      11 years    0              0

我尝试了以下方法

select a.EMPID, a.Licesne, a.Experience, count(b.MID) No_Machines, count(distinct b.MLOCATION) No_Locations
from Employee a
    left join Machine_Control b on a.EMPID= b.OPRDID
group by a.EMPID;

我得到以下结果

EMPID  License Experience No_Machines  No_Locations
--------------------------------------------------
ABC1    3256      5 years     2              2
ABC2    1324      10 years    1              1
ABC3    2345      11 years    0              0

mysql

来源：https://stackoverflow.com/questions/74506009/nested-counts-from-multiple-columns

1条答案

按热度按时间

xxhby3vn1#

使用UNION ALL组合获取SMID和SMCODE的结果。

SELECT
  a.EMPID, a.License, a.Experience,
  COALESCE(b.No_Machines, 0) No_Machines,
  COALESCE(b.No_Locations, 0) No_Locations
FROM Employee a
  LEFT JOIN (
    SELECT OPRID,
      COUNT(MID) No_Machines,
      COUNT(distinct MLOCATION) No_Locations
    FROM (
      SELECT MID, MCODE, OPRID, MLOCATION
        FROM Machine_Control
      UNION ALL
      SELECT s.SMID, s.SMCODE, p.OPRID, p.MLOCATION
        FROM Machine_Control s JOIN Machine_Control p
        ON s.SMID=p.MID AND s.SMCODE=p.MCODE
      ) ps
    GROUP BY OPRID) b
  ON a.EMPID= b.OPRID

DB Fiddle

赞(0）回复(0）举报 2022-11-21

我来回答

mysql 来自多个列的嵌套计数

1条答案

相关问题

热门标签

最新问答