日期:2014-05-18  浏览次数:20593 次

熟悉SQL的來
大虾们能不能帮忙看一下 哪错了 报错'接近關鍵字 'select' 之處的語法不正確。'
with tempbl as(select ROW_NUMBER() over (order by HomepagePromote.AddedID asc) as Row,* from (  
SELECT HomepagePromote.AddedID --上架編號
,DomaineData.DomaineOriName --酒莊原文
,VinData.VinOriName --酒款原文
,DomaineData.DomaineTraName --酒莊繁文
,VinData.VinTraName --酒款繁文
,VintageData.Vintage --年份
,ProdItems.SellPrice --售價
,ProdItems.VinID --酒款編號
,ProdItems.VintageID --年份編號
,dbo.f_GetCateLvlAll (VinData.VinID,'-','1') AS CateLvl --產區層級
,SD1.DataTraDesc AS STYPE --類型
,SD2.DataOriDesc AS VUL --容量
,HomepagePromote.PromoteDesc AS VinTraDesc --酒款簡介
,VintageCap.BtFilePath --酒瓶圖
FROM HomepagePromote
left JOIN ProdItems ON HomepagePromote.AddedID = ProdItems.AddedID
left JOIN VintageCap ON ProdItems.VintageID = VintageCap.VintageID
AND
ProdItems.VulID = VintageCap.VulID
AND
ProdItems.VinID = VintageCap.VinID
left JOIN VintageData ON ProdItems.VintageID = VintageData.VintageID
left JOIN VinData ON ProdItems.VinID = VinData.VinID
left JOIN DomaineData ON VinData.DomaineID = DomaineData.DomaineID
left JOIN SysBasicData AS SD1 ON VinData.VinType = SysBasicData.DataCode
left JOIN SysBasicData AS SD2 ON ProdItems.VulID = SysBasicData.DataCode 
WHERE HomepagePromote.AddedID in(57,66)) as DataALl
select * from tempbl where Row between cast(1 as varchar) and cast(4 as varchar)

------解决方案--------------------
SQL code

WITH    tempbl
          AS ( SELECT   ROW_NUMBER() OVER ( ORDER BY HomepagePromote.AddedID ASC ) AS Row ,
                        *
               FROM     ( SELECT    HomepagePromote.AddedID ,
                                    DomaineData.DomaineOriName ,
                                    VinData.VinOriName ,
                                    DomaineData.DomaineTraName ,
                                    VinData.VinTraName ,
                                    VintageData.Vintage ,
                                    ProdItems.SellPrice ,
                                    ProdItems.VinID ,
                                    ProdItems.VintageID ,
                                    dbo.f_GetCateLvlAll(VinData.VinID, '-',
                                                        '1') AS CateLvl ,
                                    SD1.DataTraDesc AS STYPE ,
                                    SD2.DataOriDesc AS VUL ,
                                    HomepagePromote.PromoteDesc AS VinTraDesc ,
                                    VintageCap.BtFilePath
                          FROM      HomepagePromote
                                    LEFT JOIN ProdItems ON HomepagePromote.AddedID = ProdItems.AddedID
                                    LEFT JOIN VintageCap ON ProdItems.VintageID = VintageCap.VintageID
                                                            AND ProdItems.VulID = VintageCap.VulID
                                                            AND ProdItems.VinID = VintageCap.VinID
                                    LEFT JOIN VintageData ON ProdItems.VintageID = VintageData.VintageID
                                    LEFT JOIN VinData ON ProdItems.VinID = VinData.VinID
                                    LEFT JOIN DomaineData ON VinData.DomaineID = DomaineData.DomaineID
                                    LEFT JOIN SysBasicData AS SD1 ON VinData.VinType = SysBasicData.DataCode
                                    LEFT JOIN SysBasicData AS SD2 ON ProdItems.VulID = SysBasicData.DataCode
                          WHERE     HomepagePromote.AddedID IN ( 57, 66 )
                        ) AS DataALl
             ) --这个位置少了半个括号
    SELECT  *
    FROM    tempbl
    WHERE   Row BETWEEN CAST(1 AS VARCHAR) AND CAST(4 AS VARCHAR)