MySQL加入特定结果

Question

我正在尝试构建一个查询以从表单生成器中提取数据，我只能在另一个字段值等于特定值的情况下加入一个字段值。我的查询没有返回任何行。

users
------------------
|id | name       |
------------------
|40 | John       |
|45 | Michael    |
|47 | Bob        |
------------------    

data_table
----------------------------------------------------
|id  | submission | field_type | field_value       |
----------------------------------------------------
|1   | 12345      | user       | 40                |
|2   | 12345      | score      | 5                 |
|3   | 12345      | completed  | 1                 |
|4   | 23456      | user       | 45                |
|5   | 23456      | score      | 3                 |
|6   | 23456      | completed  | 0                 |
|7   | 45678      | user       | 47                |
|8   | 45678      | score      | 2                 |
|9   | 45678      | completed  | 1                 |
----------------------------------------------------

Desired result

---------------
|Name | Score |
---------------
|John | 5     |
|Bob  | 2     |
---------------

Select 
    u.name,
    dt2.field_value as score
from
    users u
left join
    data_table dt on u.id=dt.field_value and dt.field_type='user'
left join
    data_table dt2 on dt.submission=dt2.submission and dt2.field_type='score'
where
    (dt.field_type='completed' and dt.field_value=1)

http://sqlfiddle.com/#!2/d6e72f/3

Answer 1

你可以做类似的事情(如果你真的不能改变你的数据结构，这看起来......很奇怪)。

你将在 data_table 上有一个子查询，在该表上有两个自连接(因为你需要 3 个不同的行和条件)

select u.name, s.score
from users u
join (
      select dt.field_value as user_id, dt1.field_value as score
      from data_table dt
      join data_table dt1 on dt1.submission = dt.submission
      join data_table dt2 on dt2.submission = dt1.submission
      where dt.field_type='user' and 
            dt1.field_type = 'score' and 
            dt2.field_type='completed' and 
            dt2.field_value = 1
      ) s
    on s.user_id = u.id

参见 SqlFiddle