WITH Liang AS
(
SELECT *,PATH = CONVERT(varchar(MAX),CategoryName),level = 1
FROM BT_Category AS A
WHERE NOT EXISTS(SELECT * FROM BT_Category WHERE A.FCategoryCode = CategoryCode and FCategoryCode <> '0')
UNION ALL
SELECT A.*,CONVERT(varchar(MAX),B.PATH + '->' + A.CategoryName),level+1
FROM BT_Category AS A
JOIN Liang AS B
ON A.FCategoryCode = B.CategoryCode and b.FCategoryCode <> '0')
SELECT TOP 1 WITH TIES * FROM Liang AS A
ORDER BY level DESC
from BT_DiseasesBase LEFT JOIN
(
WITH Liang AS
(
SELECT *,PATH = CONVERT(varchar(MAX),CategoryName),level = 1
FROM BT_Category AS A
WHERE NOT EXISTS(SELECT * FROM BT_Category WHERE A.FCategoryCode = CategoryCode
and FCategoryCode <> '0')
UNION ALL
SELECT A.*,CONVERT(varchar(MAX),B.PATH + '->' + A.CategoryName),level+1
FROM BT_Category AS A
JOIN Liang AS B
ON A.FCategoryCode = B.CategoryCode and b.FCategoryCode <> '0')
SELECT TOP 1 WITH TIES * FROM Liang AS A
ORDER BY level DESC
)AS TableTemp on BT_DiseasesBase.CategoryCode = TableTemp.CategoryCode我这样写 报错
消息 156,级别 15,状态 1,第 4 行
关键字 'WITH' 附近有语法错误。
消息 319,级别 15,状态 1,第 4 行
关键字 'with' 附近有语法错误。如果此语句是公用表表达式或 xmlnamespaces 子句,那么前一个语句必须以分号结尾。
消息 102,级别 15,状态 1,第 19 行
')' 附近有语法错误。
(
SELECT *,PATH = CONVERT(varchar(MAX),CategoryName),level = 1
FROM BT_Category AS A
WHERE NOT EXISTS(SELECT * FROM BT_Category WHERE A.FCategoryCode = CategoryCode
and FCategoryCode <> '0')
UNION ALL
SELECT A.*,CONVERT(varchar(MAX),B.PATH + '->' + A.CategoryName),level+1
FROM BT_Category AS A
JOIN Liang AS B
ON A.FCategoryCode = B.CategoryCode and b.FCategoryCode <> '0')select BT_DiseasesBase.DiseasesCode, TableTemp.PATH+'->'+BT_DiseasesBase.DiseasesName
from BT_DiseasesBase LEFT JOIN
(
SELECT TOP 1 WITH TIES * FROM Liang AS A
ORDER BY level DESC
)AS TableTemp on BT_DiseasesBase.CategoryCode = TableTemp.CategoryCode
(
SELECT *,PATH = CONVERT(varchar(MAX),CategoryName),level = 1
FROM BT_Category AS A
WHERE NOT EXISTS(SELECT * FROM BT_Category WHERE A.FCategoryCode = CategoryCode
and FCategoryCode <> '0')
UNION ALL
SELECT A.*,CONVERT(varchar(MAX),B.PATH + '->' + A.CategoryName),level+1
FROM BT_Category AS A
JOIN Liang AS B
ON A.FCategoryCode = B.CategoryCode and b.FCategoryCode <> '0'
)
select BT_DiseasesBase.DiseasesCode, TableTemp.PATH+'->'+BT_DiseasesBase.DiseasesName
from BT_DiseasesBase Right JOIN
(
SELECT TOP 1 WITH TIES * FROM Liang AS A
ORDER BY level DESC
)AS TableTemp on BT_DiseasesBase.CategoryCode = TableTemp.CategoryCode为什么 我 把这个放进视图 就报错 ~!