另一个表中的 SQL SELECT 条件

Question

我有 2 个相关的表：

messages
--------

mid subject
--- -----------------
1   Hello world
2   Bye world
3   The third message
4   Last one


properties
----------

pid mid name             value
--- --- ---------------- ----------- 
1   1   read             false
2   1   importance       high
3   2   read             false
4   2   importance       low
5   3   read             true
6   3   importance       low
7   4   read             false
8   4   importance       high

我需要使用 properties 表上的条件从 messages 获取数据。 例如：如果我有一个标准，例如返回未读（读=假）高优先级（重要性=高）消息，它应该返回

mid subject
--- -----------------
1   Hello world
4   Last one

如何使用SELECT子句（MySQL方言）获得这个？

Answer 1

在 SQL 中，WHERE 子句中的任何表达式一次只能引用一行。因此，您需要某种方法将属性表中的多行获取到一行结果中。您可以通过自连接来执行此操作：

SELECT ...
FROM messages AS m
JOIN properties AS pRead 
    ON m.mid = pRead.mid AND pRead.name = 'read'
JOIN properties AS pImportance 
    ON m.mid = pImportance.mid AND pImportance.name = 'importance'
WHERE pRead.value = 'false' AND pImportance.value = 'high';

这显示了使用 EAV antipattern< /a>.与使用传统属性相比，其中一个属性属于一列：

SELECT ...
FROM messages AS m
WHERE m.read = 'false' AND m.importance = 'high';

顺便说一句，@Abe Miessler 和 @Thomas 的答案都比您想要的匹配更多的 mid。它们匹配所有中读=假或重要性=高的中值。您需要将这些属性与等效的 AND 组合起来。

Answer 2

我相信下面的查询会起作用。
更新： @Gratzy 是对的，这个查询不起作用，看看我建议的结构更改。

SELECT DISTINCT m.id as mid, m.subject
FROM message as m
INNER JOIN properties as p
ON m.mid = p.mid
where (p.name = 'read' and p.value = 'false') or (p.name = 'importance' AND p.value = 'high')

不过，您的属性表的结构对我来说似乎有点偏离...

是否可以像这样构造表：

messages
--------

mid subject           Read      Importance
--- ----------------- --------- ------------
1   Hello world       false     3
2   Bye world         false     1
3   The third message true      1
4   Last one          false     3

importance
----------

iid importanceName
--- --------------
1   low
2   medium
3   high

并使用此查询：

SELECT m.id as mid, m.subject
FROM message as m
where m.read = false AND m.importance = 3

Answer 3

显然，您正在使用 EAV（实体属性值）模式。避免这种结构的众多原因之一是它使查询变得更加困难。但是，对于您给出的示例，您可以执行以下操作：

Select ...
From messages As M
Where Exists    (
                Select 1
                From Properties As P1
                Where P1.mid = M.mid
                    And P1.name = 'unread' And P1.value = 'false'
                )
    And Exists  (
                Select 1
                From Properties As P2
                Where P2.mid = M.mid
                    And P2.name = 'importance' And P2.value = 'high'
                )

更简洁的解决方案是：

Select ...
From messages As M
Where Exists    (
                Select 1
                From Properties As P1
                Where P1.mid = M.mid
                    And ((P1.name = 'unread' And P1.value = 'false')
                            Or (P1.name = 'importance' And P1.value = 'high'))
                Having Count(*) = 2
                )

Answer 4

Select m.mid, m.subject
from properties p 
inner join properties p1 on p.mid = p1.mid
inner join messages m on p.mid = m.mid
where
p.name = 'read' 
and p.value = 'false'
and p1.name = 'importance'
and p2.value = 'high'

我更喜欢将过滤条件放在 where 子句中，并将联接保留在两个表中并且是联接的实际条件的元素。

Answer 5

另一种方法可能是（未经测试）使用派生表来保存所有消息必须满足的标准，然后使用双 NOT EXISTS 标准关系划分技术

SELECT mid,
       subject
FROM   messages m
WHERE  NOT EXISTS
       ( SELECT *
       FROM    ( SELECT 'read' AS name,
                       'false' AS value

               UNION ALL

               SELECT 'importance' AS name,
                      'high'       AS value
               )
               c
       WHERE   NOT EXISTS
               (SELECT *
               FROM    properties P
               WHERE   p.mid  = m.mid
               AND     p.name =c.name
               AND     p.value=c.value
               )
       )

Answer 6

如果您想保留现有的数据模型，请遵循 Bill Karwin 的第一个建议。使用此 select 子句运行它以了解它在做什么：

select m.*, r.value as read, i.value as importance
from message m
join properties r
    on r.mid = m.mid and r.name = 'read'
join properties i
    on i.mid = m.mid and i.name = 'importance'
where r.value = 'false' and i.value = 'high';

但是如果您这样做，则应该设置一些约束以避免存储和检索错误数据：

1. message(mid) 上的唯一索引和 message(mid) 上的唯一索引属性（pid），我相信你已经有了。
2. 属性（mid，name）上的唯一索引，以便每个属性只能为一条消息定义一次 - 否则您可能会从查询中获得重复的结果。通过允许两个连接的索引访问，这也将有助于提高查询性能。