对视图排序，去重，增加新行连续报错……求助……

[iori_bao]

先上SQL

(SELECT '*' AS LineId,'*' AS Sub)

UNION

(SELECT TOP 100 PERCENT FS.LineId,CAST(left(FS.LineId,len(FS.LineId)-2) AS INT) Sub 

FROM v_Pi_Bale FS ORDER BY CAST(left(FS.LineId,len(FS.LineId)-2) AS INT))

v_Pi_Bale 为一个视图，LineId列的格式为：1A1，2A1，11A1.... 为varchar类型
为了让LineId列按数字排序，结果去重复，而且要在查询结果的第一行增加一个"*"。

形成以上结果，然后就一直报错，Incorrect syntax near the keyword 'ORDER'.

实在不知道怎么写了，求高手帮助……

[水族杰纶]

--try SELECT '*' AS LineId,'*' AS Sub UNION SELECT TOP 100 PERCENT FS.LineId,CAST(left(FS.LineId,len(FS.LineId)-2) AS INT) Sub FROM v_Pi_Bale FS ORDER BY CAST(left(FS.LineId,len(FS.LineId)-2) AS INT)

[Mr_Nice]

SELECT TOP 100 PERCENT
        FS.LineId ,
        CAST(SUBSTRING(@i , 0, PATINDEX('%[A-Z]%', @i ) ) AS INT)  Sub
FROM    v_Pi_Bale FS
ORDER BY CAST(SUBSTRING(@i , 0, PATINDEX('%[A-Z]%', @i ) ) AS INT)

注意截位的处理

[iori_bao]

引用 1 楼 wufeng4552 的回复:

--try SELECT '*' AS LineId,'*' AS Sub UNION SELECT TOP 100 PERCENT FS.LineId,CAST(left(FS.LineId,len(FS.LineId)-2) AS INT) Sub FROM v_Pi_Bale FS ORDER BY CAST(left(FS.LineId,len(FS.LineId)-2) AS INT)

只是把括号去掉了吗？ 又报了这些……疯了…… 消息 4104，级别 16，状态 1，第 3 行 The multi-part identifier "FS.LineId" could not be bound. 消息 4104，级别 16，状态 1，第 3 行 The multi-part identifier "FS.LineId" could not be bound. 消息 4104，级别 16，状态 1，第 3 行 The multi-part identifier "FS.LineId" could not be bound.

[iori_bao]

引用 1 楼 wufeng4552 的回复:

--try SELECT '*' AS LineId,'*' AS Sub UNION SELECT TOP 100 PERCENT FS.LineId,CAST(left(FS.LineId,len(FS.LineId)-2) AS INT) Sub FROM v_Pi_Bale FS ORDER BY CAST(left(FS.LineId,len(FS.LineId)-2) AS INT)

消息 4104，级别 16，状态 1，第 1 行 The multi-part identifier "FS.LineId" could not be bound. 消息 4104，级别 16，状态 1，第 1 行 The multi-part identifier "FS.LineId" could not be bound. 消息 104，级别 16，状态 1，第 1 行 ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator. 刚才少发了一个报错

[iori_bao]

引用 2 楼 OrchidCat 的回复:

SELECT TOP 100 PERCENT
        FS.LineId ,
        CAST(SUBSTRING(@i , 0, PATINDEX('%[A-Z]%', @i ) ) AS INT)  Sub
FROM    v_Pi_Bale FS
ORDER BY CAST(SUBSTRING(@i , 0, PATINDEX('%[A-Z]%', @i ) ) AS INT)

注意截位的处理

谢谢，单独能查询出来。一加上union就也报错了。

SELECT '*' AS LineId,'*' AS Sub 
UNION
SELECT TOP 100 PERCENT
        FS.LineId ,
        CAST(SUBSTRING(FS.LineId , 0, PATINDEX('%[A-Z]%', FS.LineId ) ) AS INT)  Sub
FROM    v_Pi_Bale FS
ORDER BY CAST(SUBSTRING(FS.LineId , 0, PATINDEX('%[A-Z]%', FS.LineId ) ) AS INT)

错误信息： 消息 4104，级别 16，状态 1，第 1 行 The multi-part identifier "FS.LineId" could not be bound. 消息 4104，级别 16，状态 1，第 1 行 The multi-part identifier "FS.LineId" could not be bound. 消息 104，级别 16，状态 1，第 1 行 ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.

[水族杰纶]

CAST(SUBSTRING(FS.LineId , 0, PATINDEX('%[A-Z]%', FS.LineId ) ) AS INT) 转成字符 ltrim(CAST(SUBSTRING(FS.LineId , 0, PATINDEX('%[A-Z]%', FS.LineId ) ) AS INT))

[發糞塗牆]

视图内部不要用order by，如果非要用，用select top 99.999 percent * from xxx order by 这种格式

[iori_bao]

引用 6 楼 wufeng4552 的回复:

CAST(SUBSTRING(FS.LineId , 0, PATINDEX('%[A-Z]%', FS.LineId ) ) AS INT) 转成字符 ltrim(CAST(SUBSTRING(FS.LineId , 0, PATINDEX('%[A-Z]%', FS.LineId ) ) AS INT))

不行啊……还是报那个……

[iori_bao]

引用 7 楼 DBA_Huangzj 的回复:

视图内部不要用order by，如果非要用，用select top 99.999 percent * from xxx order by 这种格式

视图是甲方的……我们见不到……

[發糞塗牆]

select * from ( SELECT '*' AS LineId,'*' AS Sub UNION SELECT TOP 100 PERCENT FS.LineId,CAST(left(FS.LineId,len(FS.LineId)-2) AS INT) Sub FROM v_Pi_Bale FS)a ORDER BY CAST(left(FS.LineId,len(FS.LineId)-2) AS INT)