时间戳

首页 > 解决方案 > 左连接后表中的重复记录

sql - 左连接后表中的重复记录

问题描述

我有三张桌子。

“部门”表：department_name、department_id。
部门表

表“LowPrice”：departmend_id、product_name、购买、出售、价格。
低价表

表“基本”：departmend_id、product_name、购买、出售、价格。
基本表

在“基本”和“低”表中，可以有几条属于一个部门的记录。

这些表之间没有关系。我想开发一个查询，该查询将按 Departments 表中的部门编号对 Low 和 Basic 表中的记录进行分组。分组时，汇总每个部门的“基本”和“低价”表中的已售商品和已购商品的成本。

我的查询代码：

    SELECT Departments.departmend_id, 
    Sum([Basic]![purchased] [Basic]![price]) AS PurchasedBasic 
    Sum([Basic]![sold] [Basic]![price]) AS SoldBasic, 
    Sum([Basic]![purchased] [Basic]![price]) - Sum([Basic]![sold][Basic]![price]) AS DiffBasic, 
    Sum([LowPrice]![purchased] [LowPrice]![price]) AS PurchasedLowPrice, 
    Sum([LowPrice]![sold] [LowPrice]![price]) AS SoldLowPrice, 
    Sum([LowPrice]![purchased] [LowPrice]![price]) - Sum([LowPrice]![sold] [LowPrice]![price]) AS DiffLowPrice
    FROM (Departments LEFT JOIN Basic ON Departments.departmend_id = Basic.departmend_id) LEFT JOIN LowPrice ON Departments.departmend_id = LowPrice.departmend_id
    GROUPBY Departments.departmend_id;

但是现在只有那些只有一张表有记录的部门才算正确。如果两个表中有一个部门的记录，则它们的数量会相乘，查询会给出不正确的结果。

结果我有

预期结果

请启发我在查询中需要更改的内容。

标签： sqlms-access

解决方案

考虑将LowPrice和BasicPrice合并到一个表中，因为它们在质量上包含相同的信息。只需添加一个类型字段来指定低价和基本价格记录。

生成表查询 （运行一次）

SELECT p.*
INTO Prices
FROM
   (SELECT b.department_id,
           b.product_name,
           b.purchased,
           b.sold, 
           b.price,
           'Basic Price' AS type
    FROM BasicPrice b

    UNION ALL

    SELECT l.department_id,
           l.product_name,
           l.purchased,
           l.sold, 
           l.price,
           'Low Price' AS type
    FROM LowPrice l
   ) p

一旦你正确规范了这两个关系以获得更有效的存储，运行条件​​聚合。下面使用 MS Access 支持的表别名和计算列。

条件聚合查询

SELECT d.departmend_id, 
       d.department_name,
       SUM(IIF(p.Type = 'Basic Price', p.[purchased] * p.[price], NULL)) AS PurchasedBasic,
       SUM(IIF(p.Type = 'Basic Price', p.[sold] * p.[price], NULL)) AS SoldBasic, 
       [PurchasedBasic] - [SoldBasic] AS DiffBasic, 

       SUM(IIF(p.Type = 'Low Price', p.[purchased] * p.[price], NULL)) AS PurchasedLowPrice, 
       SUM(IIF(p.Type = 'Low Price', p.[sold] * p.[price], NULL)) AS SoldLowPrice, 
       [PurchasedLowPrice] - [SoldLowPrice] AS DiffLowPrice

FROM Departments d
LEFT JOIN Prices p ON d.departmend_id = p.departmend_id

GROUP BY d.departmend_id,
         d.department_name;

推荐阅读