How can I write an if else if if query in MySQL when returning a variable number of rows

Question

How can I write an if else if if query in MySQL when returning a variable number of rows

I have three columns in a table:

score  status  No.
1,     2,      1
0,     1,      2
0,     0,      1

I need this to write an SQL C # alias:

rows = empty;

rows = "SELECT * FROM `table` WHERE score = 1"
if (rows.Count > 0) //at least one row
    return rows;

rows = "SELECT * FROM `table` WHERE status = 2"
if (rows.Count > 1) //more than one row 
    return row with MAX(No.) from rows; //ie MAX(No.) where status = 2

 return rows;

Hope I could be clear. In short, select from my table entries with a score of = 1, and if there is no such record, return the record where status = 2, and if there is more than one record with status = 2, return the record with the maximum value for No., where status = 2 (if there is no record at all with status = 2, return empty).

How can I write it in one query? It should be a good learning experience for me. Otherwise, I know to break up small queries and run them. And I can't go with stored procedures right now.

: WHERE, , . , . SELECT * FROM table WHERE score = 1 . / , . , , , score = 1. . - , score = 1. . , , , .

: , , :) , . :

, : @GordonLinoff's, @ZaneBiens, @ZaneBien , @Scen's, @JulienCh.'s ( 3 , , :))
, , score = 1 : @HannoBinder, @ShlomiNoach's, @ -. @Danielle ( ), , )
, , , MySQL.
@MatthewPK .

. , , , , , @Scen answer .

+5

sql mysql

nawfal 06 . '11 20:20

12

, .

, , , .

select *
from ((SELECT 'SCORE' as matchtype, t.*
       FROM `table` t
       WHERE score = 1
      ) union all
      (SELECT 'STATUS' as matchtype, t.*
       FROM `table` t join
            (select max(`No.`) as maxno
             from `table` 
             WHERE status = 2
           ) tsum
           on t.`No.` = tsum.maxno
       WHERE status = 2
      )
     ) t cross join
     (select count(*) as cnt
      from `table`
      where score = 1
     ) const
where matchtype = (case when const.cnt > 0 then 'SCORE' else 'STATUS' end)

. MatchType. , .

+5

Gordon Linoff 10 . '12 16:24

:

SELECT *
  FROM `your_table`
WHERE
  status = 2 OR score = 1 
ORDER BY
  if(score = 1, 1, 0) DESC, 
  if(status = 2, 1, 0) DESC,
  `No.` DESC
LIMIT 1;

:

No.
MySQL ( mysql)

/ KSiimson, . , .

, - ORDER BY, . FROM clause - OR.

+4

Shlomi Noach 10 . '12 11:16

, :

SELECT * FROM tbl WHERE score = 1 

UNION ALL

(
    SELECT a.* 
    FROM tbl a
    INNER JOIN
    (
        SELECT COUNT(1) AS s1exists FROM tbl WHERE score = 1
    ) b ON b.s1exists = 0
    WHERE a.status = 2
    ORDER BY a.no DESC
    LIMIT 1
)

:

, SELECT UNION , score= 1. SELECT ( no), status= 2 , , score= 1. , status= 2, - .

, :

, score= 1: SELECT ; SELECT ( status= 2).

, score= 1 , status= 2: SELECT ; SELECT ( , ).

, score= 1 , status= 2: SELECT ; SELECT , status= 2.

, score= 1, status= 2: ; , .

+3

Zane Bien 12 . '12 6:47

, , , :

SELECT * FROM `table`
WHERE score=1

union all

SELECT * FROM `table`
WHERE status=2 
and NOT EXISTS (SELECT * FROM `table` WHERE score=1)
and `No.` = (
             SELECT max(`No.`) FROM `table`
             WHERE status=2 
            )

+2

Julien Ch. 09 . '12 22:40

, No. :

select   * 
from     `table` 
where    status = 2 or score = 1 
order by if(score = 1, -2, if(status = 2, -1)), `No.` desc 
limit    1;

, , . -2, , -1, . No. .

. Shlomi Noach , , No. .

+1

siimsoni 10 . '12 4:02

:

-- If there is no row with Score 1, then the first SELECT will return nothing.
-- If there is a row with Score 1, then you will get it.
-- No row with Score 1. If there is one row with Status 2, then you will get it.
-- No row with Score 1. If there is more than one row with Status 2, then you will get the one with the highest No.

