LEFT JOIN具有特定条件

问题描述：

I have two tables

table A
---------
uid   c1(distinct)
---------
 1     a
 1     b
 2     c
 3     d
 3     e
 3     f
 4     g

and

table B
---------
uid   c2(non unique)
---------
 1     x
 2     y
 3     z
 3     z  
 3     v

I need a resultant table which maps table A to table B on uid like this

Result Table
-------------
uid   c1   c2
-------------
 1     a    x
 1     b    -
 2     c    y
 3     d    z
 3     e    z
 3     f    v
 4     g    -

Basically once a row from table B is used in the resultant table, it must be exhausted. None of the JOINS work in this case.

Both these tables contain large number of records, so I can't write individual queries for each uid.

我有两个表 p>

 表A 
--  ------- 
uid c1（distinct）
 --------- 
 1 a 
 1 b 
 2 c 
 3 d 
 3 e 
 3 f 
  4 g 
  code>  pre> 
 
 和 p> 
 
 
 表B 
 --------- 
uid c2  （非唯一）
 --------- 
 1 x 
 2 y 
 3 z 
 3 z 
 3 v 
  code>  pre> 
 
 我需要一个结果表，它将表A映射到uid上的表B，如此 p> 
 
  结果表
 ------------- \  nuid c1 c2 
 ------------- 
 1 ax 
 1 b  -  
 2 cy 
 3 dz 
 3 ez 
 3 fv 
 4 g  -  
   code>  pre> 
 
 基本上，一旦在结果表中使用了表B中的一行，就必须将其耗尽。
在这种情况下，没有JOINS工作。 p> 
  
 
这两个表都包含大量记录，因此我无法为每个uid编写单独的查询。 p> 
  div>

答

Let's count the rows with the same uid and join equal uids and that numbers

select t.uid, c1, ifnull(c2, '-') c2 
    from 
      (select *, @n:=if(@i=uid, @n+1, if(@i:=uid, 1, 1)) n 
           from tableA 
               cross join 
                (select @n:=0, @i:='') param1 
          order by uid
      ) t 
    left join 
      (select *, @n1:=if(@i1=uid, @n1+1, if(@i1:=uid, 1, 1)) n1 
           from tableB 
               cross join 
                (select @n1:=0, @i1:='') param2 
         order by uid
      ) tt 
          on t.uid=tt.uid and t.n=tt.n

LEFT JOIN具有特定条件

相关推荐