MySQL cannot condition the case statement column with where

I have a question about case and where in MySQL.
The following tables are available:

 project_id | item_id | value
----------+-------+-----
1         |67     |5
1         |68     |5
2         |67     |6
2         |68     |6

I'm having trouble with SQL asking for project_id with item_id 67 and item_id 68 and project_id with item_id 6 and above.

Horizontalize the vertical holding in the case statement as follows:

SELECT 
    project_id,
        MIN(CASE item_id)
            WHEN67 THE VALUE
        END)AS'c67',
        MIN(CASE item_id)
            WHEN68 THE VALUE
        END)AS'c68'
from
    results

Above, you can get the following:

project_id|c67|c68
----------+---+-----
1         |5  |5

Editor's Note: Exclude project_id as it cannot be retrieved

I wrote the following SQL because I thought I could get it by subquery of the above SQL.

SELECT 
*
from
    (SELECT) 
        project_id,
            MIN(CASE item_id)
                WHEN67 THE VALUE
            END)AS'c67',
            MIN(CASE item_id)
                WHEN68 THE VALUE
            END)AS'c68'
    from
        results
    GROUP BY project_id)AS temp
WHERE
    'c67'>=5AND'c68'>=5

However, it will be zero.

Can't I specify conditions for columns using case statements?

Thank you for your cooperation.

sql

2022-09-30 21:11

3 Answers

There was a problem where the column name was specified as a single quote in the last row.

In MySQL, the column name symbol is `(@+SHIFT).

Therefore, I think the WHERE clause is expected to behave as follows:

WHERE`c67`>=5 and `c68`>=5

Here's an aside.

I compared the two SQL's, but the SQL below was narrowed down to project_id by GROUP BY, but the SQL above was not narrowed down.
Therefore, I corrected the results of SQL execution in the line Editor's Note.

2022-09-30 21:11

Why don't you use HAVING instead of subqueries?

SELECT* FROM results;
=>
+------------+---------+-------+
| project_id | item_id | value |
+------------+---------+-------+
|          1 |      67 |     5 |
|          1 |      68 |     5 |
|          2 |      67 |     6 |
|          2 |      68 |     6 |
|          3 |      67 |     4 |
|          3 |      68 |     8 |
+------------+---------+-------+

SELECT 
    project_id,
        MIN(CASE item_id)
            WHEN67 THE VALUE
        END)AS'c67',
        MIN(CASE item_id)
            WHEN68 THE VALUE
        END)AS'c68'
from
    results
GROUP BY project_id
HAVING c67>=5AND c68>=5
=>
+------------+------+------+
| project_id | c67 | c68 |
+------------+------+------+
|          1 |    5 |    5 |
|          2 |    6 |    6 |
+------------+------+------+

2022-09-30 21:11

Separately resolved

SELECT a.project_id, a.value c67, b.value c68 
FROM(
    SELECT project_id, value FROM results WHERE item_id = 67 AND value > = 5
AS a INNER JOIN(
    SELECT project_id, value FROM results WHERE item_id = 68 AND value > = 5
AS bON a.project_id = b.project_id;

If the project_id and item_id are combined and unique, you can get the expected results in the SQL statement above.
http://sqlfiddle.com/#!9/1ca66/1

2022-09-30 21:11

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656