MySQL选择查询条件 [英] MySQL select query conditions

问题描述

我有以下sql表+数据:

I have the following sql table + data:

CREATE TABLE IF NOT EXISTS `job_requires` (  
   `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,  
   `Job_id` bigint(20) unsigned NOT NULL,
   `Group_Index` int(11) NOT NULL,  
   `Field_id` bigint(20) unsigned NOT NULL,  
   `Field_Value` int(11) NOT NULL,
   PRIMARY KEY (`id`)
) 

INSERT INTO `job_requires` 
    (`id`, `Job_id`, `Group_Index`, `Field_id`, `Field_Value`) 
VALUES
    (1, 7, 1, 11, 50),
    (2, 7, 1, 14, 50),
    (3, 7, 1, 11, 59),
    (4, 7, 2, 14, 1),
    (5, 7, 2, 11, 2),
    (6, 8, 1, 14, 55),
    (7, 8, 1, 11, 50),
    (8, 8, 1, 14, 59),
    (9, 8, 2, 11, 60),
    (10, 8, 2, 14, 61);

我需要通过基于"Field_id" +"Field_Value"字段进行过滤来获取所有"Job_id". 我希望查询像这样:

I need to get all 'Job_id' by filtering that based on the 'Field_id'+'Field_Value' fields. I want the query to be somthing like:

SELECT job_requires.Job_id 
FROM job_requires 
WHERE (Field_id=11 AND Field_Value=50) AND (Field_id=14 AND Field_Value=1)

我的问题是我希望查询也基于 Group_Index 字段: 仅当我上一个查询中的"Job_id"同时具有两个条件，并且这些条件具有相同的 Group_Index 时，才应在查询结果中.

My problem is that I want query to be based also on the Group_Index field: Only if 'Job_id' in my previous query have both conditions, and those conditions have the same Group_Index It should be in the query's result.

更新:我不知道查询将包含多少个过滤器，也可能是:

UPDATE: I don't know how many filters the query will have, it could be also:

SELECT job_requires.Job_id 
FROM job_requires 
WHERE (Field_id=11 AND Field_Value=50) AND (Field_id=14 AND Field_Value=1) AND (Field_id=16 AND Field_Value=56) AND (Field_id=12 AND Field_Value=26)

我希望你能理解我的问题，因为我的英语不太好:(

I hope you understand my problem, because my english is not great :(

非常感谢您

推荐答案

SELECT j1.Job_id
    FROM job_requires j1
        INNER JOIN job_requires j2
            ON j1.Job_id = j2.Job_id
                AND j1.Group_Index = j2.Group_Index
    WHERE j1.Field_id = 11 AND j1.Field_Value = 50
      AND j2.Field_id = 14 AND j2.Field_Value = 1

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