我需要辅助子查询 [英] I need assist sub query

问题描述

我有两张桌子：Tb1，Tb2

i have two table : Tb1,Tb2

TB1 
------------------------
 | ID1| SNAME |
-+----+-------+----------
 | 1  | NAME1 | 
-+----+-------+----------
 | 2  | NAME2 | 
-+----+-------+----------
 | 3  | NAME3 | 
------------------------

TB1
-------------
 | ID1  |   |**___                    TB2
-------------     \___              -----------------------
 | SNAME|   |         \___          |  ID2  |         |   
-------------             |___      +-------+---------+---
                              \==>  | IDTB1 |         |
                                    +-------+---------+---  
                                    |  SID2 |         |
                                    +-------+---------+---
                                    |SVALUE |         |
                                    +-------+---------+-- 

TB2
ID2 IDTB1   SID2   SVALUE

----------------------------------
 |ID2 |IDTB1| SID2 | SVALUE  |
-+----+-----+---------------------
 | 1  |  1  |   5  |   11    |
-+----+-----+--------------------
 | 2  |  1  |   7  |    9    |
-|----|-----+--------------------
 | 3  |  1  |   7  |   10    |
-|----|-----+--------------------
 | 4  |  1  |   7  |   13    |
-|----|-----+--------------------
 | 5  |  2  |   5  |   14    |
-|----|-----+--------------------
 | 6  |  2  |   5  |   16    |
-|----|-----+--------------------
 | 7  |  2  |   5  |   18    |
-|----|-----+--------------------
 | 8  |  2  |   7  |   10    |
-|----|-----+--------------------
 | 9  |  3  |   5  |   10    |
-|----|-----+--------------------
 | 10 |  3  |   1  |   10    |
-|----|-----+--------------------
 | 11 |  3  |   7  |   22    |
---------------------------------



I want query to show results like:
example: where SID2=7

----------------------------
 | ID2 | SNAME | SVALUE |
-+-----+-------+--------+---
 |  4  | NAME1 |   13   |
-+-----+-------+--------+---
 |  8  | NAME2 |   10   |
-+-----+-------+--------+---
 |  11 | NAME3 |   22   |
----------------------------

我尝试过：

What I have tried:

SELECT TB1.SNAME,  TB2.ID2,  ( select max(SVALUE) from tb2 where TB2.SID2 = 7 ) as SVALUE FROM dbo.TB1 INNER JOIN dbo.TB2   ON TB1.ID = TB2.IDTB1 WHERE TB2.SID2 = 7

推荐答案

这应该这样做，我认为你的样本输出是错误的。 IDTB1的最大值为18而不是10



首先使用ROW_NUMBER从TB2中选择所有最大值，然后加入Tb1

This should do it and I think your sample output is wrong. The max for IDTB1 is 18 and not 10

First select all the max using ROW_NUMBER over function from TB2 then join Tb1

SELECT  Id2, Sname, SValue
FROM    (
        SELECT  *, ROW_NUMBER() OVER (PARTITION BY [IDTB1] ORDER BY svalue DESC) AS rn
        FROM    dbo.TB2
        ) q
JOIN dbo.TB1
ON q.[IDTB1] = dbo.TB1.[Id1]
WHERE   rn = 1

输出：

Output:

Id2	Sname	SValue
4	Name1	13
7	Name2	18
11	Name3	22

这篇关于我需要辅助子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！