Question

I am creating a select query with union of three tables...... like this

select a as A,b as B c as C where c = x union
select b as A,d as B e as C where e = y and d = a union
select f as A,g as B,h as C

和质询的结果一样:

A   B   C
===========
1   abc ...
55  def ...
1   sas ...

so I want to have a column that count the number of row, just to prevent the repetition of identifier. Somthing like this

Row  A   B   C
================
1    1   abc ...
2    55  def ...
3    1   sas ...

......

我的问题是如何能够做到这一点?

Answer 1

CREATE VIEW dbo.vname
AS
    SELECT [Row] = ROW_NUMBER() OVER (ORDER BY A), A, B, C FROM
    ( <UNION query here> ) AS x;

页: 1 不管怎样命令你照此办理。请注意,您需要在对船舶的外围查询中使用ER ,以保证按该顺序罗。

Answer 2

你可以这样利用:

SELECT ROW_NUMBER() OVER (ORDER BY A,B,C) AS RowNo, *
FROM
(
select a as A,b as B c as C where c = x 
union
select b as A,d as B e as C where e = y and d = a 
union
select f as A,g as B,h as C
) x

Answer 3

你们可以用共同的表格来表达这一点:

WITH unionTable
AS
(
    select a as A, b as B, c as C where c = x union
    select b as A, d as B, e as C where e = y and d = a union
    select f as A, g as B, h as C
)
SELECT ROW_NUMBER() OVER (ORDER BY A) AS RowNumber, * 
FROM unionTable

友情链接