查询在通过Union ALL选择结果时获取连续的行号 [英] query for getting continuous row number when selecting the results by Union ALL

问题描述

我把查询写成下面通过使用得到所需的结果，但Sno列

值不连续，例如：sno获得第一次查询的结果1到50然后再次使用第二个查询获得结果1到100但现在我需要sno为1到150个连续数字的结果

Hi,




I written the query as below by using that am getting the required results but the Sno column
values are not continuous eg:sno is getting results 1 to 50 for first query and then again it's getting the results 1 to 100 using second query but now i need sno as 1 to 150 results with continuous number

SELECT ROW_NUMBER() OVER (ORDER BY id desc ) Sno, Id,Title,  from Job as c WITH (NOLOCK)
where  (CountryID=108 and CompanyName='dell')
group by Id, Title, 
union all
SELECT ROW_NUMBER() OVER (ORDER BY id desc ) Sno, Id,Title, from Job as c WITH (NOLOCK)
where  (CountryID!=108 and CompanyName='dell')
group by Id, Title

请帮帮我怎样才能得到连续数

Please help me how can i get the continuous number

推荐答案

看看t示例查询：

Have a look at sample query:

SELECT ROW_NUMBER() OVER (ORDER BY ...) AS Sno
FROM (
    SELECT
    ...
    UNION ALL
    SELECT 
    ...
) AS T
ORDER BY Sno

注意：in您尝试以这种方式准确获取数据的示例：

Note: in your example you're trying to get data exactly in this way:

WHERE (CountryID=108 OR C***ryID!=108) AND CompanyName='dell'

从逻辑的角度来看，这是个糟糕的主意。更好地使用查询，例如：

From logical point of view, it's poor idea. Better use query like:

SELECT ROW_NUMBER() OVER (ORDER BY id desc ) Sno, Id,Title
FROM Job as c WITH (NOLOCK)
WHERE CompanyName='dell'

如果您想添加自定义订单，请使用以下查询：

In case you want to add custom order, use query like this:

SELECT ROW_NUMBER() OVER (ORDER BY id desc ) Sno, Id,Title
FROM Job as c WITH (NOLOCK)
WHERE CompanyName='dell'
ORDER BY CASE WHEN CoutryID=108 THEN 0 ELSE CountryID END

[/ EDIT]

[/EDIT]

这篇关于查询在通过Union ALL选择结果时获取连续的行号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！