MYSQL高级选择不同的值与相应的ID，而不是在表中

EDITED:

编辑:

I have a huge problem with SELECT DISTINCT values from one of my table.

我有一个很大的问题，从我的表中选择不同的值。

Table 1 :T1

表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

Table 2 :T2

表2:T2

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

I need to SELECT all DISTINCT sub-things for every thing from Table T1 which are NOT in Table T2 and, most important - only sub-thing with the highest tnumber (for ex. with max(id)).

我需要为表T1中的每一项选择所有不同的子项，这些子项不在表T2中，最重要的是——只选择tnumber最高的子项(例如max(id)))。

Result Should leave records 2, 4 from T1 because there are identical sub-things with higher tnumber and of course leave record 3 from T1 because it's in the T2. Final result should be like this

结果应该在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

Thanks to everyone here, the syntax I've tried is:

感谢这里的每个人，我尝试过的语法是:

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) ;

Now I need to add a condition that the records from T1 where T1.pid is NOT in T2.pid AND t2.h1=false should NOT be given in final result.

现在我需要添加一个条件，从T1开始记录到T1。pid不在T2中。pid和t2。h1=false不应在最终结果中给出。

Big Thanks in advance for any help.

非常感谢你的帮助。

5 个解决方案

#1

This query will return the MAX(id) for every subthing:

这个查询将返回每个子项的最大(id):

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

(i think that you want to exclude all subthings that are present in t2)

(我认为你想排除t2中的所有子元素)

Then with this query you can retrieve all the other columns for the maximum id:

然后，通过这个查询，您可以检索所有其他列的最大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
  )

Please see a fiddle here. If yow want better performances, you could try with a 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

(please make sure that t1.id is indexed, and also an index on subthing on both tables should help).

(请确保t1。id是被索引的，而且两个表上的子项上的索引也应该有帮助)。

#2

try left outer join:

试试左外连接:

    select distinct t1.id, t1.thing, t1.subthing, t1.tnumber from t1 inner join (SELECT t1.thing, subthing, max(t1.tnumber) as tnumber 
FROM t1 left outer join t2 using (subthing) where t2.subthing is NULL GROUP BY subthing, thing) as b using (subthing, thing, tnumber) ;

create a concatenated Index:

创建一个连接指数:

create index index_name t1(subthing, thing, tnumber);

#3

I Found One issue in following LFJ syntax:

我在以下LFJ语法中发现了一个问题:

SELECT t1.id, t1.thing, subthing, max(t1.tnumber) as tnumber
FROM t1 left outer join t2 using (subthing) where t2.subthing is NULL GROUP BY subthing, thing;

will result

将结果

4       A1214       A01B00001     123459

in stead of

代替

6       A1214       A01B00001     123461

id=4 is the first ID for that sub-thing but number 123461 is from the max tnumber but max tnumber is in record id=6.

id=4是该子对象的第一个id，但编号123461来自最大tnumber，但max tnumber在记录id=6中。

I think syntax should start with:

我认为语法应该从:

SELECT max(t1.id), t1.thing (...)

#4

Some modification I've made based on @LFJ syntax.

我做了一些基于@LFJ语法的修改。

'changed `t1.id` column to `t1.pid`'
'changed `t2.id_table_t1` column to `t2.pid`'
'and JOINed tables via `pid` columns'
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) ;

Result is the same but comparision is via pid column (old ID) , not tnumber column, which means even if in the T2.tnumber is whatever value the result will be the same :)

结果是相同的，但是比较是通过pid列(旧ID)，而不是tnumber列，这意味着即使在T2中。tnumber是结果相同的值)

I think this is the end. Maybe it'll help somebody in future. Thanks a lot.

我想这就是结局。也许它将来会对某些人有所帮助。非常感谢。

#5

Try something like that ?

试试这样的东西?

SELECT *
FROM T1
WHERE CONCAT_WS('-', thing, `sub-thing`, tnumber) IN
(SELECT CONCAT_WS('-', thing, `sub-thing`, max_tnumber)
FROM (
    SELECT MAX(tnumber) AS max_tnumber, thing, `sub-thing`
    FROM T1
    WHERE T1.`sub-thing` NOT IN (SELECT `sub-thing` FROM T2)
    GROUP BY thing, `sub-thing`
) as view
);

#1