SQL根据一列中的最大值从多列中选择不同的行 [英] SQL Selecting distinct rows from multiple columns based on max value in one column

问题描述

这是我的 SQL 视图 - 让我们称之为 MyView :

<前>ECode SHCode TotalNrShare CountryCode Country000001 +00010 100 UKI 英国000001 ABENSO 900 美国 美国000355 +00012 1000 ESP 西班牙000355 000010 50 FRA 法国000042 009999 10 GER 德国000042 +00012 999 ESP 西班牙000787 ABENSO 500 美国 美国000787 000150 500 ITA 意大利001010 009999 100 GER 德国

我想为每个 ECode 返回 TotalNrShare 列中数字最高的单行.

例如，我想从上面的视图中返回这些结果:

<前>ECode SHCode TotalNrShare CountryCode Country000001 ABENSO 900 美国 美国000355 +00012 1000 ESP 西班牙000042 +00012 999 ESP 西班牙000787 ABENSO 500 美国 美国001010 009999 100 GER 德国

(注意在 ECode 000787 的情况下，其中有两个 SHCode，每个为 500，因为它们的数量相同，我们可以只返回第一行而不是两者，返回哪一行对我来说并不重要，因为这会发生很少，我的分析不需要 100%)

我尝试了各种方法，但似乎无法返回唯一的结果或我需要的其他国家/地区代码/国家/地区信息.

这是我的尝试之一(基于本网站上的其他解决方案，但我做错了):

SELECT tsh.ECode, tsh.SHCode, tsh.TotalNrShare, tsh.CountryCode, tsh.Country从 dbo.MyView AS tsh INNER JOIN(SELECT DISTINCT ECode, MAX(TotalNrShare) AS MaxTotalSH从 dbo.MyViewGROUP BY ECode) AS groupedtsh ON tsh.ECode = groupedtsh.ECode AND tsh.TotalNrShare = groupedtsh.MaxTotalSH

解决方案

WITHsequenced_data AS(选择*,ROW_NUMBER() OVER (PARTITION BY ECode ORDER BY TotalNrShare) AS sequence_id从我的看法)选择*从序列数据在哪里序列 ID = 1

然而，这应该会给出与您的示例查询相同的结果.这只是完成同一件事的不同方法.

然而，正如您所说的，有什么地方不对劲，请您详细说明哪里出了问题?例如 TotalNrShare 实际上是一个字符串吗?这是否会扰乱您的订单(以及 MAX())?

即使上面的代码与您的 SQL Server 不兼容，它也不应该完全崩溃.您应该只收到一条错误消息.例如，尝试执行 Select * By Magic，它应该只是给出一个错误.我强烈建议您查看和/或重新安装 Management Studio 的安装.

就替代方案而言，您可以这样做...

SELECT*从(SELECT ECode FROM MyView GROUP BY ECode) 作为基础交叉申请(SELECT TOP 1 * FROM MyView WHERE ECode = base.ECode ORDER BY TotalNrShare DESC) 作为数据

理想情况下，您可以将 base 子查询替换为一个表，该表已经包含您感兴趣的所有 ECode 的不同列表.

This is my SQL View - lets call it MyView :

ECode   SHCode  TotalNrShare    CountryCode Country
000001  +00010  100 UKI United Kingdom
000001  ABENSO  900 USA United States
000355  +00012  1000    ESP Spain
000355  000010  50  FRA France
000042  009999  10  GER Germany
000042  +00012  999 ESP Spain
000787  ABENSO  500 USA United States
000787  000150  500 ITA Italy
001010  009999  100 GER Germany

I would like to return the single row with the highest number in the column TotalNrShare for each ECode.

For example, I’d like to return these results from the above view:

ECode   SHCode  TotalNrShare    CountryCode Country
000001  ABENSO  900 USA United States
000355  +00012  1000    ESP Spain
000042  +00012  999 ESP Spain
000787  ABENSO  500 USA United States
001010  009999  100 GER Germany

(note in the case of ECode 000787 where there are two SHCode's with 500 each, as they are the same amount we can just return the first row rather than both, it isnt important for me which row is returned since this will happen very rarely and my analysis doesnt need to be 100%)

Ive tried various things but do not seem to be able to return either unqiue results or the additional country code/country info that I need.

This is one of my attempts (based on other solutions on this site, but I am doing something wrong):

SELECT tsh.ECode, tsh.SHCode, tsh.TotalNrShare, tsh.CountryCode, tsh.Country
FROM  dbo.MyView AS tsh INNER JOIN
                   (SELECT DISTINCT ECode, MAX(TotalNrShare) AS MaxTotalSH
                    FROM   dbo.MyView
                    GROUP BY ECode) AS groupedtsh ON tsh.ECode = groupedtsh.ECode AND tsh.TotalNrShare = groupedtsh.MaxTotalSH

解决方案

WITH
  sequenced_data AS
(
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY ECode ORDER BY TotalNrShare) AS sequence_id
  FROM
    myView
)
SELECT
  *
FROM
  sequenced_data
WHERE
  sequence_id = 1

This should, however, give the same results as your example query. It's simply a different approach to accomplish the same thing.

As you say that something is wrong, however, please could you elaborate on what is going wrong? Is TotalNrShare actually a string for example? And is that messing up your ordering (and so the MAX())?

EDIT:

Even if the above code was not compatible with your SQL Server, it shouldn't crash it out completely. You should just get an error message. Try executing Select * By Magic, for example, and it should just give an error. I strongly suggest getting your installation of Management Studio looked at and/or re-installed.

In terms of an alternative, you could do this...

SELECT
  *
FROM
  (SELECT ECode FROM MyView GROUP BY ECode) AS base
CROSS APPLY
  (SELECT TOP 1 * FROM MyView WHERE ECode = base.ECode ORDER BY TotalNrShare DESC) AS data

Ideally you would replace the base sub-query with a table that already has a distinct list of all the ECodes that you are interested in.

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