gpt4 book ai didi

mysql - 合并两个表而不使用 mysql 中的连接?

转载 作者:行者123 更新时间:2023-11-28 23:39:05 27 4
gpt4 key购买 nike

我尝试合并两个不同表中的值,但没有成功。第一个显示的和我想要的一样,但第二个每次都只显示第一行。

Select * From (Select date as Date, Sum(qty) as qtySum, Sum(weight) 
as weightSum From stock_list Group by date) as A,
(Select Sum(weight) as weightSum,Count(barcode)
as barcodeCount From selected_items Group by date) as B Group by date;

这是我得到的输出。

enter image description here

These is my selected_items table.

enter image description here

这是我的库存 list 。 enter image description here

我的两个查询都是单独工作的,只有当我尝试将它们一起运行时我才会得到正确的输出,这给第二个查询带来了问题。任何人都可以指出我的错误或告诉我更好的方法来做到这一点。这就是我的最终目标 enter image description here

最佳答案

第一个问题是您在子查询 B 中按日期分组,但您没有选择它,因此您的结果集可能类似于:

weightSum       barcodeCount
---------------------------
26 8
9 14
4 7

这是 3 个日期的结果,但您不知道哪一行指的是哪个日期。

您的下一个问题是您正在使用交叉连接,因为您的两个查询之间没有链接,这意味着如果您的第一个查询返回:

Date            qtySum      weightSum
----------------------------------------
2016-01-20 1 1
2016-01-21 2 2

完成此交叉连接后,您将结束:

Date            qtySum      a.weightSum     b.weightSum     barcodeCount
--------------------------------------------------------------------------
2016-01-20 1 1 26 8
2016-01-20 1 1 9 14
2016-01-20 1 1 4 7
2016-01-21 2 2 26 8
2016-01-21 2 2 9 14
2016-01-21 2 2 4 7

因此,A 中的每一行都与 B 中的每一行相匹配,总共有 6 行。

你的第三个问题是你然后按日期分组,但不执行任何聚合,没有深入研究 SQL 标准的细则、group by 子句和功能依赖性,让我们将其简化为 MySQL 允许这个,但你不应该这样做,除非你了解限制(这在 this answer 中有更详细的介绍)。 select 中不在 group by 子句中的任何内容都应该在聚合中。

因此,由于 MySQL 的 GROUP BY 扩展通过选择所有内容并仅按日期分组,您实际上是说每个日期取 1 行,但您无法控制哪一行,它可能是每个组的第一行如上所示,您将得到的结果是:

Date            qtySum      a.weightSum     b.weightSum     barcodeCount
--------------------------------------------------------------------------
2016-01-20 1 1 26 8
2016-01-21 2 2 26 8

我认为这就是为什么您最终会重复子查询 B 中的所有相同值。

这样就涵盖了错误所在,现在开始解决方案,假设 stock_list 中的日期在 selected_items 中不存在,反之亦然需要完全连接,但由于 MySQL 不支持此功能,因此您必须使用 UNION,最简单的方法是:

SELECT  t.Date,
SUM(t.StockQuantity) AS StockQuantity,
SUM(t.StockWeight) AS StockWeight,
SUM(t.SelectedWeight) AS SelectedWeight,
SUM(t.BarcodeCount) AS BarcodeCount
FROM ( SELECT date,
SUM(qty) AS StockQuantity,
SUM(weight) AS StockWeight,
0 AS SelectedWeight,
0 AS BarcodeCount
FROM stock_list
GROUP BY Date
UNION ALL
SELECT date,
0 AS StockQuantity,
0 AS StockWeight,
SUM(weight) AS SelectedWeight,
COUNT(BarCode) AS BarcodeCount
FROM selected_items
GROUP BY Date
) AS t
GROUP BY t.Date;

编辑

我无法对此进行测试,也不确定您的确切逻辑,但您可以使用 variables to calculate a running total in MySQL .这应该给出如何做的想法:

SELECT  Date,
StockQuantity,
StockWeight,
SelectedWeight,
BarcodeCount,
(@w := @w + StockWeight - SelectedWeight) AS TotalWeight,
(@q := @q + StockQuantity - BarcodeCount) AS TotalQuantity
FROM ( SELECT t.Date,
SUM(t.StockQuantity) AS StockQuantity,
SUM(t.StockWeight) AS StockWeight,
SUM(t.SelectedWeight) AS SelectedWeight,
SUM(t.BarcodeCount) AS BarcodeCount
FROM ( SELECT date,
SUM(qty) AS StockQuantity,
SUM(weight) AS StockWeight,
0 AS SelectedWeight,
0 AS BarcodeCount
FROM stock_list
GROUP BY Date
UNION ALL
SELECT date,
0 AS StockQuantity,
0 AS StockWeight,
SUM(weight) AS SelectedWeight,
COUNT(BarCode) AS BarcodeCount
FROM selected_items
GROUP BY Date
) AS t
GROUP BY t.Date
) AS t
CROSS JOIN (SELECT @w := 0, @q := 0) AS v
GROUP BY t.Date;

关于mysql - 合并两个表而不使用 mysql 中的连接?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/34900732/

27 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com