代码之家 › 专栏 › 技术社区 › abelenky

sql-通过查询重新排列表

sql

abelenky · 技术社区 · 15 年前

我继承了一张设计糟糕的桌子。看起来像:

    User  Field   Value
    -------------------
    1      name   Aaron
    1      email  aaron@company.com
    1      phone  800-555-4545
    2      name   Mike
    2      email  mike@group.org
    2      phone  777-123-4567
    (etc, etc)

我想通过查询以更合理的格式提取这些数据:

User  Name   Email              Phone
-------------------------------------------
1     Aaron  aaron@company.com  800-555-4545
2     Mike   mike@group.org     777-123-4567

我是一个sql新手,但我尝试过几种不同的group by查询, 一切都没有任何接近成功的东西。

有没有一种sql技术可以让这一切变得简单?

6 回复 | 直到 15 年前

RoboDev 15 年前

在我的工作中,很不幸有这样的数据库设计。但是这种设计比传统的数据库设计更适合我们,因为我们需要存储不同的记录,并且给了我们所需要的灵活性。我们使用的数据库存储了数百万条记录。

这将是使用mssql在大型数据库上运行查询的最快方式。这样就省去了很多可能代价高昂的连接。

DECLARE @Results TABLE
(
    UserID INT
    , Name VARCHAR(50)
    , Email VARCHAR(50)
    , Phone VARCHAR(50)
)

INSERT INTO @Results
    SELECT DISTINCT User FROM UserValues

UPDATE
    R
SET
    R.Name = UV.Value
FROM
    @Results R
INNER JOIN
    UserValues UV
    ON UV.User = R.UserID
WHERE
    UV.Field = 'name'

UPDATE
    R
SET
    R.Email = UV.Value
FROM
    @Results R
INNER JOIN
    UserValues UV
    ON UV.User = R.UserID
WHERE
    UV.Field = 'Email'

UPDATE
    R
SET
    R.Phone = UV.Value
FROM
    @Results R
INNER JOIN
    UserValues UV
    ON UV.User = R.UserID
WHERE
    UV.Field = 'Phone'


SELECT * FROM @Results

Community CDub 8 年前

这不是一个“设计糟糕的表”;实际上是一个实体属性值(eav)表。不幸的是,关系数据库是实现这种表的糟糕平台,否定了rdbms的大部分优点。用错误的铲子钉螺丝的常见情况。

但我认为这是可行的(基于 Marcus Adams' 回答,我认为不起作用(编辑:现在起作用了)

SELECT User1.Value AS name, User2.Value AS email, User3.Value AS phone
FROM Users User1
LEFT JOIN Users User2
  ON User2.User = User1.User AND User2.Field='email'
LEFT JOIN Users User3
  ON User3.User = User1.User AND User3.Field='phone'
WHERE User1.Field = 'name'
ORDER BY User1.User

编辑:从其他答案(左连接和on子句上的字段名)中得到了一些细节,现在有人知道如何将剩余部分放得更高一些吗?(但不是在第一个连接打开时,这太难看了),当然这无关紧要,因为查询优化器无论如何都会把它丑化回去。

Marcus Adams 15 年前

可以使用自联接:

SELECT User1.User, User1.Value as Name, User2.Value as Email,
  User3.Value as Phone
FROM Users User1
JOIN Users User2
  ON User2.User = User1.User
JOIN Users User3
  ON User3.User = User1.User
WHERE User1.Field = 'name' AND User2.Field = 'email' AND User3.Field = 'phone'
ORDER BY User1.User

我测试了这个查询,它工作了。

Dolph 15 年前

我相信这将建立你正在寻找的结果集。从那里,您可以创建视图或使用数据填充新表。

select user, name, email, phone from
(select user, value as name from table where field='name')
natural join
(select user, value as email from table where field='email')
natural join
(select user, value as phone from table where field='phone')

LukáÅ¡ Lalinský 15 年前

在mysql中,可以执行以下操作:

SELECT
    id,
    group_concat(CASE WHEN field='name' THEN value ELSE NULL END) AS name,
    group_concat(CASE WHEN field='phone' THEN value ELSE NULL END) AS phone,
    ...
FROM test
GROUP BY id

聚合函数实际上并不重要,只要每种类型只有一个字段。你也可以用 min() 或 max() 而是有同样的效果。

Community CDub 8 年前

的变体 Javier's answer ,这有我的投票权。

SELECT 
  UserName.name, UserEmail.email, UserPhone.phone
FROM 
  Users            AS UserName 
  INNER JOIN Users AS UserEmail ON UserName.User = UserEmail.User
    AND UserName.field = 'name' AND UserEmail.field = 'email'
  INNER JOIN Users AS UserPhone ON UserName.User = UserPhone.User
    AND UserPhone.field = 'phone'

使用 LEFT JOINs 如果不是所有属性都保证存在。综合指数超过 (User,Field) 可能会有好处。