gpt4 book ai didi

MySQL:如何转换为 EAV?

转载 作者:搜寻专家 更新时间:2023-10-30 19:53:59 24 4
gpt4 key购买 nike

假设我有下表:

TABLE: one
===============================
| id | first_name | last_name |
===============================
| 1 | John | Doe |
| 2 | Jane | Smith |
-------------------------------

我想将其转换为 EAV:

TABLE: two
===================================
| id | fk_id | attribute | value |
===================================
| 1 | 1 | first_name | John |
| 2 | 1 | last_name | Doe |
| 3 | 2 | first_name | Jane |
| 4 | 2 | last_name | Smith |
-----------------------------------

注意:

  • two.fk_id 值来自 one.id
  • two.attribute 值来自 one.first_name
  • two.value 值来自 one.last_name

我需要从表 1 创建表 2,以便运行一些测试。

最佳答案

我假设 two.id 是一个自动递增的整数,所以我将把它留给目标表来分配这些值。

select id as fk_id, 'first_name' as attribute, first_name as value
from one
union all
select id as fk_id, 'last_name', last_name
from one
order by fk_id, attribute

关于MySQL:如何转换为 EAV?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/7020661/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com