SQL Query根据另一行的结果选择一行

I included a screenshot of a demo entry to the table. I know how to write an SQL query to select data when all the information is on the same row, but this presents a new challenge to me. I want to select the meta_value result to a meta_key for a user_id only if pw_user_status has the value of approved. I'm not sure how to write this kind of query.

我在表格中添加了一个演示条目的屏幕截图。我知道当所有信息都在同一行时，如何编写SQL查询来选择数据，但这给我带来了新的挑战。我想仅在pw_user_status具有approved值时才为meta_key选择meta_value结果。我不知道如何编写这种查询。

Source data:

来源数据：

1 个解决方案

#1

You can use a subquery which identifies users having approved status, and then select certain key/value pairs for those users:

您可以使用子查询来标识具有已批准状态的用户，然后为这些用户选择某些键/值对：

SELECT meta_key, meta_value
FROM yourTable t1
WHERE t1.meta_key IN ('nickname', 'first_name', ...) AND
     EXISTS (SELECT 1 FROM yourTable t2
             WHERE t2.user_id = t1.user_id AND
                   t2.meta_key = 'pw_user_status' AND
                   t2.meta_value = 'approved')

Demo here:

在这里演示：

Rextester