MySQL 一对多关系:GROUP_CONCAT 或 JOIN 或两者兼而有之? [英] MySQL one to many relationship: GROUP_CONCAT or JOIN or both?
问题描述
我需要有关 MySQL 查询的帮助.我有三张桌子:
I need help with a MySQL query. I have three tables:
`product_category` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
);
`order_products` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(255) NOT NULL,
`qty` int(11) NOT NULL,
`unit_price` decimal(11,2) NOT NULL,
`category` int(11) NOT NULL,
`order_id` int (11) NOT NULL,
PRIMARY KEY (`id`)
);
`orders` (
`id` int(11) NOT NULL auto_increment,
`date` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
);
我有一个查询,按产品类别(在特定日期范围内)计算所有订单的小计.它看起来像这样:
I had a query that calculated subtotal of all orders by product category (in a certain date range). It looked like this:
SELECT
SUM(op.unit_price * op.qty) as amt,
c.name as category_name
FROM
order_products op,
product_category c,
orders o
WHERE
op.category = c.id
AND
op.order_id = o.id
AND
o.date > 'xxxxxxx'
GROUP BY
c.id
这很好用,但现在我想将单个产品及其小计添加到每一行,以便得到如下结果:
This works great, but now I want to add the individual products and their subtotals to each row so that I get a result like this:
c.name|amt|op.name (1) - op.subtotal (1), op.name (2), op.subtotal (2), etc....
我发现使用 GROUP_CONCAT 可以通过添加以下内容轻松显示名称:
I figured out using GROUP_CONCAT that I could get the names to show up pretty easily by adding:
GROUP_CONCAT(op.name) as product_name
到 SELECT 子句,但在我的一生中,我无法弄清楚如何让每个产品的小计显示在产品名称旁边.我有一种感觉,它涉及嵌套在 GROUP_CONCAT 中的连接或 CONCAT 的组合,但我尝试过的任何方法都没有奏效.有什么想法吗?
to the SELECT clause, but for the life of me I can't figure out how to get the subtotal for each product to show up next to the product name. I have a feeling it involves a combination of joins or CONCAT nested inside GROUP_CONCAT, but nothing I've tried has worked. Any ideas?
@piotrm 有一个看起来应该可行的想法(如下)但由于某种原因它返回以下内容:
@piotrm had an idea that seemed like it should work (below) but for some reason it returns the following:
SELECT `subtotals`
FROM `product_category`
WHERE `c`.`category_name` = 'Fragrance'AND.`amt` = '23164.50'AND.`subtotals` = CAST( 0x6c6f76656c696c7920454454202d203631302e30302c20466f72657665726c696c79202e313235206f7a2045445020726f6c6c657262616c6c202d20313831372e35302c20666f72657665726c696c79206361636865706f74202d2039302e30302c20666f72657665726c696c7920312f38206f756e63652070617266756d206f696c20726f6c6c657262616c6c202d20313833302e30302c20666f72657665726c696c792070657266756d696e6720626f6479206c6f74696f6e202d203938312e30302c20666f72657665726c696c7920332e34206f756e6365206561752064652070617266756d207370726179202009202d203535382e30302c20666f72657665726c696c79205363656e74656420566f746976652043616e646c65202d203132302e30302c20454450202620426f6f6b20736574202d203334332e30302c20666f72657665726c696c7920332e34206f756e6365206561752064652070617266756d207370726179202d2031363831352e3030 AS
BINARY ) ;
一旦我从原始 SELECT 子句中取出 s.subtotal,它就会提取正确的产品名称.JOIN 查询正确地提取产品及其关联的 category_id 和小计.如果不在这里造成混乱,我就无法让两个人一起参加 CONCAT.还有其他想法吗?
As soon as I take out s.subtotal from the original SELECT clause it pulls the correct product names. The JOIN query pulls the products out correctly with their associated category_id and subtotals. I just can't get the two to CONCAT together without creating this mess here. Any other thoughts?
解决方案
@piotrm 的查询基本上是正确的,除了 GROUP_CONCAT 正在寻找一组字符串.所以最终的查询是这样的:
@piotrm's query was basically right, except GROUP_CONCAT is looking for a collection of strings. So the final query looked like this:
SELECT c.name AS category_name,
SUM( s.subtotal ) AS amt,
GROUP_CONCAT( CONCAT(s.name, ' - ', cast(s.subtotal as char) ) SEPARATOR ', ') AS subtotals
FROM
product_category c
JOIN
(SELECT op.category, op.name, sum(op.qty*op.unit_price) AS subtotal
FROM order_products op
JOIN orders o ON o.id = op.order_id
WHERE o.date > '0'
GROUP BY op.category, op.name ) s
ON s.category = c.id
GROUP BY c.name
推荐答案
根据您的查询猜测,您的 order_products
表中还有 order_id
字段,但您没有在其中提及表定义.您的查询应如下所示:
Guessing from your query there is also order_id
field in your order_products
table you didn't mention in the table definition. Your query should then look like:
SELECT c.name AS category_name,
SUM( s.subtotal ) AS amt,
GROUP_CONCAT( CONCAT(s.name, ' - ', s.subtotal ) SEPARATOR ', ' ) AS subtotals
FROM
product_category c
JOIN
( SELECT op.category, op.name, sum(op.qty*op.unit_price) AS subtotal
FROM order_products op
JOIN orders o ON o.id = op.order_id
WHERE o.date > '2012-03-31'
GROUP BY op.category, op.name ) s
ON s.category = c.id
GROUP BY c.name
不过,您的数据库架构很奇怪,订单表看起来可以删除并且该日期移至 order_products,因为对于每个 order_products 行,您都引用了订单表.通常是另一种方式 - 订单表中 product_id 字段引用的每个产品都有很多订单.订单中的日期列也是 varchar 类型 - 为什么不是日期或日期时间?
Your db schema is quite weird though, orders table looks like it could be removed and that date moved to order_products, because for every order_products row you have reference to orders table. Usually it is the other way - there are many orders for every product referenced by product_id field in the orders table. Also date column in orders is of type varchar - why not date or datetime?
这篇关于MySQL 一对多关系:GROUP_CONCAT 或 JOIN 或两者兼而有之?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!