编辑：

我从一个表中的SELECT DISTINCT值遇到了一个大问题.

表1：T1

pid      thing       sub-thing     tnumber     
-------------------------------------------
1       A1212       A01A00001     123456      
2       A1212       A01A00002     123457     
3       A1212       A01A00002     123458      
4       A1214       A01B00001     123459     
5       A1214       A01B00002     123460        
6       A1214       A01B00001     123461         
7       A1217       C01A00001     123462     

表2：T2

id        pid      thing       sub-thing         tnumber    h1 
-------------------------------------------------------------- 
1          3            A1212       A01A00002     123458   False

我需要为表T1中的所有事物选择所有DISTINCT子事物,这些事物不在表T2中,最重要的是-仅具有最高tnumber的子事物(例如,具有max(id)的事物).

结果应该从T1留下记录2、4,因为存在相同的子事物,其中tnumber更高；当然,从记录T1离开记录3,因为它在T2中.
最终结果应该是这样

id      thing       sub-thing     tnumber     
-------------------------------------------
1       A1212       A01A00001     123456                 
5       A1214       A01B00002     123460        
6       A1214       A01B00001     123461         
7       A1217       C01A00001     123462  

感谢这里的所有人,我尝试过的语法是：

SELECT DISTINCT t1.pid, t1.thing, t1.subthing, t1.tnumber 
FROM t1 INNER JOIN
(SELECT t1.thing, t1.subthing, max(t1.tnumber) as tnumber FROM t1 LEFT OUTER JOIN t2 o ON t1.pid=o.pid WHERE o.pid IS NULL
AND t1.added>'2015-10-31' 
GROUP BY t1.subthing, thing) 
as b using (subthing, thing, tnumber) ;

现在,我需要添加一个条件,即不应在最终结果中给出来自T1的记录,其中T1.pid不位于T2.pid和t2.h1 = false中.

解决方法:

该查询将为每个子对象返回MAX(id)：

SELECT subthing, MAX(id) AS max_id
FROM t1
WHERE subthing NOT IN (SELECT subthing FROM t2) 
GROUP BY subthing;

(我认为您想排除t2中存在的所有子内容)

然后,通过此查询,您可以检索其他所有列以获取最大ID：

SELECT t1.*
FROM t1
WHERE ID IN (
    SELECT MAX(id) AS max_id
    FROM t1
    WHERE subthing NOT IN (SELECT subthing FROM t2) 
    GROUP BY subthing
  )

请查看小提琴here.如果希望获得更好的性能,则可以尝试使用LEFT JOIN：

SELECT ta.*
FROM
  t1 AS ta LEFT JOIN t1 AS tb
  ON ta.subthing=tb.subthing
     AND ta.id<tb.id
WHERE
  ta.subthing NOT IN (SELECT subthing FROM t2)
  AND tb.id IS NULL

(请确保对t1.id进行了索引,并且两个表上的子目录索引也应有所帮助).