如何将键/值样式表转换为 MySQL 中的普通表

发布于 2024-12-19 23:51:24 字数 1018 浏览 1 评论 0原文

我正在尝试将下表转换为更容易查询的内容。历史数据很重要，因此不能被丢弃，并且变量的选项数量不定（我只需要在最终结果中使用其中的一些选项）。

这几乎正是我需要做的，但它没有考虑历史数据，并假设变量对于 User_ID 是唯一的，在我的情况下，user_ID 可能有 3 或 4 个相同的变量，我需要最新的。参见> MySQL 数据透视表

| UUID   |UUID_User |Variable  |Value     |DateSet              |
|--------|----------|----------|----------|---------------------|
| X123Y  |123XX12   |FirstName |Jane      | 2011-07-09 14:13:12 |
| X126Y  |123XX12   |LastName  |Jones     | 2011-07-09 14:13:12 |
| X173Y  |123XX62   |FirstName |Joe       | 2011-07-09 14:11:12 |
| X143Y  |123XX62   |LastName  |Smith     | 2011-07-09 14:11:12 |
| X129Y  |123XX12   |LastName  |Smith     | 2011-11-09 14:13:12 | << Jane Gets Married

将上述内容转换为（确保使用 jane 的最新姓氏条目）

|UUID_User |FirstName |LastName  |
|----------|----------|----------|
|123XX12   |Jane      |Smith     |
|123XX62   |John      |Smith     |

原文

I am trying to convert the following table into something the is a bit easier to query. The historical data is important so it cannot get thrown away and there is an indefinite number of options for Variable (I only need some of them in the final result).

This is almost exactly what I need to do but it doesn't account for the historical data and assumes that the Variables are unique to the User_ID in my case a user_ID may have 3 or 4 of the same variable and I need the newest one.
See > MySQL Pivot Table

| UUID   |UUID_User |Variable  |Value     |DateSet              |
|--------|----------|----------|----------|---------------------|
| X123Y  |123XX12   |FirstName |Jane      | 2011-07-09 14:13:12 |
| X126Y  |123XX12   |LastName  |Jones     | 2011-07-09 14:13:12 |
| X173Y  |123XX62   |FirstName |Joe       | 2011-07-09 14:11:12 |
| X143Y  |123XX62   |LastName  |Smith     | 2011-07-09 14:11:12 |
| X129Y  |123XX12   |LastName  |Smith     | 2011-11-09 14:13:12 | << Jane Gets Married

Transform the above into (making sure to use the newest lastname entry for jane)

|UUID_User |FirstName |LastName  |
|----------|----------|----------|
|123XX12   |Jane      |Smith     |
|123XX62   |John      |Smith     |

分享到QQ

分享到微博

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

发布评论

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

苏佲洛 2024-12-26 23:51:24

获取每个用户的最新条目是一个常见问题，通常在 Stack Overflow 上被标记为 greatest-n-per-group。我建议为此创建一个视图，尽管这并不是绝对必要的。

CREATE VIEW LatestKeyValue AS
 SELECT k1.*
 FROM KeyValue AS k1
 LEFT OUTER JOIN KeyValue AS k2
  ON (k1.UUID_User, k1.Variable) = (k2.UUID_User, k2.Variable) 
  AND k1.DateSet < k2.DateSet
 WHERE k2.DateSet IS NULL

然后，您可以通过几种不同的方式针对您需要的每个变量进行旋转，如下所示：

SELECT UUID_User, 
  MAX(CASE Variable WHEN 'FirstName' THEN Value END) AS FirstName,
  MAX(CASE Variable WHEN 'LastName' THEN Value END) AS LastName
FROM LatestKeyValue
GROUP BY UUID_User

Getting the most recent entry per user is a common problem that is often tagged greatest-n-per-group on Stack Overflow. I suggest creating a VIEW for this, though it isn't strictly necessary.

CREATE VIEW LatestKeyValue AS
 SELECT k1.*
 FROM KeyValue AS k1
 LEFT OUTER JOIN KeyValue AS k2
  ON (k1.UUID_User, k1.Variable) = (k2.UUID_User, k2.Variable) 
  AND k1.DateSet < k2.DateSet
 WHERE k2.DateSet IS NULL

Then you can pivot that for each variable you need in a couple of different ways such as the following:

SELECT UUID_User, 
  MAX(CASE Variable WHEN 'FirstName' THEN Value END) AS FirstName,
  MAX(CASE Variable WHEN 'LastName' THEN Value END) AS LastName
FROM LatestKeyValue
GROUP BY UUID_User

回复收藏 0 原文

~没有更多了~