请问这个mysql语句要怎么写？

Question

不能使用in 的情况下，要怎么把t1对应t2没有0的数据找出来？
比如这个图 结果是 8、9 H ,因为H没有为0的t2。

Answer 1

方法1:

SELECT
  `a1`.`*`
FROM
  `test` `a1`
  LEFT JOIN `test` `a2`
    ON `a2`.`t1` = `a1`.`t1`
    AND `a2`.`t2` = 0
WHERE `a2`.`id` IS NULL;

方法2:

SELECT
  *
FROM
  `test`
WHERE `t1` IN
  (SELECT
    `t1`
  FROM
    `test`
  GROUP BY `t1`
  HAVING COUNT(IF(`t2` = 0, 1, NULL)) = 0);

方法3:

SELECT
  `a1`.`*`
FROM
  `test` `a1`
WHERE NOT EXISTS
  (SELECT
    `id`
  FROM
    `test` `a2`
  WHERE `a2`.`t1` = `a1`.`t1`
    AND `a2`.`t2` = 0);

Answer 2

select from (select from (select * from test GROUP BY t1,t2) as a GROUP BY t1) as b where t2>0;
这个也行

Answer 3

select
    t1
from
    test
group by
    t1
    having count(*) = sum(case when t2!=0 then 1 else 0 end );