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
我这还是跑不动,谢谢大家
SELECT a.b_id
FROM a
LEFT JOIN b
ON a.b_id = b.id
GROUP BY b.id
HAVING count(b.id) = 1
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
大致这样,下班了不解释。
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 不是查询的字段, 也有逻辑问题.