更新时间:2023-01-29 17:08:11
如前所述,除非使用 TOP 或 FOR XML,否则 ORDER BY 不得出现在子查询中.
As stated ORDER BY must not apper in subqueries unless TOP or FOR XML is used.
SELECT TOP 10 * FROM (
SELECT
c.id,
c.name,
c.inserteddate,
c.cityname,
ftblstates.name AS statename,
clc.name AS catname,
'' AS listingimagelogo,
'' AS orgname, relocateyn,
'' AS employerclassified
FROM tblclassifieds c
LEFT JOIN tblclassifiedscategories clc ON c.categoryid = clc.id
LEFT JOIN ftblstates ON c.stateid = ftblstates.id
WHERE c.expirydate != ''
AND NOT c.id IN (
SELECT TOP 10
tblclassifieds.id
FROM tblclassifieds
WHERE c.expirydate != ''
ORDER BY inserteddate desc
)
UNION ALL
SELECT
ce.id,
ce.name,
ce.inserteddate,
suburb AS cityname,
ftblstates.name AS statename,
ce.jobtype AS catname,
ce.listingimagelogo,
ce.orgname, '' AS relocateyn,
'1' AS employerclassified
FROM tblclassifiedemployers ce
LEFT JOIN ftblstates ON ce.stateid = ftblstates.id
WHERE ce.expirydate != ''
AND NOT ce.id IN (
SELECT TOP 10
tblclassifiedemployers.id
FROM tblclassifiedemployers
WHERE ce.expirydate != ''
ORDER BY inserteddate desc
)
) a ORDER BY inserteddate desc;