SQL GROUP_CONCAT分为不同的列 [英] SQL GROUP_CONCAT split in different columns
问题描述
我进行了很多搜索,但没有找到解决问题的合适方法.
I searched a lot, but didn't find a proper solution to my problem.
我想做什么?
我在MySQL中有2个表: - 国家 - 货币 (由于多对多的关系,我通过CountryCurrency将他们加入了我们的行列)
I have 2 tables in MySQL: - Country - Currency (I join them together via CountryCurrency --> due to many to many relationship)
请参见以下示例: http://sqlfiddle.com/#!2 /317d3/8/0
我想使用联接将两个表链接在一起,但是我想仅显示每个国家/地区的一行(某些国家/地区使用多种货币,所以这是第一个问题).
I want to link both tables together using a join, but I want to show just one row per country (some countries have multiple currencies, so that was the first problem).
我找到了group_concat函数:
I found the group_concat function:
SELECT country.Name, country.ISOCode_2, group_concat(currency.name) AS currency
FROM country
INNER JOIN countryCurrency ON country.country_id = countryCurrency.country_id
INNER JOIN currency ON currency.currency_id = countryCurrency.currency_id
GROUP BY country.name
结果如下:
NAME ISOCODE_2 CURRENCY
Afghanistan AF Afghani
Åland Islands AX Euro
Albania AL Lek
Algeria DZ Algerian Dinar
American Samoa AS US Dollar,Kwanza,East Caribbean Dollar
但是我现在想要的是将货币分成不同的列(货币1,货币2,...).我已经尝试过MAKE_SET()之类的功能,但这不起作用.
But what I want now is to split the currencies in different columns (currency 1, currency 2, ...). I already tried functions like MAKE_SET() but this doesn't work.
推荐答案
您可以使用substring_index()
进行此操作.以下查询将您的查询用作子查询,然后应用此逻辑:
You can do this with substring_index()
. The following query uses yours as a subquery and then applies this logic:
select Name, ISOCode_2,
substring_index(currencies, ',', 1) as Currency1,
(case when numc >= 2 then substring_index(substring_index(currencies, ',', 2), ',', -1) end) as Currency2,
(case when numc >= 3 then substring_index(substring_index(currencies, ',', 3), ',', -1) end) as Currency3,
(case when numc >= 4 then substring_index(substring_index(currencies, ',', 4), ',', -1) end) as Currency4,
(case when numc >= 5 then substring_index(substring_index(currencies, ',', 5), ',', -1) end) as Currency5,
(case when numc >= 6 then substring_index(substring_index(currencies, ',', 6), ',', -1) end) as Currency6,
(case when numc >= 7 then substring_index(substring_index(currencies, ',', 7), ',', -1) end) as Currency7,
(case when numc >= 8 then substring_index(substring_index(currencies, ',', 8), ',', -1) end) as Currency8
from (SELECT country.Name, country.ISOCode_2, group_concat(currency.name) AS currencies,
count(*) as numc
FROM country
INNER JOIN countryCurrency ON country.country_id = countryCurrency.country_id
INNER JOIN currency ON currency.currency_id = countryCurrency.currency_id
GROUP BY country.name
) t
表达式substring_index(currencies, ',' 2)
将货币列表以第二个货币为准.对于American Somoa,这将是'US Dollar,Kwanza'
.下次以-1
作为参数的调用将使用列表的最后一个元素,即'Kwanza'
,这是currencies
的第二个元素.
The expression substring_index(currencies, ',' 2)
takes the list in currencies up to the second one. For American Somoa, that would be 'US Dollar,Kwanza'
. The next call with -1
as the argument takes the last element of the list, which would be 'Kwanza'
, which is the second element of currencies
.
还请注意,SQL查询返回一组定义明确的列.查询不能具有可变的列数(除非您通过prepare
语句使用动态SQL).
Also note that SQL queries return a well-defined set of columns. A query cannot have a variable number of columns (unless you are using dynamic SQL through a prepare
statement).
这篇关于SQL GROUP_CONCAT分为不同的列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!