SELECT 1 AS ColumnOrder, Table.*
FROM   Table
WHERE  Score = 1
LIMIT  1
UNION ALL
SELECT   2 AS ColumnOrder, Table.* 
FROM     Table 
WHERE    Status=2 
ORDER BY ColumnOrder, No DESC
LIMIT    1

+1

Danielle Paquette-Harvey 12 . '12 19:19

, , :

/*
Query returns rows with score=1 when they exist or nothing
*/
select score, status, no from temp where score=1

UNION

/*
Query returns rows with status=2 when they exist and when there are no rows
 with score=1 or nothing otherwise.
*/
(

select t1.score, t1.status, t1.no from

( /* <subquery> */

(
select score, status, no from temp
where status=2 and (select max(score) from temp where score=1) is null
) as t1

join

(
select max(no) as maxNo from temp where status=2
) as t2

on t1.no=t2.maxNo

) /* </subquery> */

limit 1

)

, . , , ( , - = 1) . , @c :

set @c:=(select max(score) from temp where score=1);

:

/*
Query returns rows with score=1 when they exist or nothing
*/
select score, status, no from temp where score=1

UNION

/*
Query returns nothing when there are rows with score=1,
 rows with status=2 when they exist and when there are no rows with score=1, or
 nothing otherwise.

Does this by adding weights to rows.
Rows with score=1 have greater weights than rows with status=2.
Joins rows with greatest weights to rows with status=2.
*/
( /* <query> */

select t.score, t.status, t.no from
( /* <joins> */

/* Gets greatest weight */
(
select max(if(score=1, 2, if(status=2, 1, -1))) as w
from temp
) as t1

join

/*  Joined on rows with status=2 using assigned weights */
(
select score, status, no, if(score=1, 2, if(status=2, 1, -2)) as w
from temp
where status=2
) as t

on t.w=t1.w

join

/*  Joined on rows with greatest No. for rows with status=2 */
(
select max(no) as maxNo
from temp
where status=2
) as t2

on t.no=t2.maxNo

) limit 1 /* </joins> (Only returns one row with max No.) */

) /* </query> */

+1

Dave F 16 . '12 5:14

(?) .

, , ; , ,

WHERE,

, , group by. MySQL ,

select score, status, MAX(No) as No
from tbl 
group by score, status

"" No.

, select ,

select *
from ( [*] ) as tbl
where
   (score = 1)
OR (score != 1 AND status = 2)
OR /* ... more conditions of the above type... */
order by /* ... */
limit 1

([*] group ed select )

() order by score DESC, status DESC / limit 1 , , .

+1

JimmyB 16 . '12 17:39

IF EXISTS(Select * From `table` Where score = 1)
Begin
    Select * From `table` Where score = 1;
End
Else
Begin
    Select *
    From `table`
    Where status = 2
    Order by `No.` desc
    LIMIT 1
End

+1

Scen 16 . '12 19:54

, , ( WHERE), UNION.

UNION, , , ( UNION "" ). , DBMS ORDER BY X/LIMIT 1, MySQL :

SELECT a.*
FROM tbl a
CROSS JOIN (SELECT COUNT(1) AS score1cnt FROM tbl WHERE score = 1) b
CROSS JOIN (SELECT MAX(no) AS maxno FROM tbl WHERE status = 2) c
WHERE
    CASE WHEN b.score1cnt > 0 THEN a.score = 1
         WHEN b.score1cnt = 0 AND c.maxno IS NOT NULL THEN a.status = 2 AND a.no = c.maxno
         ELSE 0
    END

SQLFiddle Demo, .

+1

Zane Bien 16 . '12 19:58

SELECT SCORE, STATUS, NO from (
    SELECT * , RANK() OVER (PARTITION BY [STATUS] ORDER BY SCORE ASC, NO DESC) AS RANK
    FROM `table`
    WHERE  score = 1 OR [status] = 2 ) x
WHERE X.Rank = 1

0

user1166147 10 . '12 2:49

Scen · Accepted Answer · 2012-07-16T20:29:47+0000

Select *
From scores
Where score = 1
UNION
(
   Select *
   From scores
   Where status = 2 and not exists (Select * from scores where score = 1)
   Order By `No.` desc
   Limit 1
);

table scores.

SqlFiddle ( , ): http://sqlfiddle.com/#!2/6aac2/1/0

EDIT: SQlFiddle , .

How can I write an if else if if query in MySQL when returning a variable number of rows

:

More articles: