代码之家 › 专栏 › 技术社区 › martin

有趣的多sql连接

inner-join left-join subquery many-to-many sql

martin · 技术社区 · 16 年前

我有三个相关的表“A(id,val)”,“B(id,val)”,以及一个值为“AB(aid,bid,val)”的链接表

SELECT A.* 
FROM A INNER JOIN AB ON A.id = AB.aid INNER JOIN B ON AB.bid = B.id
WHERE B.val = 'foo';

每个A有许多B,每个B有许多A。

我要讨论的问题是需要过滤集合,以便查询只在绝对值是任何给定a/B对的最大值

E、 g.如果我有数据:

A

id   val
1    something
2    somethingelse

id   val
1    foo
2    bar

AB型

aid  bid  val
1    1    3
1    2    2
2    1    1
2    2    4

我只想选择AB的第一行和最后一行,因为它们是每个A的最大值,然后可以查询B.val='foo'来只返回第一行。我不知道如何仅约束AB表中的max-val行。

我能得到的最好的就是

SELECT * 
FROM A 
INNER JOIN 
  (SELECT aid, bid, MAX(val) AS val FROM AB GROUP BY aid) as AB
  ON A.id = AB.aid 
INNER JOIN B ON AB.id = B.id
WHERE B.val = 'foo'

但这不太管用。首先,它感觉是错误的方法,其次,它返回的出价值不好。也就是说,从子查询返回的bid不一定与max(val)来自同一行。我相信这是一个已知的分组问题,当没有为排序规则或分组指定列时,返回值的选择是未定义的。

我希望以上的一些说法是有道理的,过去几个小时我一直在为此事头痛,任何帮助都将不胜感激。谢谢。

(对于那些想知道的人来说,它的实际用途是用于字典后端,其中a是单词表,B是音素表。AB是带有“position”列的wordphone表。查询是查找以指定音位结尾的所有单词。(音位是一个单词的声音,在用法上类似于国际音标)

5 回复 | 直到 16 年前

Blorgbeard 16 年前

我认为你需要做另一个连接得到ab的最大值为每个a.id第一。

select a.*
from a
left join (
    select aid, max(val) as val 
    from ab 
    group by aid
) abmax on abmax.aid=a.id
inner join ab on ab.aid=abmax.aid and ab.val=abmax.val
inner join b on b.id=ab.bid
where b.val='foo'

Bill Karwin 16 年前

我经常使用以下技巧来获得最大的每一组:

SELECT a.*
FROM ab AS ab1
  LEFT OUTER JOIN ab AS ab2 ON (ab1.aid = ab2.aid AND ab1.val < ab2.val)
  JOIN a ON (ab1.aid = a.id)
  JOIN b ON (ab1.bid = b.id)
WHERE ab2.aid IS NULL
  AND b.val = 'foo';

patmortech 16 年前

我刚刚测试了另一种方法:

select a.*
from ab
   inner join b on(ab.bid=b.id)
   inner join a on (ab.aid=a.id)
where ab.val = (select max(val) from ab AS ab2 where ab2.aid = ab.aid)
   and b.val='foo'

MBoy 16 年前

我不确定您使用的是哪种sql,但在MS sql中,我创建了一个表值数据库函数,从表a返回最大值,然后将其联接到表B。我发现在稍后的阶段回顾我的查询时,这比复杂联接更容易理解。

Amy B 16 年前

SELECT *
FROM
(
  SELECT
    A.*,
    (SELECT top 1 AB.BID FROM AB WHERE A.AID = AB.AID ORDER BY AB.val desc) as BID
  FROM A
) as Aplus
JOIN B ON Aplus.BID = B.BID