从MySQL中的行动态创建列 [英] Dynamically create columns from rows in MySQL
问题描述
我有下表:
"crawlresults"
id | url | fk_crawljobs_id
---------------------------------------------
1 | shop*com/notebooks | 1
2 | shop*com/fridges | 1
3 | website*com/lists | 2
"extractions"
id | fk_extractors_id | data | fk_crawlresults_id
---------------------------------------------------------------
1 | 1 | 123.45 | 1
2 | 2 | notebook | 1
3 | 3 | ibm.jpg | 1
4 | 1 | 44.5 | 2
5 | 2 | fridge | 2
6 | 3 | picture.jpg | 3
7 | 4 | hello | 3
8 | 4 | world | 3
9 | 5 | hi | 3
10 | 5 | my | 3
11 | 5 | friend | 3
"extractors"
id | extractorname
----------------------
1 | price
2 | article
3 | imageurl
4 | list_1
5 | list_2
我需要构造一个select语句来获取提取表中使用的提取器表中每个提取器的列.
I need to construct a select statement to get columns for each extractor in the extractors table that is used in the extractions table.
示例:
url | price | article | imageurl
--------------------------------------------------------
shop*com/notebooks | 123.45 | notebook | ibm.jpg
shop*com/fridges | 44.5 | fridge | NULL
当我执行select语句时，我没有多少提取器名称，因此必须动态构建它.
I don't how much extractornames exists when I execute the select statement so it have to be dynamically built.
我忘了提一下，我的提取中可能有多个列表".在这种情况下，我需要以下结果集.
I forgot to mention that it is possible that I have multiple "lists" in my extractions. In this case I need a the following result set.
示例2:
url | list_1 | imageurl | list_2
--------------------------------------------------------
website*com/lists | hello | picture.jpg | NULL
website*com/lists | world | picture.jpg | NULL
website*com/lists | NULL | picture.jpg | hello
website*com/lists | NULL | picture.jpg | my
website*com/lists | NULL | picture.jpg | friend
谢谢！
推荐答案
您正在寻找动态数据透视表.
代码:
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'MAX(IF(pa.extractorname = ''',
extractorname,
''', p.data, NULL)) AS ',
extractorname
)
) INTO @sql
FROM extractors;
SET @sql = CONCAT('SELECT c.url, ',
@sql,
' FROM crawlresults c',
' INNER JOIN extractions p on (c.id = p.fk_crawlresults_id)',
' INNER JOIN extractors pa on (p.fk_extractors_id = pa.id)'
' WHERE c.fk_crawljobs_id = 1',
' GROUP BY c.id');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
基本上，您的原始查询正在生成伪造的@sql变量，该变量并未真正为每个extractorname提取data.您也不需要所有这些联接来创建@sql.您只需要每个属性名称(来自extractor表)和对包含期望值的列的引用(data).
Basically your original query was generating a bogus @sql variable which didn't really extract data for each extractorname. You also don't need all of those joins for creating @sql. You only need each one of the property names (from extractor table) and a reference to the column holding the expect values (data).
如果对结构有疑问，请为固定的属性写一个简单的数据透视查询.这样，就可以轻松识别用于编写动态查询的模式.
When in doubt about the structure, write out a simple pivot query for a fixed set of properties. This way it becomes easy to identify the pattern for writing the dynamic query.
SELECT c.url,
MAX(IF(pa.extractorname = 'price', p.data, NULL)) AS price,
MAX(IF(pa.extractorname = 'article', p.data, NULL)) AS article,
MAX(IF(pa.extractorname = 'imageurl', p.data, NULL)) AS imageurl
FROM crawlresults c
LEFT JOIN extractions p on (c.id = p.fk_crawlresults_id)
LEFT JOIN extractors pa on (p.fk_extractors_id = pa.id)
WHERE c.fk_crawljobs_id = 1
GROUP BY c.id
对于其余查询，这很好，请记住，如果某些crawlresults没有extractions，则LEFT JOINS可能很有用.同样，如果您的表中每个url/fk_crawljobs_id可以包含多个crawlresult，则按url分组是个坏主意(MAX可能会混合来自多个extractions的结果).
As for the rest of your query it is fine, just keep in mind that the LEFT JOINS could be useful if there are no extractions for some crawlresults. Also if your table can contain more than one crawlresult per url / fk_crawljobs_id, grouping by url is a bad idea (MAX can potentially mix the results from multiple extractions).
这篇关于从MySQL中的行动态创建列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
