Mysql多次选择COL以获取不同的值 [英] Mysql Select COL multiple times for different values

问题描述

这是表结构:

ID PID KEY VALUE
1 2 CITY NEW YORK
2 2 COUNTRY UNITED STATES
3 2 STATE NEW YORK
4 1 CITY NEW JERSEY

，依此类推. 我想要的是获取/过滤

and so on.. what I want is to get/filter the Result like

Where (KEY = CITY and VALUE = NEW YORK) 
      AND (key = country AND VALUE= UNITED STATES)

那将返回空行！

推荐答案

您需要一个自我JOIN.

SELECT t1.*
FROM my_table t1
INNER JOIN my_table t2 ON t1.pid = t2.pid
WHERE t1.key = 'city' and t1.value = 'new york'
  AND t2.key = 'country' and t2.value = 'united states';

这篇关于Mysql多次选择COL以获取不同的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！