MySQL:选择条件适用于多行的记录 [英] MySQL : Select records with conditions that applies to multiple rows

问题描述

我有一张桌子:

详细信息

--------------------------------------------------------
ID    |   PARENT_ID    |    DATA_KEY     |   DATA_VALUE
======================================================== 
1     |      1         |     Guitar      |      4
--------------------------------------------------------
2     |      1         |     Radio       |      2
--------------------------------------------------------
3     |      1         |     Tv          |      2
--------------------------------------------------------
4     |      1         |     Drum Kit    |      3
--------------------------------------------------------
5     |      2         |     Guitar      |      4
--------------------------------------------------------
6     |      2         |     Radio       |      2
--------------------------------------------------------
7     |      2         |     Tv          |      2
--------------------------------------------------------
8     |      2         |     Drum Kit    |      3
--------------------------------------------------------
9     |      3         |     Guitar      |      1
--------------------------------------------------------
10    |      3         |     Radio       |      2
--------------------------------------------------------
11    |      3         |     Tv          |      2
--------------------------------------------------------
12    |      3         |     Drum Kit    |      3
--------------------------------------------------------

如何在mysql中选择满足以下条件的唯一的PARENT_ID:

How do i select in mysql distinct PARENT_ID that satisfies the below conditions:

1. DATA_KEY ='吉他'和DATA_VALUE ='4'

1. DATA_KEY ='Radio'和 DATA_VALUE ='2'

1. DATA_KEY = 'Radio' and DATA_VALUE = '2'

查询的预期输出应为

-------------
PARENT_ID
=============
  1
-------------
  2
-------------

因为只有PARENT_ID 1和2具有'吉他'='4'和'无线电'='2'

Since only PARENT_ID 1 and 2 has 'Guitar' = '4' and 'Radio' = '2'

推荐答案

SELECT DISTINCT a.parent_id 
FROM details AS a
JOIN details AS b ON a.parent_id=b.parent_id
WHERE a.data_key='Guitar' AND a.data_value='4' AND
      b.data_key='Radio' AND b.data_value='2';

这篇关于MySQL:选择条件适用于多行的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！