mysql - SQL 查询选择多行

Question

我有下表:

projects: id, name, language
          1 |test | php
          2 |test | java
          3 |hello| php


attrs:  id, name,      value,   tb1_id
        1  | status    | finish | 1
        2  | reference | 2      | 1

我需要一个查询来选择所有项目，这些项目具有对另一个项目的“引用”属性和“状态”完成。

示例输出:

id, name      
1 |test 

你能帮我吗？

Answer 1

您可以:

• 对连接表进行分组，并在 HAVING 子句中使用合适的聚合函数过滤此类组:

  SELECT   projects.id, projects.name
  FROM     projects
      JOIN attrs ON attrs.tb1_id = projects.id
  GROUP BY projects.id
  HAVING   SUM(attrs.name='reference')
       AND SUM(attrs.name='status' AND attrs.value='finish')
  

• 或者多次加入attrs:

  SELECT   p.id, p.name
  FROM     projects AS p
      JOIN attrs    AS r ON  r.tb1_id = p.id
                         AND r.name   = 'reference'
      JOIN attrs    AS s ON  s.tb1_id = p.id
                         AND s.name   = 'status'
  WHERE    s.value = 'finish'