从同一表中选择对应于行的SQL Server [英] Select corresponding to row from the same table SQL Server

问题描述

我想根据另一栏获得一些栏

I want to get some column based on another column

示例表:

| BlilCode | BlilShortName | BatchWeigth | BillVersion | BlilMaxTime |
+----------+---------------+-------------+-------------+-------------+
| 5502     | aaa           | 1.00        | 1           | 360         |
| 5502     | aaa           | 2.00        | 2           | 240         |
| 5510     | bbb           | -32.94      | 2           | 360         |
| 5510     | bbb           | 1.00        | 1           | 360         |
| 5510     | bbb           | 36.37       | 3           | 3600        |

但是我想获取每个BlilCode最大值的BillVersion最大值的行

but I want to get the rows where BillVersion is max for every BlilCode is max

预期结果

| BlilCode | BlilShortName | BatchWeigth | BillVersion | BlilMaxTime |
+----------+---------------+-------------+-------------+-------------+
| 5502     | aaa           | 2.00        | 2           | 240         |
| 5510     | bbb           | 36.37       | 3           | 3600        |

我当前的查询是:

SELECT    
    [BlilCode], [BlilShortName], 
    BatchWeigth, (BillVersion) AS BillVersion, [BlilMaxTime]
FROM 
    [CVfeedDB].[dbo].[constants.Blil]  
WHERE 
    BlilActive = 1 AND BatchWeigth IS NOT NULL
ORDER BY 
    BlilCode

推荐答案

从您的描述来看，我并不是很聪明，但是，可以使用以下查询来实现结果

I'm not really smart from your description, however, the result can be achieved using the following query

select your_table.*
from your_table 
join
(
  select BlilShortName, max(billversion) bmax
  from your_table
  group by BlilShortName
) t on your_table.billversion = t.bmax and your_table.BlilShortName = t.BlilShortName

根据我的经验，与始终使用顺序扫描的row_number解决方案相比，在某些情况下它可以更快.

From my experience it can be faster in some cases when compared to row_number solution which always uses sequential scan.

绩效奖金

由于有一个关于效率的讨论，我敢于添加简单的测试

Since there is a discussion regarding the efficiency I dare to add simple test

IF OBJECT_ID('dbo.GTable', 'U') IS NOT NULL  DROP TABLE dbo.GTable
SELECT TOP 1000000
      NEWID() id, 
      ABS(CHECKSUM(NEWID())) % 100 group_id, 
      ABS(CHECKSUM(NEWID())) % 10000 orderby
 INTO GTable
FROM    sys.sysobjects
CROSS JOIN sys.all_columns

SET STATISTICS TIME on
-- GROUP BY version
select t1.*
from gtable t1
join
    (
      SELECT group_id, max(orderby) gmax
      from gtable
      group by group_id
    ) t2 on t1.group_id = t2.group_id and t1.orderby = t2.gmax

-- WINDOW FUNCTION version
select t.id, t.group_id, t.orderby
from
(
select *, 
       dense_rank() over (partition by group_id order by orderby desc) rn
from gtable 
) t
where t.rn = 1

如果我在服务器上运行此命令，则GROUP BY版本的性能比窗口函数版本的性能高出两倍以上.此外，如果我创建索引

If I run this on my server then the performance of GROUP BY version is more than twice better than the window function version. Moreover, if I create index

CREATE NONCLUSTERED INDEX ix_gtable_groupid_orderby
    ON [dbo].[GTable] (group_id,orderby) INCLUDE (id)

然后，性能甚至提高了三倍以上，而窗口函数解决方案的性能却是相同的，因为尽管有索引，它仍使用顺序扫描.

then the performance is even more than three times better, whereas the performance of window function solution is the same since it uses sequential scan despite the index.

这篇关于从同一表中选择对应于行的SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！