英文:
How to get first top values of a column, then perform where conditions to reduce rows
问题
以下是要翻译的部分：
SELECT TOP 25 customer
FROM tt
WHERE DocNum LIKE '%something%' AND U_NAME NOT LIKE '%something%'
GROUP BY customer
ORDER BY MIN(DocNum) DESC
如何获取前25行客户的以下部分：
WHERE DocNum LIKE '%something%'
AND U_NAME NOT LIKE '%something%'
GROUP BY customer
ORDER BY MIN(DocNum) DESC
英文:
SELECT TOP 25 customer
FROM tt
WHERE DocNum LIKE '%something%' AND U_NAME NOT LIKE '%something%'
GROUP BY customer
ORDER BY MIN(DocNum) DESC
Let's say I have the query above but obviously it returns the top 25 row of
SELECT customer
FROM tt
WHERE DocNum LIKE '%something%'
AND U_NAME NOT LIKE '%something%'
GROUP BY customer
ORDER BY MIN(DocNum) DESC
How can I get the
WHERE DocNum LIKE '%something%'
AND U_NAME NOT LIKE '%something%'
GROUP BY customer
ORDER BY MIN(DocNum) DESC
of the top 25 rows of customer?
答案1
得分: 1
以下是您要翻译的内容：
你想首先获取前 25 名客户（这些客户是具有最少 docnums 的前 25 名）。对于这些客户，您想选择与特定 docnum 和 u_name 模式匹配的所有行：
WITH top25 AS
(
SELECT TOP 25 customer
FROM tt
GROUP BY customer
ORDER BY MIN(docnum) DESC
)
SELECT *
FROM tt
WHERE docnum LIKE '%something%' AND u_name NOT LIKE '%something%'
AND customer IN (SELECT customer FROM top25)
ORDER BY customer, docnum;
英文:
You want the top 25 customers (which are the 25 with the least docnums) first. For these customers you want to select all rows that match certain docnum and u_name patterns:
WITH top25 AS
(
SELECT TOP 25 customer
FROM tt
GROUP BY customer
ORDER BY MIN(docnum) DESC
)
SELECT *
FROM tt
WHERE docnum LIKE '%something%' AND u_name NOT LIKE '%something%'
AND customer IN (SELECT customer FROM top25)
ORDER BY customer, docnum;
答案2
得分: 0
WHERE子句在TOP和ORDER BY之前进行评估。如果您希望首先获取按MIN(DocNum) DESC排序的前25条记录，那么需要先执行此操作。可以使用子查询来实现，如下所示：
SELECT customer
FROM tt
INNER JOIN
(
SELECT TOP 25 customer
FROM tt
GROUP BY customer
ORDER BY MIN(DocNum) DESC
) ds
ON tt.customer = ds.customer
WHERE tt.DocNum LIKE '%something%'
AND tt.U_NAME NOT LIKE '%something%';
这是您提供的SQL查询的翻译部分。
英文:
The WHERE clause is evaluating before the TOP and ORDER BY. If you want to first get the TOP 25 records order by MIN(DocNum) DESC then you need to perform this operation first. This can be achieved using sub-query like the following:
SELECT customer
FROM tt
INNER JOIN
(
SELECT TOP 25 customer
FROM tt
GROUP BY customer
ORDER BY MIN(DocNum) DESC
) ds
ON tt.customer = ds.customer
WHERE tt.DocNum LIKE '%something%'
AND tt.U_NAME NOT LIKE '%something%'
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论