我有3張桌子。
company_report(old_product_code,count)dictionary(old_product_code, new_product_code)myreport(new_product_code, package_of_1, package_of_3, package_of_5)
在我的company_report桌子上，我有時有 2 或 3 個old_product_code適合一個new_product_code
我想要的結果是my_report表。
附上示例圖片。
uj5u.com熱心網友回復：
看起來你只是想要一個有條件聚合的連接
SELECT
d.new_product_code,
Package1 = SUM(CASE WHEN REPLACE(cr.old_product_code, d.new_product_code, '') = '1' THEN count END),
Package3 = SUM(CASE WHEN REPLACE(cr.old_product_code, d.new_product_code, '') = '3' THEN count END),
Package5 = SUM(CASE WHEN REPLACE(cr.old_product_code, d.new_product_code, '') = '5' THEN count END)
FROM company_report cr
JOIN dictionary d ON d.old_product_code = cr.old_product_code
GROUP BY
d.new_product_code;
uj5u.com熱心網友回復：
select d.new_code
,case when SUBSTRING(fieldName, PATINDEX('%[0-9]%', fieldName), LEN(fieldName))='1' then count end as package_1
,case when SUBSTRING(fieldName, PATINDEX('%[0-9]%', fieldName), LEN(fieldName))='3' then count end as package_3
,case when SUBSTRING(fieldName, PATINDEX('%[0-9]%', fieldName), LEN(fieldName))='5' then count end as package_5
from dictionary d
left join company_report c
on d.old_code=c.old_product_code
group by d.new_code
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/325101.html
標籤：sql sql-server 短信
上一篇：如何在兩個查詢結果之間執行邏輯與