SELECT *
FROM ( SELECT A.a1 AS c1 ,
a.a2 AS c2 ,
b.b4 AS c3 ,
b.b1 AS c4 ,
b.b5 AS c5
FROM t_a a
INNER JOIN T_b b ON a.a3 = b.b3
UNION ALL
SELECT a1 AS c1 ,
a2 AS c2 ,
a3 AS c3 ,
NULL ,
a4 AS c5
FROM t_a
) T
ORDER BY c1/*
c1 c2 c3 c4 c5
1 m x 1 3
1 m y 2 5
1 m z 3 11
1 m a NULL 5
2 m b NULL 12
3 m c NULL 4*/
FROM ( SELECT A.a1 AS c1 ,
a.a2 AS c2 ,
b.b4 AS c3 ,
b.b1 AS c4 ,
b.b5 AS c5
FROM t_a a
INNER JOIN T_b b ON a.a3 = b.b3
UNION ALL
SELECT a1 AS c1 ,
a2 AS c2 ,
a3 AS c3 ,
NULL ,
a4 AS c5
FROM t_a
) T
ORDER BY c1/*
c1 c2 c3 c4 c5
1 m x 1 3
1 m y 2 5
1 m z 3 11
1 m a NULL 5
2 m b NULL 12
3 m c NULL 4*/
ELSE ''
END AS c1 ,
c2 ,
c3 ,
ISNULL(c4, '') AS c4 ,
c5
FROM ( SELECT * ,
ROW_NUMBER() OVER ( PARTITION BY c1 ORDER BY GETDATE() ) AS num
FROM ( SELECT A.a1 AS c1 ,
a.a2 AS c2 ,
b.b4 AS c3 ,
b.b1 AS c4 ,
b.b5 AS c5
FROM t_a a
INNER JOIN T_b b ON a.a3 = b.b3
UNION ALL
SELECT a1 AS c1 ,
a2 AS c2 ,
a3 AS c3 ,
NULL ,
a4 AS c5
FROM t_a
) T
) A
/*
c1 c2 c3 c4 c5
1 m x 1 3
m y 2 5
m z 3 11
m a 5
2 m b 12
3 m c 4*/
最终是想要这样的结果