Comment sélectionner top 2, bottom 2 et 6 loggings randoms (pas dans Top 2 et Bottom 2) de la table en utilisant une requête de sélection SQL?
En MS SQL 2005/2008:
with cte as ( select row_number() over (order by name) RowNumber, row_number() over (order by newid()) RandomOrder, count(*) over() Total, * from sys.tables ) select * from cte where RowNumber <= 2 or Total - RowNumber + 1 <= 2 union all select * from ( select top 6 * from cte where RowNumber > 2 and Total - RowNumber > 2 order by RandomOrder ) tt
Remplacez sys.tables
par le nom de votre table et modifiez l' order by name
pour spécifier la condition de command pour le top 2 et le bottom 2.
Peut-être pas une seule déclaration de sélection, mais il peut être exécuté en un seul appel:
/* Top 2 - change order by to get the 'proper' top 2 */ SELECT * from table ORDER BY id DESC LIMIT 2 UNION ALL /* Random 6.. You may want to add a WHERE and random data to get the random 6 */ /* Old Statement before edit - SELECT * from table LIMIT 6 */ SELECT * from table t LEFT JOIN (SELECT * from table ORDER BY id DESC LIMIT 2) AS top ON top.id = t.id LEFT JOIN (SELECT * from table ORDER BY id DESC LIMIT 2) AS bottom ON bottom.id = t.id WHERE ISNULL(top.id ) AND ISNULL(bottom.id) ORDER BY RANDOM() LIMIT 6 UNION ALL /* Bottom 2 - change order by to get the 'proper' bottom 2 */ SELECT * from table ORDER BY id ASC LIMIT 2
Quelque chose dans ce sens. Fondamentalement, l' UNION All
est l'astuce.
En supposant que la "command" est par la colonne id:
select * from (select id, id from my_table order by id limit 2) t1 union select * from (select id, id from my_table where id not in ( select * from (select id from my_table order by id asc limit 2) t22 union select * from (select id from my_table order by id desc limit 2 ) t23) order by rand() limit 6) t2 union select * from (select id, id from my_table order by id desc limit 2) t3
EDIT: syntaxe fixe et requête testée – cela fonctionne