假設有兩個不同的表：
包裹
id
--
1
2
3
產品
id | package_id | currency | total
----------------------------------
1 | 1 | USD | 100
2 | 1 | EUR | 200
3 | 2 | USD | 300
4 | 2 | USD | 400
5 | 3 | GBP | 500
期望的結果是通過每種 DISTINCT 貨幣獲得每個包裹（來自其產品）的串聯總金額，例如：
id | total_amount
----------------------
1 | USD 100, EUR 200
2 | USD 700
3 | GBP 500
嘗試使用此查詢：
SELECT
packages.id,
(
SELECT
GROUP_CONCAT(CONCAT(currency,' ',total))
FROM
(
SELECT
products.currency AS currency,
SUM(products.total) AS total
FROM products
WHERE products.package_id = [[ packages.id ]] -- double brackets just for marking
GROUP BY products.currency
) T
) AS total_amount
FROM packages
LEFT JOIN products
ON products.package_id = packages.id
GROUP BY packages.id;
但是有一個錯誤，package.id（在上面的雙括號中）不可見，可能是因為子查詢深度。
有什么辦法可以實作嗎？謝謝。
uj5u.com熱心網友回復：
您可以嘗試將查詢重寫為左連接而不是子查詢，這可能會更高效或更快，如下所示：
SELECT
p.id,
ct.currency_totals
FROM
packages p
LEFT JOIN (
SELECT
package_id,
GROUP_CONCAT(
CONCAT(currency,' ',total)
) as currency_totals
FROM (
SELECT
package_id,
currency,
SUM(total) as total
FROM
products
GROUP BY
package_id,
currency
) t
GROUP BY
package_id
) ct on ct.package_id=p.id;
| ID | currency_totals |
|---|---|
| 1 | 100 美元，200 歐元 |
| 2 | 700 美元 |
| 3 | 500英鎊 |
此外，如果您只需要當前使用的包的包 ID 而沒有其他詳細資訊，則使用您的子查詢可能足以完成此任務。
SELECT
package_id,
GROUP_CONCAT(
CONCAT(currency,' ',total)
) as currency_totals
FROM (
SELECT
package_id,
currency,
SUM(total) as total
FROM
products
GROUP BY
package_id,
currency
) t
GROUP BY
package_id;
| 包 ID | currency_totals |
|---|---|
| 1 | 100 美元，200 歐元 |
| 2 | 700 美元 |
| 3 | 500英鎊 |
查看 DB Fiddle 上的作業演示
uj5u.com熱心網友回復：
您可以使用 ：
select package_id , group_concat(concat(currency,' ',grandtotal))
from (
select package_id,currency, sum(total) grandtotal
from products
group by package_id,currency
) t group by package_id
db<>在這里擺弄
uj5u.com熱心網友回復：
嘗試使用alias表名來識別...
SELECT
pkg.id,
(
SELECT
GROUP_CONCAT(CONCAT(currency,' ',total))
FROM
(
SELECT
products.currency AS currency,
SUM(products.total) AS total
FROM products
WHERE products.package_id = pkg.id
GROUP BY products.currency
) T
) AS total_amount
FROM packages as pkg
LEFT JOIN products
ON products.package_id = pkg.id
GROUP BY pkg.id;
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/338958.html