这个怎么样？

SELECT * FROM 
(SELECT * FROM sof t1
WHERE (SELECT COUNT(id) FROM sof t2 WHERE t2.id = t1.id) < 3) as temp2
WHERE id IN (SELECT id FROM sof WHERE value = 'DDD')

输入和输出至少和我这边的案子相符。
演示：http://rextester.com/azla7822

您可以使用同一个表的自联接，内部查询将计算每个id的计数，并过滤计数小于3的行

select a.*
from table1 a
join (
    select id, count(*) total
    from table1
    group by id
    having total < 3
    and sum(`Value` = 'DDD') > 0
) t using(id);

演示
或

select a.*
from table1 a
where  (
    select count(*)
    from table1
    where ID = a.ID 
    having sum(`Value` = 'DDD') > 0
) < 3

但我更喜欢加入法
更新的演示