主页 / dba / 问题 / 209989
Accepted
JOSEFtw
Asked: 2018-06-19 23:30:33 +0800 CST

多行的where子句，GROUP BY

  • 772

我有以下表结构

AccountID   Property            Value
123456      Status              Active
123456      City                Los Angeles
123456      RegistrationDate    2018-05-11 11:30:14.000
543210      Status              Active
543210      City                Las Vegas
543210      RegistrationDate    2018-05-11 11:13:14.000
888888      Status              Inactive
888888      City                Toronto
888888      RegistrationDate    2015-05-12 11:13:14.000

我希望能够选择 Property == Status 和 Value == Active And Property == RegistrationDate 和 Value == 2018-05-11 11:30:14.000 的所有行

我想我需要做某种 GROUP BY 然后选择那个聚合但我无法理解它（我的 sql 真的生锈了）。

我想要上述“查询”的以下输出

AccountID    Status    RegistrationDate
123456       Active    2018-05-11 11:30:14.000
543210       Active    2018-05-11 11:30:14.000

如果所有的列都存在于同一行，我会写这样的：

SELECT AccountID FROM Property
WHERE Status = 'Active'
AND RegistrationDate = '2018-05-11 11:30:14.000'
sql-server sql-server-2016

2 个回答

  1. Best Answer

    该设计模式称为 EAV（实体属性值）。这种设计通常需要一个GROUP BY或多个连接的查询：

    SELECT
      a.AccountID               
FROM 
      tbl AS a
      JOIN tbl AS b
      ON a.AccountID = b.AccountID
WHERE
      a.Property = 'Status' 
  AND a.Value    = 'Active'

  AND b.Property = 'RegistrationDate' 
  AND b.Value    = '2018-05-11 11:30:14.000' ;
    • 10

  2. 另一种表达方式是使用PIVOT将 EAV 结构转换为表格形式。鉴于：

    DROP TABLE IF EXISTS dbo.EAV;

CREATE TABLE dbo.EAV
(
    AccountID integer NOT NULL,
    Property varchar(30) NOT NULL,
    Value sql_variant NULL
);

INSERT dbo.EAV
    (AccountID, Property, [Value])
VALUES
    (123456, 'Status', CONVERT(sql_variant, 'Active')),
    (123456, 'City', CONVERT(sql_variant, 'Los Angeles')),
    (123456, 'RegistrationDate', CONVERT(sql_variant, CONVERT(datetime, '2018-05-11 11:30:14.000', 121))),
    (543210, 'Status', CONVERT(sql_variant, 'Active')),
    (543210, 'City', CONVERT(sql_variant, 'Las Vegas')),
    (543210, 'RegistrationDate', CONVERT(sql_variant, CONVERT(datetime, '2018-05-11 11:13:14.000', 121))),
    (888888, 'Status', CONVERT(sql_variant, 'Inactive')),
    (888888, 'City', CONVERT(sql_variant, 'Toronto')),
    (888888, 'RegistrationDate', CONVERT(sql_variant, CONVERT(datetime, '2015-05-12 11:13:14.000', 121)));

    一个示例查询是：

    SELECT
    P.AccountID,
    P.[Status],
    P.City,
    P.RegistrationDate
FROM dbo.EAV AS E
PIVOT (MAX([Value]) 
    FOR Property IN 
    (
        [Status], 
        [City], 
        [RegistrationDate])
    ) AS P
WHERE 
    P.[Status] = CONVERT(sql_variant, 'Active')
    AND P.RegistrationDate = CONVERT(sql_variant, CONVERT(datetime, '2018-05-11 11:30:14.000', 121));

    结果：

    帐户ID | 状态 | 城市 | 注册日期   
--------: | :----- | :------------ | :-----------------
   123456 | 活跃 | 洛杉矶 | 2018 年 11 月 5 日 11:30:14

    db<>在这里摆弄

    • 6

相关问题