副本在联盟中

Question

我对SQL很陌生，所以这可能是我的查询出现问题的原因，但我的问题是，我运行了下面的查询，最后在我的表上出现了许多重复的查询。

这两个表都有一个具有相同库存号的项目列表，这就是我加入股票编号的原因。问题是，我的列表中有重复，相同的库存号码，但一行有周转率和租用天数，另一行有来自第二选择减去营业额和雇用天数的其余信息。

任何帮助都将不胜感激！

(SELECT NULL                           AS 'ITEM_ID',
        stock_items.stock_number       AS 'STOCK NUMBER',
        SUM(stock_assign.turnover)     AS 'TURNOVER',
        SUM(stock_assign.days_on_hire) AS 'DAYS ON HIRE',
        NULL                           AS ' CAT',
        NULL                           AS 'ITEM NAME',
        NULL                           AS 'DEPOT LOC',
        NULL                           AS 'DISPOSAL DATE',
        NULL                           AS 'DISPOSAL TYPE',
        NULL                           AS 'DATE CREATED',
        NULL                           AS 'CREATED BY',
        NULL                           AS 'COST PRICE'
 FROM   stock_assign
        left join stock_items
               ON stock_assign.stock_number = stock_items.stock_number
        left join depots
               ON depot_id = depots.dpt_id
 GROUP  BY stock_items.stock_number,
           depots.dpt_name)
UNION
(SELECT stock_items.item_id         AS 'ITEM_ID',
        stock_items.stock_number    AS 'STOCK NUMBER',
        NULL                        AS 'TURNOVER',
        NULL                        AS 'DAYS ON HIRE',
        stock_items.equipment_class AS ' CAT',
        stock_items.name            AS 'ITEM NAME',
        depots.dpt_name             AS 'DEPOT LOC',
        stock_items.disposal_date   AS 'DISPOSAL DATE',
        stock_items.disposal_type   AS 'DISPOSAL TYPE',
        stock_items.date_available  AS 'DATE CREATED',
        users.usr_name              AS 'CREATED BY',
        fixed_assets.cost_amount    AS 'COST PRICE'
 FROM   stock_items
        left join users
               ON users.usr_id = stock_items.userid_created
        left join depots
               ON stock_items.depot_id_located_at = depots.dpt_id
        left join fixed_assets
               ON stock_items.item_id = fixed_assets.stock_item_id)
ORDER  BY 'STOCK NUMBER' ASC  

Answer 1

UNION的行为是在联合数据集中的每一列匹配时返回唯一的行。

您有两个选择，由于为许多列分配的空值，它们显然总是会给出不同的结果。

如果您将其重写为两个数据集的JOIN，则可能会得到所需的结果。

假设您在每个cte上都有一个行，下面是一个示例；

WITH cte_Assignment AS
(
    SELECT
        stock_items.stock_number       AS 'STOCK NUMBER',
        SUM(stock_assign.turnover)     AS 'TURNOVER',
        SUM(stock_assign.days_on_hire) AS 'DAYS ON HIRE'
    FROM stock_assign
    LEFT JOIN stock_items
        ON stock_assign.stock_number = stock_items.stock_number
    LEFT JOIN depots
        ON depot_id = depots.dpt_id
    GROUP BY
        stock_items.stock_number
)

, cte_Item AS
(
    SELECT
        stock_items.item_id         AS 'ITEM_ID',
        stock_items.stock_number    AS 'STOCK NUMBER',
        stock_items.equipment_class AS ' CAT',
        stock_items.name            AS 'ITEM NAME',
        depots.dpt_name             AS 'DEPOT LOC',
        stock_items.disposal_date   AS 'DISPOSAL DATE',
        stock_items.disposal_type   AS 'DISPOSAL TYPE',
        stock_items.date_available  AS 'DATE CREATED',
        users.usr_name              AS 'CREATED BY',
        fixed_assets.cost_amount    AS 'COST PRICE'
    FROM stock_items
    LEFT JOIN users
        ON users.usr_id = stock_items.userid_created
    LEFT JOIN depots
        ON stock_items.depot_id_located_at = depots.dpt_id
    LEFT JOIN fixed_assets
        ON stock_items.item_id = fixed_assets.stock_item_id
)

SELECT
    item.[ITEM_ID],
    item.[STOCK NUMBER],
    assignment.[TURNOVER],
    assignment.[DAYS ON HIRE],
    item.[ CAT],
    item.[ITEM NAME],
    item.[DEPOT LOC],
    item.[DISPOSAL DATE],
    item.[DISPOSAL TYPE],
    item.[DATE CREATED],
    item.[CREATED BY],
    item.[COST PRICE]
FROM cte_Assignment assignment
INNER JOIN cte_Item item
    on item.[STOCK NUMBER] = assignment.[STOCK NUMBER]

ORDER  BY item.[STOCK NUMBER] ASC 

Answer 2

另一个选项是将现在的查询包装为子查询。

SELECT MAX(ITEM_ID) as ITEM_ID, SUM(TURNOVER) 
FROM 
(SELECT NULL                           AS 'ITEM_ID',
    stock_items.stock_number       AS 'STOCK NUMBER',
    SUM(stock_assign.turnover)     AS 'TURNOVER',
    SUM(stock_assign.days_on_hire) AS 'DAYS ON HIRE',
    NULL                           AS ' CAT',
    NULL                           AS 'ITEM NAME',
    NULL                           AS 'DEPOT LOC',
    NULL                           AS 'DISPOSAL DATE',
    NULL                           AS 'DISPOSAL TYPE',
    NULL                           AS 'DATE CREATED',
    NULL                           AS 'CREATED BY',
    NULL                           AS 'COST PRICE'
 FROM   stock_assign
    left join stock_items
           ON stock_assign.stock_number = stock_items.stock_number
    left join depots
           ON depot_id = depots.dpt_id
GROUP  BY stock_items.stock_number,
       depots.dpt_name)
UNION
(SELECT stock_items.item_id         AS 'ITEM_ID',
    stock_items.stock_number    AS 'STOCK NUMBER',
    NULL                        AS 'TURNOVER',
    NULL                        AS 'DAYS ON HIRE',
    stock_items.equipment_class AS ' CAT',
    stock_items.name            AS 'ITEM NAME',
    depots.dpt_name             AS 'DEPOT LOC',
    stock_items.disposal_date   AS 'DISPOSAL DATE',
    stock_items.disposal_type   AS 'DISPOSAL TYPE',
    stock_items.date_available  AS 'DATE CREATED',
    users.usr_name              AS 'CREATED BY',
    fixed_assets.cost_amount    AS 'COST PRICE'
FROM   stock_items
    left join users
           ON users.usr_id = stock_items.userid_created
    left join depots
           ON stock_items.depot_id_located_at = depots.dpt_id
    left join fixed_assets
           ON stock_items.item_id = fixed_assets.stock_item_id)
ORDER  BY 'STOCK NUMBER' ASC) a
GROUP BY a.[Stock Number]

然后，您需要按2个查询中的所有类似列进行分组，即。股票号码。如果在两个查询中都有十进制值，则使用SUM，如果一个查询中为null，而另一个查询中为null，则使用MAX。

如果正确地执行此操作，则每个股票编号将有一个分组行。

作为附带说明，尽量避免在列名中使用空格，而应将其替换为a_