SQL：键值：列中的值？

发布于 2024-11-27 18:23:33 字数 805 浏览 1 评论 0原文

我的表看起来像这样：

+--------------------------+
| key | value-name | value |
+-----+------------+-------+
| 1   | color      | green |
| 1   | height     | 15    |
| 2   | whatever   | lol   |
+-----+------------+-------+

我想翻转它，使它看起来像：

+-----+-------+--------+----------+
| key | color | height | whatever |
+-----+-------+--------+----------+
| 1   | green | 15     | ---      |
| 2   | ---   | ---    | lol      |
+-----+-------+--------+----------+

使用像这样的sql：

SELECT key AS k,
  (SELECT color FROM table WHERE key = k),
  (SELECT height FROM table WHERE key = k), ...

... 等等

在不为每个值名称创建子选择的情况下如何完成此操作（因为我相信这不是很快速地）？

我的DBS是Oracle 10g。

编辑：我后来找到了一些例子，但它们都只是加入，我不能这样做，因为我的表有很多值名称。

原文

my table looks like this:

+--------------------------+
| key | value-name | value |
+-----+------------+-------+
| 1   | color      | green |
| 1   | height     | 15    |
| 2   | whatever   | lol   |
+-----+------------+-------+

and i want to flip it so it looks like:

+-----+-------+--------+----------+
| key | color | height | whatever |
+-----+-------+--------+----------+
| 1   | green | 15     | ---      |
| 2   | ---   | ---    | lol      |
+-----+-------+--------+----------+

with sql like:

SELECT key AS k,
  (SELECT color FROM table WHERE key = k),
  (SELECT height FROM table WHERE key = k), ...

... and so on

How can ths be done without creating a subselect for every single value-name (because I believe this is not very fast) ?

My DBS is Oracle 10g.

Edit: I found some examples afterwards, but they all just join and I can't do that because my table has many value-names.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

戒ㄋ 2024-12-04 18:23:33

select 
  key
, max( case value-name when 'color' then value else NULL end)      as color
, max( case value-name when 'heght' then value else NULL end)      as height
, max( case value-name when 'whatever' then value else NULL end)   as whatever
from 
  table
group by key

select 
  key
, max( case value-name when 'color' then value else NULL end)      as color
, max( case value-name when 'heght' then value else NULL end)      as height
, max( case value-name when 'whatever' then value else NULL end)   as whatever
from 
  table
group by key

回复收藏 0 原文

邮友 2024-12-04 18:23:33

您首先将值作为单独的列获取，这可以使用 case 来完成，然后对键进行分组以将它们放入同一记录中：

select key, max(color), max(height), max(whatever)
from (
  select
    key,
    case when value-name = 'color' then value else null end as color,
    case when value-name = 'height' then value else null end as height,
    case when value-name = 'whatever' then value else null end as whatever
  from table
) x
group by key

You would first get the values as separate columns, which you can do with a case, then you would group on the key to get them into the same record:

select key, max(color), max(height), max(whatever)
from (
  select
    key,
    case when value-name = 'color' then value else null end as color,
    case when value-name = 'height' then value else null end as height,
    case when value-name = 'whatever' then value else null end as whatever
  from table
) x
group by key

回复收藏 0 原文

三月梨花 2024-12-04 18:23:33

使用条件 CASE 语句

CASE column
 WHEN column='a' THEN 'x'
 WHEN column='b' THEN 'y'
END

Use conditional CASE statement

CASE column
 WHEN column='a' THEN 'x'
 WHEN column='b' THEN 'y'
END

回复收藏 0 原文

苏璃陌 2024-12-04 18:23:33

您可以在此使用 pivot 函数方式：

SELECT *
FROM 
    table_name
PIVOT ( 
    max(value)
    for value-key
    in (
        'color' as color,
        'height' as height,
        'whatever' as whatever
    )
);

You can use the pivot function in this way:

SELECT *
FROM 
    table_name
PIVOT ( 
    max(value)
    for value-key
    in (
        'color' as color,
        'height' as height,
        'whatever' as whatever
    )
);

回复收藏 0 原文

~没有更多了~