select the I'd from the database using CONDITIONS IN SQL-CodePudding

select the id from the table using below conditions

please find the sample data in comment

Query to select the Id from the table using the following conditions. It should satisfy all three conditions

If year is 2018 and values greater than or equal to 2000

If year is 2019 and values greater than or equal to 4000

If year is 2020 and values greater than or equal to 6000

Example id 1 and 2 have three years and met the limit conditions

The output like Id 1 2

Create table A(id int ,year int,value int);
Insert into A values(1, 2018, 2000);
Insert into A values(1, 2019, 4000); 
Insert into A values(1, 2020, 6000); 
Insert into A values(2, 2018, 3000); 
Insert into A values(2, 2019, 4542); 
Insert into A values(2, 2020, 8000); 
Insert into A values(3, 2019, 3000); 
Insert into A values(3, 2020, 7000); 
Insert into A values(4, 2018, 1000); 
Insert into A values(4, 2019, 4564); 
Insert into A values(4, 2020, 7035);

CodePudding user response：

Is this what you are looking for? (question is unfortunatly a bit unclear about desired output):

select year, GROUP_CONCAT(id) as `ids`
from A 
where year=2018 and value>=2000
union all
select year, GROUP_CONCAT(id)  as `ids`
from A 
where year=2019 and value>=4000
union all 
select year, GROUP_CONCAT(id)  as `ids`
from A 
where year=2020 and value>=6000;

output:

year	ids
2018	1,2
2019	1,2,4
2020	1,2,3,4

DBFIDDLE

When a space is needed, in stead of a ,, you should change the GROUP_CONCAT(), and add SEPARATOR ' ', see this DBFIDDLE

CodePudding user response：

As for your question as it currently stands:

Query to select the Id from the table using the following conditions. It should satisfy all three conditions If year is 2018 and values greater than or equal to 2000 If year is 2019 and values greater than or equal to 4000 If year is 2020 and values greater than or equal to 6000

    SELECT id FROM a 
        WHERE year = 2018 AND value >= 2000 
            OR year = 2019 AND value >= 4000 
            OR year = 2020 AND value >= 6000;

Edited: as per your comments you want only Id 1 and 2:

    SELECT distinct id 
        FROM a 
        WHERE id in (SELECT id from a WHERE year = 2018 AND value >= 2000) 
            AND id in (SELECT id from a WHERE year = 2019 AND value >= 4000) 
            AND id in (SELECT id from a WHERE year = 2020 AND value >= 6000)