mysql 查询并集记要

2014 年 12 月 16 日 jslfl mysql

两张表tb_a,tb_b数据分别如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

tb_a:
id uid f1
--------------------
1 1 11
2 1 12
3 1 13
4 1 14
5 2 15
6 2 16
7 4 177

tb_b:
id uid f1
--------------------
1 1 21
2 1 22
3 1 23
4 2 24
5 2 25
6 3 26
7 3 27

最终所需效果为(对uid对应的f1属性进行统计,如MAX,SUM等)
uid MAX(a.f1) MAX(b.f1)
---------------------------------
1 14 23
2 16 25
3 NULL 27
4 177 NULL

SQL:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

SELECT IFNULL(t.u1,t.u2) AS uid,t.f1,t.f2 FROM(
SELECT t1.uid AS u1,t1.f1 AS f1,t2.uid AS u2,t2.f1 AS f2 FROM(
SELECT uid,MAX(f1) AS f1 FROM tb_a GROUP BY uid
) t1 LEFT JOIN(
SELECT uid,MAX(f1) AS f1 FROM tb_b GROUP BY uid
) t2 ON t1.uid=t2.uid

UNION

SELECT t1.uid AS u1,t1.f1 AS f1,t2.uid AS u2,t2.f1 AS f2 FROM(
SELECT uid,MAX(f1) AS f1 FROM tb_a GROUP BY uid
) t1 RIGHT JOIN(
SELECT uid,MAX(f1) AS f1 FROM tb_b GROUP BY uid
) t2 ON t1.uid=t2.uid
) t ORDER BY uid

主要原理:left join + union + right join
union会对结果去重复,在大数据量下可能较耗时

mysql

Comments are currently closed.

mysql 查询并集记要

近期文章

近期评论

文章归档

分类目录

标签