请问这个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);