How do I intermerge multiple SELECT results?

First of all, I'm not familiar with SQL in depth, so this may be a beginner question.

I know how to select data ordered by Id: SELECT * FR开发者_如何学JAVAOM foo ORDER BY id LIMIT 100 as well as how to select a random subset: SELECT * FROM foo ORDER BY RAND() LIMIT 100.

I'd like to merge these two queries into 1 in a zip manner, choosing limit/2 from each (i.e. 50). For example:

0
85
1
35
2
38
3
19
4
...

I would like to avoid duplicates. The easiest way is probably to just add a WHERE id > 100/2 to the part of the query that retrieves randomly ordered rows.

Additional info: It is unknown how many rows exist.

To get the "zip-manner" merge add a generated rownumber to each query and use an union with order by rownnumber.
Use even numbers for one and odd numbers for the other query.

Try this for MySQL

SELECT 
  @rownum0:=@rownum0+2 rn, 
  f.* 
FROM ( SELECT * FROM foo ORDER BY id ) f, (SELECT @rownum0:=0) r
UNION
SELECT @rownum1:=@rownum1+2 rn, 
  b.* 
FROM ( SELECT * FROM bar ORDER BY RAND() ) b, (SELECT @rownum1:=-1) r
ORDER BY rn
LIMIT 100

This should be self-explicative but doesnt remove duplicates:

select @rownum:=@rownum+1 as rownum, 
      (@rownum-1) % 50 as sortc, u.id 
from (
        (select id from player order by id limit 50) 
        union all 
        (select id from player order by rand() limit 50)) u, 
     (select @rownum:=0) r 
order by sortc,rownum;

If you replace "union all" with "union", you remove duplicates but get less rows as a consequence.

This will deal with duplicates, does not restrict random numbers in the ids > 50, and always return 100 rows:

SELECT @rownum := @rownum + 1 AS rownum, 
       ( @rownum - 1 ) % 50   AS sortc, 
       u.id 
FROM   ((SELECT id 
         FROM   foo 
         ORDER  BY Rand() 
         LIMIT  50) 
        UNION 
        (SELECT id 
         FROM   foo 
         WHERE  id <= 100 
         ORDER  BY id)) u, 
       (SELECT @rownum := 0) r 
WHERE  @rownum < 100 
ORDER  BY sortc, 
          rownum DESC 
LIMIT  100; 

SELECT * FROM foo ORDER BY id LIMIT 50 UNION SELECT * FROM foo ORDER BY RAND() LIMIT 50 

If I understand your requirement correctly. UNION removes duplicates by itself