如何使MySQL关联查询返回结果集中相同字段不同数据 只返回一条

Question

* tableA
* id name 
* 1 张三
* 
* tableB
* id  a_id  yifu
* 1 1       短袖
* 2 1       长袖
* 
* 
* 要求返回结果
* name   b_id1      yifu1       b_id2    yifu2
* 张三       1             短袖        2             长袖

Answer 1

列转行，然后在行转列就ok了。下面是sqlServer的代码，可以参考下。

--测试数据
use Tempdb
go
--> --> 听雨停了-->生成测试数据

if not object_id(N'Tempdb..#tabA') is null
    drop table #tabA
Go
Create table #tabA([id] int,[name] nvarchar(22))
Insert #tabA
select 1,N'张三'
Go
Select * from #tabA

use Tempdb
go
--> --> 听雨停了-->生成测试数据

if not object_id(N'Tempdb..#tabB') is null
    drop table #tabB
Go
Create table #tabB([id] int,[a_id] int,[yifu] nvarchar(22))
Insert #tabB
select 1,1,N'短袖' union all
select 2,1,N'长袖'
Go
Select * from #tabB
--测试数据结束

SELECT NAME,
       id_1,
       yifu_1,
       id_2,
       yifu_2
FROM   (
           SELECT NAME,
                  c + '_' + CONVERT(VARCHAR, rn) AS 'c',
                  v
           FROM   (
                      SELECT NAME,
                             CONVERT(VARCHAR(10), id) AS id,
                             CONVERT(VARCHAR(10), yifu) yifu,
                             ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY id) AS 
                             rn
                      FROM   (
                            SELECT a.name,b.id,b.yifu 
                            FROM #tabA AS a 
                            INNER JOIN #tabB AS b ON a.id=b.a_id    
                      )aa
                  ) a
                  UNPIVOT(v FOR c IN ([id], yifu)) u
       ) b
       PIVOT(MAX(v) FOR c IN (id_1, yifu_1, id_2, yifu_2)) p


NAME                   id_1       yifu_1     id_2       yifu_2
---------------------- ---------- ---------- ---------- ----------
张三                     1          短袖         2          长袖