使用具有某些不同值的联接表对数据进行分组
我正努力想办法解决这个问题，所以在我解释的时候，请与我一起赤裸。
我们有一个订单系统，它记录订单、订单行、商品、用户等。我感兴趣的是订单和订单。
目前，我们制作销售数据的报告，显示销售，回报等，但它是缓慢和低效的。这是因为我们使用PHP/MS查询各个表，遍历数组，然后将数据合并在一起。
我的任务是努力提高效率，所以我尝试用连接表、别名表和子查询表生成一个查询。它们可以很好地工作并连接在一起，但是由于对表进行分组的方式，我得到了错误的值。
注意，我无法更改任何模式，而且我正在使用现有的系统。
这个解释可能听起来很长，我很可能会用我想要的东西失去你，但请留在我身边。
无论如何，下面是当前模式、示例数据和我的查询结果的图表：
SELECT
`ol`.`storeID`,
SUM(DISTINCT `o`.`grossValue`) AS 'Total',
SUM(DISTINCT `o`.`paymentValue`) AS 'paymentTotal'
FROM `orders` AS `o`
LEFT JOIN `orderLines` AS `ol` ON `o`.`orderID` = `ol`.`orderID`
WHERE (`o`.`orderDate` BETWEEN '2015-07-07 00:00:00' AND '2015-07-07 23:59:59')
GROUP BY `ol`.`storeID`我已经在MySQL中快速地生成了上面的内容，因为我现在在家，我想这个解决方案对于SQL server来说是相似的，但是如果我错了，请纠正我，或者为两者都提供解决方案，这将是很棒的！
基本上，这些命令有很多行。订单记录了线路的总价值，以及到目前为止支付了多少。每个订单行都是对库存项的引用。每一行记录存储它被记录到，因为不同的项目可以从不同的商店来源。我们想要制作报告，显示每一家商店的营业额。
正如您在查询商店06的结果中所看到的，显示的是4.99，但是这应该是9.98。
正在发生的事情是，netValue正在变得与众不同。我不想要这个。我不想要订单<=>命令行的副本，但是我想要netValue的副本，因为这是单独的订单。
我把你弄糊涂了吗？
是否有一种方法仍然按storeID进行分组，并通过orderID或orderLineID对netValue进行分组？
我尝试过所有类型的组合，并得到了许多错误，我现在不记得了，但主要是关于聚合列，无法分组等。
(欢迎所有建议和帮助:)
创建表代码：
CREATE TABLE `orderLines` (
`orderLineID` int(10) NOT NULL AUTO_INCREMENT,
`orderID` int(10) NOT NULL,
`itemCode` varchar(30) NOT NULL,
`netValue` decimal(10,2) NOT NULL,
`taxValue` decimal(10,2) NOT NULL,
`storeID` varchar(2) NOT NULL,
PRIMARY KEY (`orderLineID`)
);
CREATE TABLE `orders` (
`orderID` int(10) NOT NULL AUTO_INCREMENT,
`grossValue` decimal(10,2) NOT NULL,
`paymentValue` decimal(10,2) NOT NULL,
`typeID` int(10) NOT NULL,
`orderDate` datetime NOT NULL,
PRIMARY KEY (`orderID`)
);预期结果：
+---------+-------+--------------+
| storeID | Total | paymentTotal |
+---------+-------+--------------+
| 02 | 10.00 | 8.00 |
| 06 | 9.98 | 9.98 |
| 07 | 3.00 | 3.00 |
+---------+-------+--------------+回答 2
Database Administration用户
发布于 2015-07-07 20:36:05
假设您想要将每个storeID的netValues之和，但是一个paymenValue是不同的：
查询：
SELECT storeID,SUM(Total) AS Total,SUM(paymentTotal) AS paymentTotal FROM(
SELECT ol.storeID,SUM(ol.netValue) AS 'Total',0.00 AS 'paymentTotal'
FROM orders AS o
LEFT JOIN orderLines AS ol ON (o.orderID = ol.orderID)
WHERE o.orderDate BETWEEN '2015-07-07 00:00:00' AND '2015-07-07 23:59:59'
GROUP BY ol.storeID
UNION ALL
SELECT ol.storeID,0.00 AS 'Total',o.paymentValue AS 'paymentTotal'
FROM orders AS o
LEFT JOIN orderLines AS ol ON (o.orderID = ol.orderID)
WHERE o.orderDate BETWEEN '2015-07-07 00:00:00' AND '2015-07-07 23:59:59'
GROUP BY ol.storeID) AS Result
GROUP BY storeID;测试：
mysql> SELECT storeID,SUM(Total) AS Total,SUM(paymentTotal) AS paymentTotal FROM(
-> SELECT ol.storeID,SUM(ol.netValue) AS 'Total',0.00 AS 'paymentTotal'
-> FROM orders AS o
-> LEFT JOIN orderLines AS ol ON (o.orderID = ol.orderID)
-> WHERE o.orderDate BETWEEN '2015-07-07 00:00:00' AND '2015-07-07 23:59:59'
-> GROUP BY ol.storeID
-> UNION ALL
-> SELECT ol.storeID,0.00 AS 'Total',o.paymentValue AS 'paymentTotal'
-> FROM orders AS o
-> LEFT JOIN orderLines AS ol ON (o.orderID = ol.orderID)
-> WHERE o.orderDate BETWEEN '2015-07-07 00:00:00' AND '2015-07-07 23:59:59'
-> GROUP BY ol.storeID) AS Result
-> GROUP BY storeID;
+---------+-------+--------------+
| storeID | Total | paymentTotal |
+---------+-------+--------------+
| 2 | 10.00 | 8.00 |
| 6 | 9.98 | 4.99 |
| 7 | 3.00 | 3.00 |
+---------+-------+--------------+
3 rows in set (0.00 sec)
mysql> Database Administration用户
发布于 2015-07-07 21:24:52
这是Server的回答，希望在mySQL中类似：
DECLARE @orderLines TABLE (
orderLineID INT,
orderID INT,
itemCode VARCHAR(30),
netValue DECIMAL(10,2),
taxValue DECIMAL(10,2),
storeID VARCHAR(2)
);
DECLARE @orders TABLE (
orderID INT,
grossValue DECIMAL(10,2),
paymentValue DECIMAL(10,2),
typeID INT,
orderDate DATETIME
);
INSERT INTO @orderLines VALUES
(1, 1, 'BK001', 4.99, 0.00, '06'),
(2, 2, 'YX002', 2.00, 0.00, '06'),
(3, 2, 'CC5683', 2.99, 0.00, '06'),
(4, 3, 'LL007', 5.00, 0.00, '02'),
(5, 3, 'LL007', 5.00, 0.00, '02'),
(6, 4, 'MN476756', 3.00, 0.00, '07'),
(7, 5, 'LX/34', 0.33, 0.00, '12'),
(8, 5, 'LX/34', 0.33, 0.00, '12'),
(9, 5, 'LX/34', 0.33, 0.00, '12');
INSERT INTO @orders VALUES
(1, 4.99, 4.99, 0, '2015-07-07 14:09:00'),
(2, 4.99, 4.99, 0, '2015-07-07 14:32:19'),
(3, 10.00, 8.00, 0, '2015-07-07 15:13:07'),
(4, 3.00, 3.00, 0, '2015-07-07 16:22:10'),
(5, 0.99, 0.99, 0, '2015-07-08 09:03:00');
WITH orderLinesCTE AS
(
SELECT storeID, orderID, sum(netValue) as netValue
FROM @orderLines
GROUP BY storeID, orderID
)
SELECT
ol.storeID,
SUM(o.grossValue) AS 'Total',
SUM(o.paymentValue) AS 'paymentTotal'
FROM @orders AS o
LEFT JOIN orderLinesCTE AS ol ON o.orderID = ol.orderID
WHERE (o.orderDate BETWEEN '2015-07-07 00:00:00' AND '2015-07-07 23:59:59')
GROUP BY ol.storeID;https://dba.stackexchange.com/questions/106239
复制相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号