mysql - 内连接 值同表

Question

我的表有如下条目。其中对象 id 相同。我需要获取该特定 object_id 具有“HR”/“Infra”的对象 id 的其他值。

--------------------------------------------------
|id     | field_id  | object_id     | value_text
----------------------------------------------------    
|182    |   11      | 149           | HR
|183    |   13      | 149           | Learning Management   
|184    |   11      | 150           | HR 
|185    |   13      | 150           | Compensation & Benefits   
|186    |   11      | 151           | HR 
|187    |   13      | 151           | Leave Management  
|188    |   11      | 152           | HR 
|189    |   13      | 152           | Compensation & Benefits   
|190    |   11      | 153           | Infra 
|191    |   13      | 153           | Hardisk   
|192    |   11      | 154           | Infra 
|193    |   13      | 154           | Software  
|194    |   11      | 155           | Infra
|195    |   13      | 155           | Network   
|196    |   11      | 156           | HR 
|197    |   13      | 156           | Position Management   
|198    |   11      | 139           | HR 
|199    |   13      | 139           | Compensation & Benefits   
|200    |   11      | 157           | Infra 
|201    |   13      | 157           | Network       

查询:

SELECT d.value_text 
FROM dynamic d 
INNER JOIN dynamic dv ON d.object_id = d.object_id AND d.field_id = "13" 
                     AND dv.field_id = "11" AND dv.value_text = "HR"

所需输出:

--------------
| value_text |
--------------
| Learning Management 
| Compensation & Benefits
| Position Management

第二个查询:

SELECT d.value_text 
FROM dynamic d 
INNER JOIN dynamic dv ON d.object_id = d.object_id AND d.field_id = "13" 
                     AND dv.field_id = "11" AND dv.value_text = "Infra"

所需输出:

--------------
| value_text |
--------------
| Network 
| Software
| Hardisk

如何修改查询以获取详细信息。 .

Answer 1

您想合并这两个查询吗？

那么你可以这样做:

SELECT d.value_text FROM dynamic d 
INNER JOIN dynamic dv ON d.object_id = d.object_id AND
d.field_id = "13" AND dv.field_id = "11" 
AND (dv._obejct_id = "Infra" OR dv._obejct_id = "HR")

我希望这是您所需要的。