对于这些数据集，具有子查询结果的内部联接是不同的

b91juud3 于 2021-07-26 发布在 Java

关注(0)|答案(2)|浏览(325)

CREATE TABLE IF NOT EXISTS `wcd` (
  `id` int(6) unsigned NOT NULL,
  `wid` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;
INSERT INTO `wcd` (`id`, `wid`) VALUES
  ('168', '5685'),
  ('167', '5685'),
  ('166', '5685'),
  ('165', '5685'),
  ('164', '5685'),
  ('163', '5685'),
  ('162', '5684'),
  ('161', '5684');

  CREATE TABLE IF NOT EXISTS `cases` (
  `id` int(6) unsigned NOT NULL,
  `wcd_id` int(11) unsigned NOT NULL,
  `reason_id` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;
INSERT INTO `cases` (`id`, `wcd_id`, `reason_id`) VALUES
  ('20', '168', '4'),
  ('19', '168', '1'),
  ('18', '167', '6'),
  ('17', '167', '5'),
  ('16', '166', '4'),
  ('15', '166', '1'),
  ('14', '165', '4'),
  ('13', '165', '1'),
  ('12', '164', '1'),
  ('11', '163', '4'),
  ('10', '162', '1'),
  ('9', '162', '4'),
  ('8', '162', '5'),
  ('7', '161', '5'),
  ('6', '161', '6');

上面的两个表与wcd.id=cases.wcd\u id有foreignkey关系，让我们考虑与wcd.wid 5865相关的记录。结果应按reason\u id和condition max（cases.id）分组
我使用下面的查询来实现这一点，并得到了预期的结果。

SELECT d.id, d.wid, c.* FROM wcd d
LEFT JOIN cases c ON c.wcd_id = d.id
inner JOIN (SELECT MAX(id) AS max_id FROM cases GROUP BY reason_id) c2
ON c2.max_id = c.id
WHERE d.wid = 5685;

结果：

id  wid     id  wcd_id  reason_id
168 5685    19  168     1
168 5685    20  168     4
167 5685    17  167     5
167 5685    18  167     6

对于5684的同一查询，尽管有可用的数据，但该查询返回0行。但我在等下面的几行。

id  wid     id  wcd_id  reason_id
162 5684    10  162     1
162 5684    9   162     4
162 5684    8   162     5
161 5684    6   161     6

查询有什么问题，需要更改什么才能得到5684的上述结果。？
这里是sqlfiddle链接

sql mysql Join mysql-5.6 inner-join

来源：https://stackoverflow.com/questions/62205057/inner-join-with-subquery-results-differs-for-these-data-sets

2条答案

按热度按时间

zpgglvta1#

你需要回顾过去 wcd 表进行属性关联，因为您需要具有“最新”原因的行的id wid -该列在中不可用 cases .
在mysql 8.0中，我们只需要 row_number() ... 但是你把你的问题标记为MySQL5.6。我发现最简单的表达方式是使用相关子查询：

SELECT d.id, d.wid, c.* 
FROM wcd d
INNER JOIN cases c ON c.wcd_id = d.id
WHERE c.id = (
    SELECT max(c2.id)
    FROM wcd d2
    INNER JOIN cases c2 ON c2.wcd_id = d2.id
    WHERE d2.wid = d.wid AND c2.reason_id = c.reason_id
)
AND d.wid = 5685;

赞(0）回复(0）举报 2021-07-26

bvuwiixz2#

然后必须使用min并去掉where子句

('162', '5684')
  ('161', '5684')

因为

SELECT 
d.id
, d.wid
, 
c.*
FROM 
wcd d
LEFT JOIN 
cases c 
ON c.wcd_id = d.id
inner JOIN (SELECT MIN(id) AS min_id FROM cases GROUP BY reason_id) c2
ON c2.min_id = c.id

看到了吗http://sqlfiddle.com/#!9/fb4569/26号

赞(0）回复(0）举报 2021-07-26

我来回答

对于这些数据集，具有子查询结果的内部联接是不同的

2条答案

相关问题

热门标签

最新问答