一条access 里的语句，麻烦帮我改写成 ms sqlserver 里面的语句

[EexeNet]

语句如下：
SELECT Ay_Content.*, Ay_Class.bName AS bClassName, Ay_Class.bParent AS bParentID, IIf(IsNull(c.bCommentCount),0,c.bCommentCount) AS bCommentCount
FROM (Ay_Content LEFT JOIN Ay_Class ON Ay_Content.bClassID = Ay_Class.bId) LEFT JOIN [select bArtID,count(*) as bCommentCount from Ay_Comment group by bArtID]. AS c ON Ay_Content.bId = c.bArtID;


access 下的，麻烦帮我改写成ms sqlserver 的，这语句竟然不通用。如何写的呢？请指教一下，哪里出错了？

[AcHerat]

SELECT Ay_Content.*, Ay_Class.bName AS bClassName, Ay_Class.bParent AS bParentID, IsNull(c.bCommentCount,0) AS bCommentCount
FROM Ay_Content LEFT JOIN Ay_Class ON Ay_Content.bClassID = Ay_Class.bId
    LEFT JOIN (select bArtID,count(*) as bCommentCount from Ay_Comment group by bArtID) AS c ON Ay_Content.bId = c.bArtID

[快溜]

SELECT Ay_Content.*, Ay_Class.bName AS bClassName, Ay_Class.bParent AS bParentID,
 IIf(IsNull(c.bCommentCount),0,c.bCommentCount) AS bCommentCount
FROM Ay_Content a
LEFT JOIN Ay_Class b ON a.bClassID = b.bId
LEFT JOIN (select bArtID,count(*) as bCommentCount from Ay_Comment group by bArtID) c 
            ON a.bId = c.bArtID;

[EexeNet]

access : select * from (SELECT a.* FROM Ay_Class AS a where a.bId=14 union (select b.* from Ay_Class AS a INNER JOIN Ay_Class AS b ON a.bParent = b.bId where a.bId=14) ) order by bParent,bOrder

这句，又是哪错了，在sqlserver 提示 order 附近有错，

也需要改成 mssql里运行的

[快溜]

select * from 
(SELECT a.* FROM Ay_Class AS a where a.bId=14 
   union 
  select b.* from Ay_Class AS a 
       INNER JOIN Ay_Class AS b ON a.bParent = b.bId where a.bId=14 ) 
order by bParent,bOrder

[快溜]

select * from 
(SELECT a.* FROM Ay_Class AS a where a.bId=14 
   union 
  select b.* from Ay_Class AS a 
       INNER JOIN Ay_Class AS b ON a.bParent = b.bId where a.bId=14 ) aa
order by bParent,bOrder

[EexeNet]

ssp2009 你好，感谢你的答复，问一下，你的：a.bParent = b.bId where a.bId=14 ) aa

aa 是什么呢？

[快溜]

 --如果改为 
a.bParent = b.bId where a.bId=14 ) as aa
 --你应该就懂了。

[zhuosh]

SQL code
select * from
(SELECT a.* FROM Ay_Class AS a where a.bId=14
union
select b.* from Ay_Class AS a
INNER JOIN Ay_Class AS b ON a.bParent = b.bId where a.bId=14 ) aa
order by bParent,bOrder

[EexeNet]

很好，结贴，谢谢大家，特别感谢
ssp2009