多表统计查询请求：求生成指定设备库存统计结果的SQL语句

1. Table_inventory（库存表） #

2. Table_device_type（设备类型映射表） #

3. Table_location（位置映射表） #

4. Table_status（状态映射表） #

第一类：按位置统计总库存及各设备类型数量 #

期望结果 #

No Location    Total inventory   PC    Laptop     Smartphone    Telephone
1  Store       3                 1     0          1            1
2  Sales       3                 0     2          1            0
3  Workshop    3                 2     0          0            1    

执行SQL #

SELECT
    ROW_NUMBER() OVER(ORDER BY loc.id) AS No,
    loc.device_type AS Location,
    COUNT(inv.id) AS "Total inventory",
    SUM(CASE WHEN inv.device_type = 1 THEN 1 ELSE 0 END) AS PC,
    SUM(CASE WHEN inv.device_type = 2 THEN 1 ELSE 0 END) AS Laptop,
    SUM(CASE WHEN inv.device_type = 3 THEN 1 ELSE 0 END) AS Smartphone,
    SUM(CASE WHEN inv.device_type = 4 THEN 1 ELSE 0 END) AS Telephone
FROM Table_location loc
LEFT JOIN Table_inventory inv ON loc.id = inv.location
GROUP BY loc.id, loc.device_type
ORDER BY loc.id;

第二类：按位置统计PC设备的各状态数量 #

期望结果 #

No Location    PC    Broken    In Use     Disposed
1  Store       1     0          1            0
2  Sales       0     0          0            0
3  Workshop    2     1          1            0

执行SQL #

SELECT
    ROW_NUMBER() OVER(ORDER BY loc.id) AS No,
    loc.device_type AS Location,
    COUNT(inv.id) AS PC,
    SUM(CASE WHEN inv.status = 1 THEN 1 ELSE 0 END) AS Broken,
    SUM(CASE WHEN inv.status = 2 THEN 1 ELSE 0 END) AS "In Use",
    SUM(CASE WHEN inv.status = 3 THEN 1 ELSE 0 END) AS Disposed
FROM Table_location loc
LEFT JOIN Table_inventory inv ON loc.id = inv.location AND inv.device_type = 1
GROUP BY loc.id, loc.device_type
ORDER BY loc.id;

内容的提问来源于stack exchange，提问作者kuraba