从两个相关表中获取数据的SQL查询

Question

我有两个表，员工和库存。一个员工可以拥有零个或多个库存。 我想要列出员工信息以及最多一个库存信息和属于一个员工的库存数量。

员工表

emp_num  last_name  first_name
-----------------------------------
100      john       smith

101      mike       pet

102      jes        lyoid

库存表

inv_num  emp_num
---------------------------
12       100

13       100

15       100

30       102

期望的输出结果

emp_num     last_name       invnum  count(inv_num)
-------------------------------------------------------------------------- 
100         john            12      3

101         mike            -       0

102         jes             30      1

在这种情况下，我可以使用什么SQL查询？

- user857293

Answer 1

试试这个：

SELECT emp_num, last_name, MAX(inv_num) AS invnum, COUNT(inv_num) AS inv_count
FROM employee e LEFT OUTER JOIN inventory i ON e.emp_num = i.emp_num
GROUP BY e.emp_num, e.last_name

- Aziz Shaikh

Answer 2

你可以像这样做

Select E.Emp_Num,
       e.Last_name,
       MIN(Inv_Num) AS OldestInv,
       COUNT(Inv_Num) AS TotalInv
FROM Employee E
    LEFT OUTER JOIN Inventory I
        (E.Emp_Num = I.Emp_Num)
GROUP BY E.Emp_Num, E.Last_Name

这将为您提供最小发票号码和总计数。左外连接是关键。

- Mitchel Sellers

Answer 3

SELECT 
    e.emp_num,
    e.last_name,
    IFNULL(MAX(i.inv_num),'-') AS 'invnum',
    COUNT(i.inv_num) AS 'count(inv_num)'
FROM
    employee e LEFT JOIN inventory i
    ON e.emp_num = i.emp_num
GROUP BY
    e.emp_num, e.last_name

- Talha Ahmed Khan