Mysql：如何检索具有相同ID但列中值不同的两行

debugcn 发表于 Dev

罗德里戈

如何在列和其他变量中检索具有相同ID和相同值的两行。这是表“数据”

+---------+----------------+-------------+
| post_id | meta_key       | meta_value  |
+---------+----------------+-------------+
| 1000    | payment_method | visa        |
| 1000    | other          | sometext    |
| 1000    | order_total    | 65.00       |
| 1000    | etc            | sometext2   |
| 1001    | payment_method | bacs        |
| 1001    | other          | sometext    |
| 1001    | order_total    | 105.00      |
| 1001    | etc            | sometext2   |
| 1002    | payment_method | visa        |
| 1002    | other          | sometext    |
| 1002    | order_total    | 28.00       |
| 1002    | etc            | sometext2   |
| ...     | ...            | ...         |
+---------+----------------+-------------+

如您所见，payment_method的值稳定，order_total可变。

我试过了：

SELECT * FROM'data'在哪里meta_key IN（'payment_method'，'order_total'）GROUP BY post_id，meta_key

输出

+---------+-----------------+------------+
| post_id | meta_key        | meta_value |
+---------+-----------------+------------+
| 1000    | payment_method  | visa       |
| 1000    | order_total     | 65.00      |
| 1001    | payment_method  | bacs       |
| 1001    | order_total     | 105.00     |
| 1002    | payment_method  | visa       |
| 1002    | order_total     | 28.00      |
| ...     | ...             | ...        |
+---------+-----------------+------------+

我只想通过post_id支付方式=签证及其相应的金额。

+---------+-----------------+------------+
| post_id | meta_key        | meta_value |
+---------+-----------------+------------+
| 1000    | payment_method  | visa       |
| 1000    | order_total     | 65.00      |
| 1002    | payment_method  | visa       |
| 1002    | order_total     | 28.00      |
| ...     | ...             | ...        |
+---------+-----------------+------------+

谢谢。

蒂姆·比格莱森（Tim Biegeleisen）

您可以使用透视逻辑来查找每个键所需的值。子查询被别名别名为d2post，将仅返回其付款方式为Visa的帖子。然后，我们将您的原始data表连接到该子查询，以仅限制您要查看的帖子。

SELECT
    d1.post_id,
    d1.meta_key,
    d1.meta_value
FROM data d1
INNER JOIN
(
    SELECT post_id
    FROM data
    GROUP BY post_id
    HAVING MAX(CASE WHEN meta_key = 'payment_method' THEN meta_value END) = 'visa'
) d2
    ON d1.post_id = d2.post_id
WHERE
    d1.meta_key IN ('payment_method', 'order_total')
ORDER BY
    d1.post_id,
    d1.meta_key DESC;