选择匹配column2的top column1

sorry for asking this, but i'm runnin' out of ideas

对不起要问这个,但我已经没想到了

i have this table:

我有这张桌子:

[id]    [pid]    [vid]
1        4        6844
1        5        6743
2        3        855
2        6        888
...

how to i query this eg.table to get the following result:

如何查询此eg.table以获得以下结果:

[id]    [pid]    [vid]
1        5        6743
2        6        888

i want to get the highest [pid] for an [id] with the [vid] matching to this [pid]

我想获得[id]的最高[pid],[vid]与此[pid]匹配

any ideas?

i'm using mssql 2008

我正在使用mssql 2008

3 个解决方案

#1

I would use Common Table Expressions (CTE). This offers lots of possibilities like so:

我会使用Common Table Expressions(CTE)。这提供了很多可能性:

WITH Result (RowNumber, [id], [pid], [vid])
AS
(
    SELECT Row_Number() OVER (PARTITION BY [id]
                              ORDER     BY [vid] DESC)
          ,[id]
          ,[pid]
          ,[vid]
      FROM MyTable
)
SELECT [id]
      ,[pid]
      ,[vid]
  FROM Result
 WHERE RowNumber = 1

#2

one way

select t1.* from
(select id,max(pid) as Maxpid
from yourtable
group by id) t2
join yourtable t1 on t2.id = t1.id
and t2.Maxpid = t1.pid

#3

Since you're using Microsoft SQL Server 2008, then I'd recommend Common Table Expressions and the OVER clause to accomplish dividing the results into groups by id, and returning just the top row in each group (ordered by pid). Bliek's answer shows one way to do that.

由于您使用的是Microsoft SQL Server 2008,因此我建议使用Common Table Expressions和OVER子句来完成将结果按ID分组,并返回每个组中的顶行(按pid排序)。 Bliek的答案显示了一种方法。

(This same basic approach, by the way, is very useful for more efficient paging, as well.)

(顺便说一下,这种基本方法对于更高效的分页也非常有用。)

There isn't a fantastically great way to do this with "standard" SQL. The method show in SQLMenace's answer will only work in databases where you can use a subquery as a table. It'd be one way to accomplish this in SQL Server 2000, for example, but may not work in every mainstream RDBMS.

使用“标准”SQL没有一个非常好的方法来做到这一点。 SQLMenace的答案中显示的方法仅适用于可以将子查询用作表的数据库。例如,它是在SQL Server 2000中实现此目的的一种方法,但可能不适用于每个主流RDBMS。

#1