请好手赐教_读书人

请高手赐教！
--4. 查找所有人及他们当中的一位年龄最大的亲属
这个操作，突然有点思维短路，虽然得到了结果，但不全面，没有亲属的名称和SubId，想不出好的办法，请高手赐教！

DECLARE @TMain TABLE(MainId INT, MainName VARCHAR(32))
DECLARE @TSub TABLE(SubId INT, MainId INT, SubName VARCHAR(32), Age INT)

INSERT INTO @TMain
SELECT 1,'小明' UNION
SELECT 2,'小华' UNION
SELECT 3,'小鸟'

INSERT INTO @TSub
SELECT 11,1,'小明爸',48 UNION
SELECT 12,1,'小明妈',47 UNION
SELECT 13,1,'小明爷',69 UNION
SELECT 22,2,'小华妈',88 UNION
SELECT 23,2,'小华姐',32

--1. 查找每个有亲属的人及亲属
SELECT *
FROM   @TMain a
       INNER JOIN @TSub b
            ON  a.MainId = b.MainId
--2. 查找每个有亲属的人
SELECT *
FROM   @TMain a
WHERE  a.MainId IN (SELECT t.MainId
                    FROM   @TSub t)

--3. 查找所有人及亲属
SELECT *
FROM   @TMain a
       LEFT JOIN @TSub b
            ON  a.MainId = b.MainId
            
--4. 查找所有人及他们当中的一位年龄最大的亲属
SELECT c.*,
       t.age
FROM   @TMain c
       LEFT JOIN 
       (
                SELECT b.MainId,
                       MAX(b.Age) AS Age,
                FROM   @TSub b
                GROUP BY
                       b.MainId
            ) AS t
            ON  c.MainId = t.MainId

[最优解释]

DECLARE @TMain TABLE(MainId INT, MainName VARCHAR(32))
DECLARE @TSub TABLE(SubId INT, MainId INT, SubName VARCHAR(32), Age INT)

INSERT INTO @TMain
SELECT 1,'小明' UNION
SELECT 2,'小华' UNION
SELECT 3,'小鸟'

INSERT INTO @TSub
SELECT 11,1,'小明爸',48 UNION
SELECT 12,1,'小明妈',47 UNION
SELECT 13,1,'小明爷',69 UNION
SELECT 22,2,'小华妈',88 UNION
SELECT 23,2,'小华姐',32

--1. 查找每个有亲属的人及亲属
SELECT *
FROM   @TMain a
       INNER JOIN @TSub b
            ON  a.MainId = b.MainId
--2. 查找每个有亲属的人
SELECT *
FROM   @TMain a
WHERE  a.MainId IN (SELECT t.MainId 
 
                    FROM   @TSub t)

--3. 查找所有人及亲属
SELECT *
FROM   @TMain a
       LEFT JOIN @TSub b
            ON  a.MainId = b.MainId
            
--4. 查找所有人及他们当中的一位年龄最大的亲属
SELECT *
FROM   @TMain c
       outer apply 
       (
                SELECT top(1)* 
                       
                FROM   @TSub b where  c.MainId = b.MainId
                order by Age desc
            ) AS t

[其他解释]

引用:

