--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)
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)
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) 注意截位的处理
只是把括号去掉了吗?
又报了这些……疯了……
消息 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.
消息 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.刚才少发了一个报错
谢谢,单独能查询出来。一加上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.
转成字符
ltrim(CAST(SUBSTRING(FS.LineId , 0, PATINDEX('%[A-Z]%', FS.LineId ) ) AS INT))
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)