MySQL连接查询，但如果它是重复的，则不显示第一个表的结果

问题描述：

So basically I have 2 tables:

table1

id| name
1   Test
2   Something
3   More

table2

id| table1_id
1   1
2   1
3   1
4   2
5   2
6   3

Now I need the result to be like this:

name      | table2.id
Test        1
            2
            3
Something   4
            5
More        6

So basically no duplicate entries from the first table. So the exact same results as joining it but without showing the name more than once. Is this possible in MySQL or should I just filter it in PHP? I know it's possible in PHP but I am wondering if something like this is achievable in MySQL if so, I'd like to know what to look for. I was thinking something with DISTINCT and/or a left or right join.

基本上我有2个表： p>

table1 强> p>

  ID |  name 
1 Test 
2 Something 
3 More 
  code>  pre> 
 
   table2  strong>  p> 
 
 
  id  |  table1_id 
1 1 
2 1 
3 1 
4 2 
5 2 
 3 3 
  code>  pre> 
 
 现在我需要结果如下： p>  
 
 
  name |  table2.id 
Test 1 
 2 
 3 
Something 4 
 5 
更多6 
  code>  pre> 
 
 所以基本上没有来自第一个表的重复条目。 所以与加入它的结果完全相同，但没有多次显示名称。 这在MySQL中是可行的还是我应该在PHP中过滤它？ 我知道在PHP中这是可能的，但我想知道如果这样的话在MySQL中是否可以实现，我想知道要寻找什么。 我正在考虑使用DISTINCT和/或左或右连接。 p> 
  div>

答

So, you asked if it is possible with MySQL and I answered in comments that it is. If your question was how can I accomplish this with only MySQL, here it is:

SELECT
tmp.name,
tbl2.id
FROM
    tbl2
LEFT JOIN (
SELECT
    tbl2.id AS id,
    tbl1.`name` AS name
FROM
    tbl2
INNER JOIN tbl1 ON tbl1.id = tbl2.tbl1_id
GROUP BY
    tbl2.tbl1_id
) AS tmp ON tbl2.id = tmp.id;

Hope it is what you wanted.

As @roberto06 suggested, this query returns NULL instead of duplicates, but if you don't like NULLs and want an empty string you can change SELECT tmp.name to SELECT IFNULL(tmp.name,'')

答

This is a question of presenting the query result, so I would opt for PHP, which is better at handling this than MySQL.

答

Something like this:

select distinct t1.name, t2.id from table1 as t1 outer right join table2 as t2 on t1.id = t2.table1_id

答

You can try like...

select A.name,B.id from table1 A
right join table2 B on A.id=B.table1_id

答

What about this?

SELECT
*, 
(SELECT GROUP_CONCAT(CONCAT(`id`) separator '|') AS table2_ids 
FROM table2 WHERE table1_id = table1.id) AS m
FROM table1

In PHP, you just need to explode('|', $mvar)

答

Found it !

SELECT (
    SELECT
        IF(COUNT(t2_2.table1_id) = 1, t1.name, '')
        FROM table2 t2_2
        WHERE t2_2.id <= t2.id
            AND t2_2.table1_id = t1.id
    ) AS name,
    t2.id
FROM table1 t1
    LEFT JOIN table2 t2 ON t2.table1_id = t1.id

Results:

name      | .id
----------+-----
Test      |  1
          |  2
          |  3
Something |  4
          |  5
More      |  6

Explanation :

This will count the number of occurences of table1_id IN t2_2 where t2_2.id is lower or equal than the actual t2.id and where t2_2.table1_id is equal to t1.id
- So this will return 1, 2, 3, 1, 2, 1 in your table example
Then it'll output t1.name if it is evaluated to 1 and an empty string if not.

I don't know if it's clear enough, and I'm pretty sure the performance could be enhanced but hey, it works.

MySQL连接查询，但如果它是重复的，则不显示第一个表的结果

相关推荐