问题描述
我想从SQL Server中的数据库中选择最新记录.如果仅选择一个项目,则最终输出为:
I want to select the latest records from the DB in SQL Server. If only one item is selected the final output is this:
SELECT TOP(10) * from dbo.Eventos WHERE Tipo LIKE '%OS%' AND Distrito LIKE '%'
+始终添加在末尾:
ORDER BY Data DESC
注意:Distrito LIKE '%'
必须保留,因为有时会以编程方式将其更改为%
以外的其他内容.
NOTE: Distrito LIKE '%'
must stay as it sometimes is programatically changed to something other than %
.
如果选择了更多项目,则该查询将以编程方式为每个项目添加一条UNION
行 .最后,ORDER BY
一如既往地添加.选中所有4个项目的示例:
If there are more items selected, the query gets one UNION
line added programatically for each item. At the end, the ORDER BY
is added as always. Example with all 4 items checked:
SELECT TOP(10) * from dbo.Eventos WHERE Tipo LIKE '%OS%' AND Distrito LIKE '%'
UNION ALL SELECT TOP(10) * from dbo.Eventos WHERE Tipo LIKE '%Rad%' AND Distrito LIKE '%'
UNION ALL SELECT TOP(10) * from dbo.Eventos WHERE Tipo LIKE '%Aci%' AND Distrito LIKE '%'
UNION ALL SELECT TOP(10) * from dbo.Eventos WHERE Tipo LIKE '%Out%' AND Distrito LIKE '%'
ORDER BY Data DESC
但是,这给了我 OLDEST 10个针对每个WHERE
子句排序的BY Data DESC
的结果.
But this gives me the OLDEST 10 results for each WHERE
clause sorted BY Data DESC
.
如何获得每个项目(WHERE
)的最新 X条结果?
How can i get the NEWEST X results for each item (WHERE
)?
推荐答案
如果我理解正确,则您希望每个tipo的10个最新eventos,例如os,rad,aci,out.您可以通过查看数据来确定最近的数据(我假设这是一个日期字段).我们可以通过使用Tipo的ROW_NUMBER分区来完成此操作,而无需所有并集.但是由于我们有通配符,所以我们需要将它们定义在同一个集合中.可以通过window函数中的case语句来完成.
If I understand correctly, you want the 10 most recent eventos for each tipo like os, rad, aci, out. You determine the most recent by looking at data (I'm assuming that's a date field) We can accomplish this by using a ROW_NUMBER partition by the tipo without all the unions. but since we have wild cards for tipo, we need to define them into the same set; which can be done with a case statement within the window function.
我假设在填充时,Distrito的每个Tipo的值都相同.
I'm assuming that Distrito would have the same value for each tipo when populated.
WITH CTE AS (
SELECT E.*, Row_number() over (partition by
CASE WHEN Tipo LIKE '%OS%' then 'OS'
WHEN Tipo like '%Rad%' then 'Rad'
WHEN Tipo LIKE '%Aci%' then 'ACI'
WHEN tipo LIKE '%Out%' then 'OUT' end order by data Desc) RN
FROM dbo.Eventos E
WHERE (Tipo LIKE '%OS%' OR Tipo LIKE '%Rad%' OR Tipo LIKE '%Aci%' OR Tipo LIKE '%Out%')
AND Distrito like '%')
SELECT *
FROM cte
WHERE RN <=10;
我们使用通用表表达式(CTE),因为在限制行号之前,我们需要为行号生成结果.由于row_number将针对每个不同的技巧重新启动,因此我们只需要获取那些< = 10即可替换您的顶部.
We use the Common table Expression (CTE) because we need the results to be generated for the rownumbers before we can limit by them. Since row_number will restart for every different tipo, we simply need to get those <=10 to replace your top.
或者只是基于您所做的事情...
Or to just build on what you've done...
SELECT * FROM (
SELECT * FROM
(SELECT TOP(10) * from dbo.Eventos WHERE Tipo LIKE '%OS%' AND Distrito LIKE '%' ORDER BY DATA desc) A
UNION ALL
SELECT * FROM
(SELECT TOP(10) * from dbo.Eventos WHERE Tipo LIKE '%Rad%' AND Distrito LIKE '%' ORDER BY DATA DESC) B
UNION ALL
SELECT * FROM
(SELECT TOP(10) * from dbo.Eventos WHERE Tipo LIKE '%Aci%' AND Distrito LIKE '%' ORDER BY DATA DESC) C
UNION ALL
SELECT * FROM
(SELECT TOP(10) * from dbo.Eventos WHERE Tipo LIKE '%Out%' AND Distrito LIKE '%' ORDER BY Data DESC) D
) E
ORDER BY DATA DESC;
您需要子查询有其自己的顺序,以便为每个tipo分组获得正确的前10名.为此,您需要将每个查询用作内联视图,并在合并发生之前完全实现(实际生成数据).
You need the subqueries to have their own order by to get the right top 10 for each tipo grouping. To accomplish this you need each query to act as a inline view and fully materialize (actually generate the data) before the union occurs.
这篇关于使用多个UNION和ORDER BY的SELECT TOP的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!