帶有動態列的MySQL樞軸表查詢我使用以下表格存儲產品數據：mysql> SELECT * FROM product;+---------------+---------------+--------+| id | name | description | stock |+---------------+---------------+--------+| 1 | product1 | first product | 5 | | 2 | product2 | second product| 5 | +---------------+---------------+--------+mysql> SELECT * FROM product_additional;+-----------------+------------+| id | fieldname | fieldvalue |+-----------------+------------+| 1 | size | S || 1 | height | 103 || 2 | size | L || 2 | height | 13 || 2 | color | black |+-----------------+------------+使用以下查詢從兩個表中選擇記錄mysql> SELECT p.id , p.name , p.description ,MAX(IF(pa.fieldname = 'size', pa.fieldvalue, NULL)) as `size` ,MAX(IF(pa.fieldname = 'height', pa.fieldvalue, NULL)) as `height` ,MAX(IF(pa.fieldname = 'color', pa.fieldvalue, NULL)) as `color`FROM product pLEFT JOIN product_additional AS pa ON p.id = pa.idGROUP BY p.id+---------------+---------------+--------+---------+--------+| id | name | description | size | height | color |+---------------+---------------+--------+---------+--------+| 1 | product1 | first product | S | 103 | null || 2 | product2 | second product| L | 13 | black |+---------------+---------------+--------+---------+--------+一切正常工作：)因為我動態地填充了“附加”表，所以如果查詢也是動態的，那就更好了。這樣，每次輸入新的字段名和字段值時，我都不必更改查詢。
3 回答
米琪卡哇伊
TA貢獻1998條經驗 獲得超6個贊
delimiter //
create procedure myPivot(
in tableA varchar(255),
in columnA varchar(255),
in tableB varchar(255),
in columnB varchar(255))begin
set @sql = NULL;
set @sql = CONCAT('select group_concat(distinct concat(
\'SUM(IF(',
columnA,
' = \'\'\',',
columnA,
',\'\'\', 1, 0)) AS \'\'\',',
columnA,
',\'\'\'\') separator \', \') from ',
tableA, ' into @sql');
-- select @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
-- select @sql;
SET @sql = CONCAT('SELECT p.',
columnB,
', ',
@sql,
' FROM ', tableB, ' p GROUP BY p.',
columnB,'');
-- select @sql;
/* */
PREPARE stmt FROM @sql;
EXECUTE stmt;
/* */
DEALLOCATE PREPARE stmt;end//delimiter ;添加回答
舉報
0/150
提交
取消