从同一个表SQL Server中选择对应的行

I want to get some column based on another column

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

Example table:

示例表：

| 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        |

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

但我希望得到BillVersion最大的行，每个BlilCode最大

Expected result

预期结果

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

My current query is:

我目前的查询是：

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

2 个解决方案

#1

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

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

根据我的经验，与总是使用顺序扫描的row_number解决方案相比，在某些情况下可能会更快。

PERFORMANCE BONUS

绩效奖金

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

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

如果我在我的服务器上运行它，那么GROUP BY版本的性能比窗口函数版本好两倍多。而且，如果我创建索引

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.

然后性能甚至超过三倍，而窗口函数解决方案的性能是相同的，因为它使用顺序扫描，尽管索引。

#2

Your results suggest that you want:

您的结果表明您想要：

select db.*
from (select db.*,
             row_number() over (partition by shortname order by billversion desc) as seqnum
      from db
     ) db
where seqnum = 1;

If you based this on blilcode, then the results would have three rows instead of two.

如果你基于blilcode，那么结果将有三行而不是两行。

Note: The question was edited after this, so the appropriate query would be:

注：此问题已在此之后编辑的，所以适当的查询将是：

select db.*
from (select db.*,
             row_number() over (partition by blilcode order by billversion desc) as seqnum
      from db
     ) db
where seqnum = 1;

#1