如何从mysql中的表中创建选择动态字段?

2021-12-17 00:00:00 join sql left-join mysql

我在 select 语句中加入查询，如下所示:

I have query join in select statement like this : 

select a.item_number, total_quantity, store, factory
from (
    select item_number, sum(quantity) as "total_quantity"
    from `item_details`
    group by item_number
) `a` 
left join (
    select item_number, sum(quantity) as 'store' 
    from `item_details` where location_code = 'STORE' 
    group by item_number
) `b` on `a`.`item_number` = `b`.`item_number` 
left join (
    select item_number, sum(quantity) as 'factory' 
    from `item_details` 
    where location_code = 'FACTORY' 
    group by item_number
) `c` on `a`.`item_number` = `c`.`item_number` 
order by `item_number` asc

从上面的查询来看，如果我使用表 item_details 和字段 id、item_no、quantity 和 location_code

From the query above, it appears if I use table item_details with fields id, item_no, quantity and location_code

如果查询执行，结果如下:

If the query executed, the result like this :

结果是正确的.但在这里我想创建现场商店和工厂是动态的.所以它取自表位置.因为数据位置是动态的.可以添加和删除

The results are correct. But here I want to create field store and factory to be dynamic. So it's taken from table locations. Because the data location is dynamic. It can be added and removed

所以我有带有字段 ID 和描述的表格位置，如下所示:

So I have table locations with field id and description like this :

item_details 表中的field location_code 是locations 表中field id 的外键

field location_code in the item_details table is foreign key to field id in locations table

那么如何从位置表中创建选择动态字段?

So how to create select dynamic fields from location table?

注意:

我使用select 语句中的查询联接"，因为我之前没有表位置.现在我使用表位置.因为位置表中的数据是动态的.它可以添加和删除.所以我想像上面的表 1 一样显示它，并带有表位置.似乎需要加入表格.但是我还是很迷茫

I use "query join in select statement" because I didn't have table locations before. Now I use table locations. Because the data in the location table is dynamic. It can be added and removed. So I want to display it like table 1 above with table location. Seems it need to join the table. But i'm still confused to do it

推荐答案

这没有经过测试，如果你发现错误，请创建一个 fiddle.

This is not tested ,create a fiddle if you find errors.

SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'ifnull(SUM(case when location_code = ''',
      location_code ,
      ''' then quantity end),0) AS `',
      location_code , '`'
    )
  ) INTO @sql
FROM
  item_details;
SET @sql = CONCAT('SELECT item_number,SUM(quantity) as "total_quantity", ', @sql, ' 
                  FROM item_details
                   GROUP BY item_number');

PREPARE stmt FROM @sql;
EXECUTE stmt;

DEALLOCATE PREPARE stmt;

相关文章