Mysql 按多个表分组计数

Question

嗨，我正在寻找 Mysql 查询来显示两个表中的组计数

表 1 = 植物

P_id    Plant location  Employee
1         DUBAI         Employee A
2         SHARJAH       Employee A
3         OMAN          Employee B
4         DAMMAM        Employee C
5         RIYADH        Employee C

表 2 = 访问次数

V_id    Visit status    Plant location  Employee
1        Done            DUBAI           Employee A
2        Done            SHARJAH         Employee A
3        Done            OMAN            Employee B
4        Done            RIYADH          Employee C

我需要结果为

Employee    Visit Completed Remaining Visits    Result
Employee A       2             0                100 %
Employee B       1             0                100 %
Employee C       1             1                50%

SELECT employee_name,COUNT(v_id)
FROM visit      
GROUP BY employee_name; 

我使用了上面的代码。它仅显示访问计数。但我如何才能得到上面的结果表

Answer 1

有了这个声明

SELECT
   Employee,
   count_plant `Visit Completed` 
   ,(count_plant -count_visits)`Remaining Visits`
   , CONCAT(ROUND(((count_visits / count_plant) * 100),1), " %") Result
FROM
  (SELECT 
    p.Employee,
   COUNT(p.`Plant location`) count_plant
   ,COUNT(v.`Plant location`) count_visits
  FROM
    (SELECT
     * FROM plant) p
    LEFT join
      (SELECT *
       FROM visits 
       WHERE `Visit status` =  'Done' ) v
       ON p.Employee = v.Employee 
          and p.`Plant location` = v.`Plant location`
 GROUP BY p.Employee) p_v;

给出以下结果

Employee    Visit Completed     Remaining Visits    Result
Employee A  2                   0                   100.0 %
Employee B  1                   0                   100.0 %
Employee C  2                   1                   50.0 %

结果列在逗号后四舍五入到 i 位。您应该看看这是否是您所需要的。