How could I simplify this mysql statement?

I use follow mysql statement to get some info from mysql via php.

( SELECT * 
    FROM mytable 
   WHERE qid NOT IN ({$used['used']}) 
     AND level = 1 
ORDER BY RAND() 
   LIMIT 5) 
UNION 
( SELECT * 
    FROM app_mytable _qt 
   WHERE qid NOT IN ({$used['used']}) 
     AND level = 2 
ORDER BY RAND() 
   LIMIT 5) 
UNION 
( SELECT * 
    FROM app_mytable _qt 
   WHERE qid NOT IN ({$used['used']}) 
     AND level = 3 
ORDER BY RAND() 
   LIMIT 5) 
UNION 
( SELECT * 
    FROM app_mytable _qt 
   WHERE qid NOT IN ({$used['used']}) 
     AND level = 4 
ORDER BY RAND() 
   LIMIT 5)

$used['used'] is a set of开发者_运维问答 qid that likes 23,31,653,147,146,134,6.....

How could I simplify this mysql statement?

You can use user variables to keep running totals by groups. This is untested but something like the following should work:

select *, 
       @running:=@previous:=NULL
  from (
         select *,
                @running:=if(@previous=inside.level,@running,0)+1 as TOTAL,
                @previous:=inside.level
           from (
                  select * 
                    from mytable
                   where qid NOT IN ({$used['used']})
                   order by level, rand() 
                ) as inside
       )as outside
 where TOTAL < 5;

Some dynamic sql can eliminate the copy pasting for ya... that way you can easily extend this out for as many levels as you like, just mod the max_level.

DECLARE current_level INT;  SET current_level = 1;
DECLARE max_level INT; SET max_level = 4;
DECLARE full_sql VARCHAR(1000); SET full_sql = '';
DECLARE base_sql VARCHAR(1000); SET base_sql = '(SELECT * FROM mytable WHERE qid NOT IN ({$used['used']}) AND level = ? ORDER BY RAND() limit 5) ';

WHILE current_level <= max_level
    SET full_sql = CONCAT(full_sql, REPLACE(base_sql, '?', current_level));

    IF current_level < max_level THEN
      SET full_sql = CONCAT(full_sql, ' UNION ');
    END IF
    SET current_level = current_level + 1;
END WHILE

PREPARE s1 FROM full_sql;
EXECUTE s1;
DEALLOCATE PREPARE s1;