我之所以创建这个,是因为我对答案很好奇。
我有一个基本上有4列的表:GroupA、GroupB、Tag和value。
A组 | B组 | 标记 | 值
---|---|---|
A | E | 前缀年份3.子类别子类别A100 |
B | F | 前缀年份3.子类别B子类别AA | [/tr>
C | G前缀年份4.子类别子类别Z | 300 |
您的样本数据和显示的预期结果不匹配,因此需要一些猜测,特别是对于不在所提供数据中的category
,但我相信您可以获得如下所需的数据:
CREATE TABLE mytable(
GroupA VARCHAR(1) NOT NULL
,Group_B VARCHAR(1) NOT NULL
,Tag VARCHAR(39) NOT NULL
,Value VARCHAR(3) NOT NULL
);
INSERT INTO mytable(GroupA,Group_B,Tag,Value) VALUES ('A','E','PrefixYear3.SubCategoryASubSubcategoryA','100');
INSERT INTO mytable(GroupA,Group_B,Tag,Value) VALUES ('B','F','PrefixYear3.SubCategoryBSubSubcategoryA','A');
INSERT INTO mytable(GroupA,Group_B,Tag,Value) VALUES ('C','G','PrefixYear4.SubCategoryCSubSubcategoryZ','300');
那么这个查询:
select
SubCategoryA, SubCategoryB, SubCategoryC, Year, GroupA as Category
from (
select
*
, case when tag like '%.SubCategoryA%' then try_cast(value as float) end as SubCategoryA
, case when tag like '%.SubCategoryB%' then try_cast(value as float) end as SubCategoryB
, case when tag like '%.SubCategoryC%' then try_cast(value as float) end as SubCategoryC
, case when tag like 'PrefixYear3%' then 2010
when tag like 'PrefixYear4%' then 2011
else NULL
end as [Year]
from mytable
) as sq
它产生这个输出:
+--------------+--------------+--------------+------+----------+
| SubCategoryA | SubCategoryB | SubCategoryC | Year | Category |
+--------------+--------------+--------------+------+----------+
| 100 | | | 2010 | A |
| | | | 2010 | B |
| | | 300 | 2011 | C |
+--------------+--------------+--------------+------+----------+
在db<gt;小提琴这里
注意:我使用了try_cast()
,因为如果它无法从varchar值返回浮点值,它将正常地返回NULL。