你可以这样做substring_index()
。以下查询使用您的查询作为子查询,然后应用此逻辑:
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)
将列表中的货币显示为第二个。对于美属萨摩亚来说,这将是'US Dollar,Kwanza'
。下一次通话-1
因为参数采用列表的最后一个元素,这将是'Kwanza'
,这是第二个元素currencies
.
另请注意,SQL 查询返回一组明确定义的列。查询不能具有可变数量的列(除非您通过prepare
陈述)。