如何在mysql中的表中创建选择动态字段? [英] How to create select dynamic fields from a table in 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，数量和位置代码一起使用，则会显示

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表中的字段location_code是位置表中字段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

推荐答案

未经测试，如果发现错误，请创建小提琴.

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;

这篇关于如何在mysql中的表中创建选择动态字段?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！