Générer par programme générer des requêtes top n pour Microsoft SQL Server à partir d'une requête générale

J'ai un programme qui génère du SQL standard comme SELECT * FROM TEST ORDER BY X et le passe (sous forme de string) à une fonction avec un entier pour créer une requête limitée (également une string). Pour Oracle (contre lequel l'application a été développée), cette fonction renvoie SELECT * FROM ( sq ) WHERE ROWNUM <= n donnant:

 SELECT * FROM (SELECT * FROM TEST ORDER BY X) WHERE ROWNUM <= 10 

ce qui fonctionne.

Malheureusement, je ne peux pas comprendre comment écrire la fonction équivalente pour Microsoft SQL Server (je ne suis pas un expert MS SQL).

J'ai essayé:

 SELECT TOP 10 FROM (SELECT * FROM TEST ORDER BY X) 

mais cela conduit à:

 Error: Incorrect syntax near the keyword 'from'. 

Puis j'ai essayé:

 SELECT TOP 10 * FROM (SELECT * FROM TEST ORDER BY X) 

qui m'a gagné:

 Error: The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified. 

ce qui était frustrant puisque je spécifie TOP ….

J'ai essayé quelques autres variantes de fusils de chasse, notamment:

 SELECT TOP (10) * FROM (SELECT * FROM TEST ORDER BY X) SELECT TOP 10 * FROM (SELECT * FROM TEST ORDER BY X) SUBQUERY SELECT TOP 10 * FROM (SELECT * FROM TEST ORDER BY X) AS SUBQUERY SELECT * FROM (SELECT * FROM TEST ORDER BY X) WHERE ROW_NUMBER <= 10 

mais bien sûr, aucun d'eux ne travaille non plus.

N'y a-t-il aucun espoir?

Utilisez SET ROWCOUNT . Cela rend le travail très simple.

 SET ROWCOUNT 10 SELECT * FROM [TEST] ORDER BY [X] SET ROWCOUNT 0 

Votre fonction peut-elle simplement replace SELECT par SELECT TOP 10?

 SELECT TOP 10 * FROM TEST ORDER BY X 

Vous pouvez utiliser correctement les expressions CTE (Common Table Expressions) et les fonctions de classment ici.

 ; WITH CTE_RowNumbers AS ( SELECT *, RANK() OVER (ORDER BY X) AS ROWNUM FROM Users ) SELECT * FROM CTE_RowNumbers WHERE ROWNUM < 10; 

Il suffit de replace le 10 avec votre variable.

Cela fournira exactement les mêmes résultats dans MS SQL que vous obtiendriez d'une fonction ROWNUM dans Oracle.

S'il vous plaît noter: Le ROWNUM dans cet extrait de code est juste un alias, pas une fonction dans MSSQL et la puissance réelle se trouve dans l'utilisation des expressions de la table commune et des fonctions de classment utilisées.

UPDATE: CTE masqué – remplace le [VAR-Name] par vos variables

  ; WITH CTE_RowNumbers AS ( SELECT [VAR-ColumnList], RANK() OVER (ORDER BY [VAR-OrderByList]) AS ROWNUM FROM [VAR-Table] ) SELECT [VAR-ColumnList] FROM CTE_RowNumbers WHERE ROWNUM < [VAR-ResultSize]; 

Extrayez les variables d'input de votre requête en tant que telles:

SELECT [VAR-ColumnList] FROM [Table VAR] ORDER BY [VAR-OrderByList]

Vous aurez également besoin d'assigner une valeur à [VAR-ResultSize], qui par son apparence est constante et définie dans votre fonction?

Note: Plus de flexibilité est possible, si vous avez besoin d'append DESC ou ASC à la command par, il suffit d'étendre votre script et d'inclure cela comme une variable.

La solution la plus simple et la plus logique serait de placer votre ORDER BY dehors de la sous-sélection:

SELECT TOP 10 * FROM (SELECT * FROM TEST) as sub ORDER BY X

SQL Server s'attend à ce que TOP et ORDER soient dans la même étendue.

Même si vous ORDER BY sur la requête interne, si vous avez fait un TOP à l'extérieur, il ne serait pas garanti d'être dans le bon ordre.

Les sous-requêtes sont des sets, et sont donc non ordonnées, donc vous ne pouvez pas les utiliser avec ORDER BY sauf si vous utilisez également TOP .

Si vous souhaitez limiter le nombre de lignes renvoyées et ne vous souciez pas des lignes, vous pouvez simplement en sortir tout ORDER BY interne, s'il y en a un, puis fournir un alias pour la sous-requête.

Requête originale:

 SELECT * FROM TEST ORDER BY X 

Edité et emballé pour renvoyer 10 lignes:

 SELECT TOP 10 * FROM (SELECT * FROM TEST) AS EXPR 

Ou vous pouvez appliquer une clause TOP à la sous-requête:

 SELECT TOP 10 * FROM (SELECT TOP 10 * FROM TEST ORDER BY X) AS EXPR 

Malheureusement, il n'est pas garanti de renvoyer les mêmes résultats que:

 SELECT TOP 10 * FROM TEST ORDER BY X 

Pour cela, vous devez déplacer l'ORDER BY en dehors de la sous-requête:

 SELECT TOP 10 * FROM (SELECT * FROM TEST) AS EXPR ORDER BY X 

Vous pouvez également paramétrer le nombre de lignes à returnner:

 SELECT TOP (@nrows) * FROM (SELECT * FROM TEST) AS EXPR 

Avec SQL Server 2012, vous pouvez presque atteindre ce que vous searchz réellement, sauf dans ce cas, ORDER BY est requirejs:

 SELECT * FROM TEST ORDER BY X OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY 

Vous pourriez essayer une table temporaire:

 select * into #temp from test order by x select top 10 * from #temp where ... 

Je pense que c'est ce que vous cherchez (je suppose SQL Server 2005 et plus):

 SELECT * FROM (SELECT *,ROW_NUMBER() OVER (ORDER BY x) rownum FROM test) tmp WHERE rownum BETWEEN 1 AND 10 

Où «x» est la colonne à order par et «tester» la table à sélectionner. En outre, une colonne 'count' est souvent utile pour returnner, surtout si vous faites de la pagination:

 SELECT * FROM (SELECT *,COUNT(*) OVER() cnt,ROW_NUMBER() OVER (ORDER BY x) rownum FROM test) tmp WHERE rownum BETWEEN 1 AND 10 

Faites une chose qui est assez simple et directe: – >>

  1. Supprimer la command par clause et
  2. utilisez le mot-key 'AS' après la requête interne.

Par exemple: SELECT TOP 10 * FROM (SELECT * FROM TEST) COMME X