在SQL中联接多个表后如何计数记录

debugcn 发表于 Dev

柯比·希波纳

我需要使用内部联接根据员工表中的ecode计算计划，顺序和存储中的记录数。当我使用TestQuery时，查询计数非常理想，但是当我在MainQuery1中使用内部联接合并查询时，我得到了错误的结果（来自以下结果）。我发现测试查询的3结果彼此不匹配，也不接受0。请帮我解决这个问题。提前致谢。

我需要这样的结果：

     ecode    schedulecount noordercount    salescount
      BUL-SJU-01    135    7              135
      BUL-PLA-05    1          0               0
      BUL-PLA-03    121        0              120
      BUL-MAL-03    128        3              128
      BUL-CAL-01    48         10             46
      BUL-BOC-01    111        11             111

MainQuery1：这是我的问题

SELECT 
emp.[ecode],
COUNT(sch.[user_id]) as schedulecount,
COUNT(ord.[ecode]) as noordercount,
COUNT(sto.[ecode]) as salescount
FROM [mydb].[dbo].[employee] as emp
INNER JOIN [mydb].[dbo].[schedule] as sch 
ON  sch.[user_id] = emp.[ecode]
INNER JOIN [mydb].[dbo].[order] as ord
ON  ord.[ecode] = emp.[ecode]
INNER JOIN [mydb].[dbo].[store] as sto
ON  sto.[ecode] = emp.[ecode]
 GROUP BY 
 emp.[ecode]
 ORDER BY
 emp.[ecode] DESC;

错误的结果：

      ecode   schedulecount noordercount    salescount
      BUL-SJU-01    127575     127575    127575
      BUL-MAL-03    49152      49152     49152
      BUL-CAL-01    22080      22080     22080
      BUL-BOC-01    135531     135531    135531

TestQuery1;

   SELECT 
emp.[ecode],
COUNT(sch.[user_id]) as schedulecount
FROM [mydb].[dbo].[employee] as emp
INNER JOIN [mydb].[dbo].[schedule] as sch 
ON  sch.[user_id] = emp.[ecode]
GROUP BY 
 emp.[ecode]
ORDER BY
 emp.[ecode] DESC;

结果：

      ecode          schedulecount
     BUL-SJU-01       135
     BUL-PLA-05       1
     BUL-PLA-03       121
     BUL-MAL-03       128
     BUL-CAL-01       48
     BUL-BOC-01       111

TestQuery2;

  SELECT 
   emp.[ecode],
COUNT(ord.[ecode]) as schedulecount
FROM [mydb].[dbo].[employee] as emp
INNER JOIN [mydb].[dbo].[order] as ord
    ON  ord.[ecode] = emp.[ecode]
GROUP BY 
   emp.[ecode]
ORDER BY
  emp.[ecode] DESC;

结果：

    ecode     schedulecount
    BUL-SJU-01      7
    BUL-MAL-03      3
    BUL-CAL-01      10
    BUL-BOC-01      11

TestQuery3;

SELECT 
emp.[ecode],
COUNT(sto.[ecode]) as salescount
FROM [mydb].[dbo].[employee] as emp
INNER JOIN [Z_ALI].[dbo].[M_STORE] as sto
ON  sto.[ecode] = emp.[ecode]
GROUP BY 
emp.[ecode]
ORDER BY
emp.[ecode] DESC;

结果：

       ecode    salescount
       BUL-SJU-01   135
       BUL-PLA-03   120
       BUL-MAL-03   128
       BUL-CAL-01   46
       BUL-BOC-01   111

拉努

另一种方法是使用APPLY在FROM获得数：

USE mydb;


SELECT emp.ecode,
       Sc.schedulecount,
       O.noordercount,
       St.salescount
FROM dbo.employee emp
     CROSS APPLY (SELECT COUNT(*) AS schedulecount
                  FROM dbo.schedule sch
                  WHERE sch.user_id = emp.ecode) Sc
     CROSS APPLY (SELECT COUNT(*) AS noordercount
                  FROM dbo.[order] ord --Generally it's a good idea to avoid Reserved Keywords for Object names
                  WHERE ord.ecode = emp.ecode) O
     CROSS APPLY (SELECT COUNT(*) AS salescount
                  FROM dbo.store sto
                  WHERE sto.ecode = emp.ecode) St
ORDER BY emp.ecode DESC;

db <> fiddle显示每个问题的结果正确。

水晶球：

SELECT emp.ecode,
       Sc.schedulecount,
       O.noordercount,
       St.salescount
FROM dbo.employee emp
     CROSS APPLY (SELECT COUNT(*) AS schedulecount
                  FROM dbo.schedule sch
                  WHERE sch.user_id = emp.ecode) Sc
     CROSS APPLY (SELECT COUNT(*) AS noordercount
                  FROM dbo.[order] ord --Generally it's a good idea to avoid Reserved Keywords for Object names
                  WHERE ord.ecode = emp.ecode) O
     CROSS APPLY (SELECT COUNT(*) AS salescount
                  FROM dbo.store sto
                  WHERE sto.ecode = emp.ecode) St
WHERE Sc.schedulecount > 0  
   OR O.noordercount > 0
   OR St.salescount > 0
ORDER BY emp.ecode DESC;

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。