引用:SQL code?123456789101112--SQL--查找所有人及他们当中的一位年龄最大的亲属 select TM.MainId,TM.MainName,M.SubId,M.SubName,M.MaxAge from @TMain TM inner join(select ts.MainId, ts.SubId,ts.……

那就稍微改动一下，


--SQL
--查找所有人及他们当中的一位年龄最大的亲属

select TM.MainId,TM.MainName,M.SubId,M.SubName,M.MaxAge from @TMain TM left join(
select ts.MainId, ts.SubId,ts.SubName,r.MaxAge from @TSub TS inner join(
select MainId,MAX(Age) MaxAge From @TSub group by MainId ) R
on TS.MainId=r.MainId and TS.Age=r.MaxAge) M
on TM.MainId=M.MainId

--结果集合
--1小明13小明爷69
--2小华22小华妈88
--3小鸟NULLNULLNULL

[其他解释]
DECLARE @TMain TABLE(MainId INT, MainName VARCHAR(32))
DECLARE @TSub TABLE(SubId INT, MainId INT, SubName VARCHAR(32), Age INT)
INSERT INTO @TMain SELECT 1,'小明' UNION SELECT 2,'小华' UNION SELECT 3,'小鸟'
INSERT INTO @TSub SELECT 11,1,'小明爸',48 UNION SELECT 12,1,'小明妈',47 UNION SELECT 13,1,'小明爷',69 UNION SELECT 22,2,'小华妈',88 UNION SELECT 23,2,'小华姐',32
select c.*,t.*
from @TMain c
left join
(
select * from @TSub b where not exists(select 1 from @TSub where MainId=b.mainid and Age>b.age)
)t
on c.mainid=t.MainId
/*
MainId MainName SubId MainId SubName Age
----------- -------------------------------- ----------- ----------- -------------------------------- -----------

1 小明 13 1 小明爷 69
2 小华 22 2 小华妈 88
3 小鸟 NULL NULL NULL NULL

(3 行受影响)

*/
[其他解释]
没有就加咯

DECLARE @TMain TABLE(MainId INT, MainName VARCHAR(32))
 DECLARE @TSub TABLE(SubId INT, MainId INT, SubName VARCHAR(32), Age INT)
  
 INSERT INTO @TMain
 SELECT 1,'小明' UNION
 SELECT 2,'小华' UNION
 SELECT 3,'小鸟'
  
 INSERT INTO @TSub
 SELECT 11,1,'小明爸',48 UNION
 SELECT 12,1,'小明妈',47 UNION
 SELECT 13,1,'小明爷',69 UNION
 SELECT 22,2,'小华妈',88 UNION
 SELECT 23,2,'小华姐',32
 
 --4. 查找所有人及他们当中的一位年龄最大的亲属
 SELECT  c.* ,
         t.age ,
         SubName ,
         subid
 FROM    @TMain c
         LEFT JOIN ( SELECT  b.MainId ,
                             MAX(b.Age) AS Age ,
                             b.SubName ,
                             b.subid
                     FROM    @TSub b
                     GROUP BY b.MainId ,
                             b.SubName , 
 
                             b.subid
                   ) AS t ON c.MainId = t.MainId
 /*
 MainId      MainName                         age         SubName                          subid
 ----------- -------------------------------- ----------- -------------------------------- -----------
 1           小明                               48          小明爸                              11
 1           小明                               47          小明妈                              12
 1           小明                               69          小明爷                              13
 2           小华                               32          小华姐                              23
 2           小华                               88          小华妈                              22
 3           小鸟                               NULL        NULL                             NULL
 
 (6 行受影响)
 
 */

[其他解释]

引用:

SQL code?123456789101112131415161718192021222324252627282930313233343536373839404142DECLARE @TMain TABLE(MainId INT, MainName VARCHAR(32))DECLARE @TSub TABLE(SubId INT, MainId INT, SubNam……

人外有人，天外有天呀！！！
[其他解释]


--SQL
--查找所有人及他们当中的一位年龄最大的亲属

select TM.MainId,TM.MainName,M.SubId,M.SubName,M.MaxAge from @TMain TM inner join(
select ts.MainId, ts.SubId,ts.SubName,r.MaxAge from @TSub TS inner join(
select MainId,MAX(Age) MaxAge From @TSub group by MainId ) R
on TS.MainId=r.MainId and TS.Age=r.MaxAge) M
on TM.MainId=M.MainId

--结果集合
--1小明13小明爷69
--2小华22小华妈88

[其他解释]

引用:

SQL code?123456789101112--SQL--查找所有人及他们当中的一位年龄最大的亲属 select TM.MainId,TM.MainName,M.SubId,M.SubName,M.MaxAge from @TMain TM inner join(select ts.MainId, ts.SubId,ts.SubName,r.MaxAge from @……

你没有查询所有人，这已经不对了，另外你这也太复杂了吧
[其他解释]
虽然复杂，但是很好理解啊，分层次一步一步的求解
1：先分组求出每组的最大年龄
2：将1步得到的结果与@TSub 表连接得到@TSub信息
3：最后将2步结果集与@TMain 最终连接得到我们的结果

[其他解释]


CREATE  TABLE #TMain
(MainId INT,
 MainName VARCHAR(32)
) 
CREATE  TABLE #TSub
(
SubId INT, 
MainId INT, 
SubName VARCHAR(32), 
Age INT
)   
INSERT INTO #TMain 
SELECT 1,'小明' UNION
SELECT 2,'小华' UNION
SELECT 3,'小鸟'  
INSERT INTO #TSub 
SELECT 11,1,'小明爸',48 UNION
SELECT 12,1,'小明妈',47 UNION
SELECT 13,1,'小明爷',69 UNION
SELECT 22,2,'小华妈',88 UNION
SELECT 23,2,'小华姐',32   
--1. 查找每个有亲属的人及亲属 
SELECT T.MainID,T.MainName,S.SubID,S.SubName,S.Age
FROM #TMain as T inner join #TSub as S on s.MainID = T.MainID
--2. 查找每个有亲属的人 
select * from #TMain where MainID in (select MainID from #TSub group by MainID) 
--3. 查找所有人及亲属 
SELECT T.MainID,T.MainName,S.SubID,S.SubName,S.Age
FROM #TMain as T left outer join #TSub as S on s.MainID = T.MainID          
--4. 查找所有人及他们当中的一位年龄最大的亲属 
select *
from #TMain as T left outer join (select T.MainID,T.SubName,T.Age 
                                  from #TSub as T inner join (select MainID,max(Age) as Age from #TSub group by MainID) as S 
                                                       on s.age = T.age) as A  
 
                       on T.MainID = A.MainID
/*
MainId      MainName                         MainID      SubName                          Age
----------- -------------------------------- ----------- -------------------------------- -----------
1           小明                               1           小明爷                              69
2           小华                               2           小华妈                              88
3           小鸟                               NULL        NULL                             NULL

(3 行受影响)

*/

[其他解释]
谢谢诸位了。 #1 tanleittl 的最为优雅。 #7 nidexuanzhe 的思路最清晰。

请好手赐教

热点推荐