sql - 从一行中获取基于组的id [英] sql - getting the id from a row based on a group by

问题描述

>

成绩

表B

• tableBID


• 名称


• 说明




表A从两个表中的表BID中链接到表b。





如果我想查找表A中的行，对于表B中的每一行，我都会写下我的查询：



  select max（grade ）从tableA group by tableBID 
  

然而，我不只是想要成绩，我想要成绩加上该行的id。

解决方案

您可以尝试类似于

<$ （
）SELECT tableBID，
MAX（等级）MaxGrade
FROM TableA a
FROM TableA a INNER JOIN

GROUP BY tableBID
）B ON a.tableBID = B.tableBID AND a.grade = B.MaxGrade


使用Sql Server 2005 ROW_NUMBER 功能你也可以尝试

  SELECT tableAID，
 tableBID，
 grade 
 FROM（
 SELECT *，
 ROW_NUMBER （）OVER（tableBID ORDER BY grade DESC）RowNum 
 FROM @TableA 
）A 
 WHERE a.RowNum = 1 
  




Table A

• tableAID
• tableBID
• grade

Table B

• tableBID
• name
• description

Table A links to Table b from the tableBID found in both tables.

If I want to find the row in Table A, which has the highest grade, for each row in Table B, I would write my query like this:

select max(grade) from TableA group by tableBID

However, I don't just want the grade, I want the grade plus id of that row.

解决方案

You could try something like

SELECT  a.*
FROM    TableA a INNER JOIN
        (
            SELECT  tableBID,
                    MAX(grade) MaxGrade
            FROM    TableA
            GROUP BY tableBID
        ) B ON a.tableBID = B.tableBID AND a.grade = B.MaxGrade

Using the Sql Server 2005 ROW_NUMBER function you could also try

SELECT  tableAID,
        tableBID,
        grade
FROM    (
            SELECT  *,
                    ROW_NUMBER() OVER (PARTITION BY tableBID ORDER BY grade DESC) RowNum
            FROM    @TableA
        ) A
WHERE   a.RowNum = 1

这篇关于sql - 从一行中获取基于组的id的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！