在查询中使用 LIMIT 的 SQL 位置

Question

我试图将我的结果限制在 10 个。下面的查询返回所有没有"限制 10"的结果，但是当我添加限制 10 时，它给了我一个错误，说

接近限制的语法不正确

我错过了什么？

SELECT dbo.Organizations.cmoName AS "Organizations not quoted"
,dbo.Calls.kbpCallID AS "kbpCallID"
,MAX(dbo.CallLines.kblCallLineID) AS "kblCallLineID"
FROM (
(
(
(
dbo.Organizations LEFT OUTER JOIN dbo.Quotes ON dbo.Organizations.cmoOrganizationID = dbo.Quotes.qmpCustomerOrganizationID
) LEFT OUTER JOIN dbo.OrganizationIndustryTypeLinks ON dbo.Organizations.cmoOrganizationID = dbo.OrganizationIndustryTypeLinks.cmdOrganizationID
) LEFT OUTER JOIN dbo.Calls ON dbo.Organizations.cmoOrganizationID = dbo.Calls.kbpOrganizationID
) LEFT OUTER JOIN dbo.CallLines ON dbo.Calls.kbpCallID = dbo.CallLines.kblCallID
)
WHERE dbo.Quotes.qmpQuoteID IS NULL
AND dbo.Organizations.cmoCustomerStatus = 1
AND dbo.OrganizationIndustryTypeLinks.cmdIndustryTypeID = 'DRAFTING  '
AND (
dbo.CallLines.kblAddedDate < DATEADD(dd, - 60, GETDATE())
OR dbo.CallLines.kblCallID IS NULL
)
GROUP BY dbo.Organizations.cmoName
,dbo.Calls.kbpCallID
ORDER BY dbo.Organizations.cmoName ASC LIMIT 10

Answer 1

在 SQL Server 中：可以使用前 10 个而不是限制，如下面的查询所示。

SELECT TOP 10 dbo.Organizations.cmoName AS "Organizations not quoted"
, dbo.Calls.kbpCallID AS "kbpCallID"
, MAX(dbo.CallLines.kblCallLineID) AS "kblCallLineID"
FROM (
(
(
(
dbo.Organizations LEFT OUTER JOIN dbo.Quotes ON dbo.Organizations.cmoOrganizationID = dbo.Quotes.qmpCustomerOrganizationID
) LEFT OUTER JOIN dbo.OrganizationIndustryTypeLinks ON dbo.Organizations.cmoOrganizationID = dbo.OrganizationIndustryTypeLinks.cmdOrganizationID
) LEFT OUTER JOIN dbo.Calls ON dbo.Organizations.cmoOrganizationID = dbo.Calls.kbpOrganizationID
) LEFT OUTER JOIN dbo.CallLines ON dbo.Calls.kbpCallID = dbo.CallLines.kblCallID
)
WHERE dbo.Quotes.qmpQuoteID IS NULL
AND dbo.Organizations.cmoCustomerStatus = 1
AND dbo.OrganizationIndustryTypeLinks.cmdIndustryTypeID = 'DRAFTING  '
AND (
dbo.CallLines.kblAddedDate < DATEADD(dd, - 60, GETDATE())
OR dbo.CallLines.kblCallID IS NULL
)
GROUP BY dbo.Organizations.cmoName
,dbo.Calls.kbpCallID
ORDER BY dbo.Organizations.cmoName ASC

Answer 2

如果您使用 SQL Server，则使用top(10)而不是Limit 10。

Answer 3

在 SQL Server 中，按照 @Zip 的建议使用 TOP。

从 SQL Server 2012 开始，您可以使用OFFSET... FETCH阅读有关偏移量的详细信息...获取

SELECT dbo.Organizations.cmoName AS "Organizations not quoted"
,dbo.Calls.kbpCallID AS "kbpCallID"
,MAX(dbo.CallLines.kblCallLineID) AS "kblCallLineID"
FROM (
(
(
(
dbo.Organizations LEFT OUTER JOIN dbo.Quotes ON dbo.Organizations.cmoOrganizationID = dbo.Quotes.qmpCustomerOrganizationID
) LEFT OUTER JOIN dbo.OrganizationIndustryTypeLinks ON dbo.Organizations.cmoOrganizationID = dbo.OrganizationIndustryTypeLinks.cmdOrganizationID
) LEFT OUTER JOIN dbo.Calls ON dbo.Organizations.cmoOrganizationID = dbo.Calls.kbpOrganizationID
) LEFT OUTER JOIN dbo.CallLines ON dbo.Calls.kbpCallID = dbo.CallLines.kblCallID
)
WHERE dbo.Quotes.qmpQuoteID IS NULL
AND dbo.Organizations.cmoCustomerStatus = 1
AND dbo.OrganizationIndustryTypeLinks.cmdIndustryTypeID = 'DRAFTING  '
AND (
dbo.CallLines.kblAddedDate < DATEADD(dd, - 60, GETDATE())
OR dbo.CallLines.kblCallID IS NULL
)
GROUP BY dbo.Organizations.cmoName
,dbo.Calls.kbpCallID
ORDER BY dbo.Organizations.cmoName ASC OFFSET 0 ROWS
FETCH 10 ROWS ONLY;