MYSQL创建动态枢轴表
MYSQL创建动态枢轴表
提问于 2014-06-29 05:21:24
这是我的疑问
SELECT w.purchase_date, c.center_name, sum(w.final_kg) FROM wd_leaf_purchase w
join wd_leaves_supplier s on w.supplier_id = s.supplier_id
join wd_collection_center c on s.center_id = c.center_id
WHERE EXTRACT(MONTH FROM w.purchase_date) = MONTH(NOW())
AND EXTRACT(YEAR FROM w.purchase_date) = YEAR(NOW())
group by w.purchase_date, c.center_name;以上查询的输出如下
'2014-06-11', 'Main Center', '10.00'
'2014-06-20', 'Main Center', '10.00'
'2014-06-26', 'Main Center', '9.00'
'2014-06-26', 'Center B', '9.50'
'2014-06-27', 'Main Center', '73.00'
'2014-06-27', 'Center B', '73.00'
'2014-06-28', 'Main Center', '103.00'
'2014-06-28', 'Center B', '8.00'但我需要像下面这样的输出。
'2014-06-11', 'Main Center', '10.00', 'Center B', '0'
'2014-06-20', 'Main Center', '10.00', 'Center B', '0'
'2014-06-26', 'Main Center', '9.00' 'Center B', '9.50'
'2014-06-27', 'Main Center', '73.00' 'Center B', '73.00'
'2014-06-28', 'Main Center', '103.00' 'Center B', '8.00'如何编写查询以获得以上输出？
这是木琴
回答 1
Stack Overflow用户
回答已采纳
发布于 2014-06-29 05:31:59
使用准备好的语句动态地构建列：
set @sql:=null;
select group_concat(distinct concat('sum(if(c.center_name="',center_name,'",w.final_kg,0)) `', center_name,'`') order by center_id)
from wd_collection_center
into @sql;
set @sql:=concat('SELECT w.purchase_date,', @sql, '
FROM wd_leaf_purchase w
join wd_leaves_supplier s on w.supplier_id = s.supplier_id
join wd_collection_center c on s.center_id = c.center_id
WHERE EXTRACT(MONTH FROM w.purchase_date) = MONTH(NOW())
AND EXTRACT(YEAR FROM w.purchase_date) = YEAR(NOW())
group by w.purchase_date;');
prepare st from @sql;
execute st;
deallocate prepare st;小提琴
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/24473398
复制相关文章
相似问题
腾讯云开发者
