sql写的有问题，求指证，目的删选唯一id,谢谢

Question

SELECT `b_id`
FROM `a` 
GROUP BY `b_id` 
HAVING COUNT(`b_id`)=1
AND `b_id`
IN (SELECT `id`
FROM `b` 
GROUP BY `somefield` 
HAVING COUNT(`id`)=1)



改进版：
SELECT `b_id`
FROM `a` 
WHERE `b_id` IN (SELECT `id`
FROM `b` 
GROUP BY `somefield` 
HAVING COUNT(`id`)=1)
GROUP BY `b_id` 
HAVING COUNT(`b_id`)=1

我这还是跑不动，谢谢大家

Answer 1

SELECT a.b_id
FROM a
LEFT JOIN b
ON a.b_id = b.id
GROUP BY b.id
HAVING count(b.id) = 1

Answer 2

SELECT 
    `b_id`
FROM `a`
    WHERE `a`.`b_id` IN (SELECT `id` FROM `b` GROUP BY (`id`) HAVING COUNT(`id`) = 1)
GROUP BY `b_id`
HAVING COUNT(`b_id`) = 1

大致这样，下班了不解释。

Answer 3

select a.bid from (
select bid from a group by bid having count(bid)=1
) as a
left join (
select id from b group by id having count(id)=1
) as b on a.bid=b.id

你的语句中

SELECT `id`
FROM `b` 
GROUP BY `somefield` 
HAVING COUNT(`id`)=1

实际上按照 sql 的语法规则来说 你查询的是 id 那么 group by 的字段也应该是 id 而不是 somefield, MySQL 似乎支持这么玩, 标准的 tsql 语句应该不行的, 而且 group by 不是查询的字段, 也有逻辑问题.