如何根据这个场景编写sql查询来获取记录 [英] How to write sql query to fetch record according to this scenrio

问题描述

大家好我有一个名为VW_VW_Academics的视图表



该视图包含

Hi guys i have one view table called VW_VW_Academics

that view consists of

Position   MaxPoints
 1          48
 2          24
 3          16
 4          12

在My Maintable我有fldAvgMarks，fldHouseID表名是tblChampionsThrophy



我的查询是

In My Maintable i have fldAvgMarks,fldHouseID table name is tblChampionsThrophy

My Query is

SELECT A.fldAvgMarks, A.fldHouseID,B.MaxPoints
    FROM (SELECT ROW_NUMBER() OVER (ORDER BY fldAvgMarks DESC) AS Position, *
    FROM RMS.tblChampionsThrophy
    ) AS A inner JOIN (
            SELECT Position, MaxPoints
            FROM VW_Academics) AS B ON A.Position=B.Position

我得到输出

I''M getting output

fldAvgMarks fldHouseID  MaxPoints
40	    HN04	48
40	    HN05	24-----> here it should come 48 only b'coz fldAvgMarks both HN04,5 r 40
20	    HN03	16
10	    HN02	12

这里输出部分正确..怎么做？



任何人都可以建议我。

Here Output is Partially correct.. How to do that?

Can any Pls Suggest me.

推荐答案

尝试使用以下MaxPoints的 MAX 函数。

Hi,

try by making use of MAX function for MaxPoints like below.

SELECT A.fldAvgMarks, A.fldHouseID,MAX(B.MaxPoints) 'MaxPoints'
    FROM (SELECT ROW_NUMBER() OVER (ORDER BY fldAvgMarks DESC) AS Position, *
    FROM RMS.tblChampionsThrophy
    ) AS A inner JOIN (
            SELECT Position, MaxPoints
            FROM VW_Academics) AS B ON A.Position=B.Position
GROUP BY A.fldAvgMarks, A.fldHouseID

希望它有效。

hope it works.

不要重新发布!!!



答案在这里： sql查询获取特定字段并赋值 [ ^ ]

Do not repost!!!

Answer is here: sql query to fetch particular field and assign value[^]

这篇关于如何根据这个场景编写sql查询来获取记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！