Sql找到具有相同值的另一个表，并在输出中添加它们的ID

Question

I've got the following two SQL tables (in MySQL):

 Posts Tables
 +----+---------------+--------+----------+ 
 | id | title         |  slug  | language |
 +----+---------------+--------+----------+ 
 | 1  | Post title    | slug_1 |  eng     |
 | 2  | Another title | slug_2 |  eng     |
 | 3  | Title German  | slug_1 |  ger     |
 | 4  | Again German  | slug_3 |  ger     |
 | 5  | Russian title | slug_1 |  rus     |
 +----+---------------+--------+----------+ 

In output i am have to get list of all Posts and in the same line of array the information of other posts (id) where slug is same. Something like this

 +----+------------+--------+----------+--------------+---------------+
 | id | title      |  slug  | language | german trans | russian trans |
 +----+------------+--------+----------+--------------+---------------+
 | 1  | Post title | slug_1 |  eng     | 3            | 5
 | 2  | Another ti | slug_2 |  eng     | null         | null
 | 4  | German tit | slug_3 |  null    | 4            | null

First i decided do this-sorting after getting list of posts with foreach loop but it takes a really big resource when list of posts is big. so i think in sql it will be much faster but i dont know i to do this.

Answer 1

You seem to want the first post, along with information about translations. If you know the languages then this probably does what you want:

select s.minid,
       max(case when p.id = s.minid then title end) as title,
       s.slug,
       max(case when p.id = s.minid then language end) as language,
       max(case when p.lang = 'ger' then id end) as German,
       max(case when p.lang = 'rus' then id end) as Russian
from posts p join
     (select slug, min(id) as minid
      from posts p
      group by slug
     ) s
     on p.slug = s.slug
group by p.slug;