Vistadb SQL 语法错误

Question

vistadb4 无法识别此 SQL 视图脚本。 但它正在使用SQL2005。 有解决方案吗？ 谢谢。

SELECT DISTINCT ItemCode, SellingPrice
FROM         dbo.SalesPurchases AS a
WHERE     (KeyID =
                          (SELECT     TOP (1) MAX(KeyID) AS KeyID
                            FROM          dbo.SalesPurchases AS b
                            GROUP BY ItemCode, Type
                            HAVING      (ItemCode = a.ItemCode) AND (Type = 'purchase')))
GROUP BY ItemCode, SellingPrice

Answer 1

不确定，但看看这个：

SELECT 
  DISTINCT a.ItemCode, a.SellingPrice
FROM         
  dbo.SalesPurchases AS a
JOIN
  (SELECT
     TOP (1) ItemCode, Type, MAX(KeyID) AS KeyID
   FROM
     dbo.SalesPurchases AS 
   GROUP BY 
     ItemCode, Type
   HAVING
     (ItemCode = a.ItemCode) AND (Type = 'purchase')
   ) b
   on b.KeyID = a.KeyID
GROUP BY 
  a.ItemCode, a.SellingPrice