ALTER PROCEDURE [dbo].[SellTable_GetCollectionPage]
(
@startIndex int,
@endIndex int
)
AS
BEGIN
WITH SellTemp AS(
SELECT ROW_NUMBER() OVER (ORDER BY SellTable.SellID DESC)AS Row, SellTable.SellID,SellTable.SellDate,SellTable.CustomerID,CustomerInfo.CustomerName,CustomerInfo.CustomerNumber,Cast(SUM(SellTableList.Quantity * SellTableList.Price)AS money)AS SellTotal
FROM SellTable
LEFT OUTER JOIN CustomerInfo on SellTable.CustomerID=CustomerInfo.CustomerID
LEFT OUTER JOIN SellTableList on SellTable.SellID=SellTableList.SellID)GROUP BY SellTable.SellID, SellTable.SellDate, SellTable.CustomerID, CustomerInfo.CustomerName, CustomerInfo.CustomerNumberFROM SellTempWHERE Row BETWEEN @startIndex AND @endIndexend一运行就提示"关键字 'GROUP' 附近有语法错误",请高手指点,谢谢!
(
@startIndex int,
@endIndex int
)
AS
BEGIN
WITH SellTemp AS(
SELECT ROW_NUMBER() OVER (ORDER BY SellTable.SellID DESC)AS Row, SellTable.SellID,SellTable.SellDate,SellTable.CustomerID,CustomerInfo.CustomerName,CustomerInfo.CustomerNumber,Cast(SUM(SellTableList.Quantity * SellTableList.Price)AS money)AS SellTotal
FROM SellTable
LEFT OUTER JOIN CustomerInfo on SellTable.CustomerID=CustomerInfo.CustomerID
LEFT OUTER JOIN SellTableList on SellTable.SellID=SellTableList.SellID)GROUP BY SellTable.SellID, SellTable.SellDate, SellTable.CustomerID, CustomerInfo.CustomerName, CustomerInfo.CustomerNumberFROM SellTempWHERE Row BETWEEN @startIndex AND @endIndexend一运行就提示"关键字 'GROUP' 附近有语法错误",请高手指点,谢谢!
-->去掉括号??
LEFT OUTER JOIN SellTableList on SellTable.SellID=SellTableList.SellID
是先使用with,
然后在查询么?
去看看基本语法先,
WITH TB AS
(
SELECT * FROM A
)
SELECT * FROM TB
(
@startIndex int,
@endIndex int
)
AS
BEGIN
WITH SellTemp AS(
SELECT ROW_NUMBER() OVER (ORDER BY SellTable.SellID DESC)AS Row, SellTable.SellID,SellTable.SellDate,SellTable.CustomerID,CustomerInfo.CustomerName,CustomerInfo.CustomerNumber,Cast(SUM(SellTableList.Quantity * SellTableList.Price)AS money)AS SellTotal
FROM SellTable
LEFT OUTER JOIN CustomerInfo on SellTable.CustomerID=CustomerInfo.CustomerID
LEFT OUTER JOIN SellTableList on SellTable.SellID=SellTableList.SellID GROUP BY SellTable.SellID, SellTable.SellDate, SellTable.CustomerID, CustomerInfo.CustomerName, CustomerInfo.CustomerNumber
)
select *
FROM SellTemp WHERE Row BETWEEN @startIndex AND @endIndex end 是要这样的效果吗?
(
@startIndex int,
@endIndex int
)
AS
BEGIN
WITH SellTemp AS(
SELECT ROW_NUMBER() OVER (ORDER BY SellTable.SellID DESC)AS Row,
SellTable.SellID,SellTable.SellDate,SellTable.CustomerID,CustomerInfo.CustomerName,CustomerInfo.CustomerNumber,
Cast(SUM(SellTableList.Quantity * SellTableList.Price)AS money)AS SellTotal
FROM SellTable
LEFT OUTER JOIN CustomerInfo on SellTable.CustomerID=CustomerInfo.CustomerID
LEFT OUTER JOIN SellTableList on SellTable.SellID=SellTableList.SellID
GROUP BY SellTable.SellID, SellTable.SellDate, SellTable.CustomerID, CustomerInfo.CustomerName, CustomerInfo.CustomerNumber
) SELECT * FROM SellTemp WHERE Row BETWEEN @startIndex AND @endIndex end