使用多个UNION和ORDER BY的SELECT TOP [英] SELECT TOP with multiple UNION and with ORDER BY

问题描述

我想从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 BYis 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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！