【每周一议】干货讨论 如何合并两个查询的结果集的数据

buptdream

jimn1982 发表于 2012-11-7 11:30
这个的确很不错啊。很简单的问题。版主怎么哪里讨论啊。

这个问题应该是从大数据量来进行考虑吧

〇〇

lz要求用mysql实现？

smarck

除了3楼的写法没啥别的想法了，哈哈

dukope

大家都发出了oracle的,那我发一个mysql版的吧，mysql没有full out join

mysql> select * from newUser;
+---------------------+------+
| dt                  | num  |
+---------------------+------+
| 2012-11-01 00:00:00 |    1 |
| 2012-11-02 00:00:00 |    3 |
| 2012-11-05 00:00:00 |    8 |
+---------------------+------+
3 rows in set (0.00 sec)

mysql> select * from oldUser;
+---------------------+------+
| dt                  | num  |
+---------------------+------+
| 2012-11-01 00:00:00 |    5 |
| 2012-11-02 00:00:00 |    3 |
+---------------------+------+
2 rows in set (0.00 sec)

mysql> select a.dt,a.num new_num,ifnull(b.num,0) old_num from newUser a left join oldUser b on a.dt=b.dt union all select a.dt,a.num new_num,ifnull(b.num,0) old_num from newUser b join oldUser a on a.dt=b.dt  where b.dt is null;
+---------------------+---------+---------+
| dt                  | new_num | old_num |
+---------------------+---------+---------+
| 2012-11-01 00:00:00 |       1 |       5 |
| 2012-11-02 00:00:00 |       3 |       3 |
| 2012-11-05 00:00:00 |       8 |       0 |
+---------------------+---------+---------+
3 rows in set (0.00 sec)

smarck

〇〇 发表于 2012-11-7 12:42
lz要求用mysql实现？

看主题的所在版块，貌似是哦。

〇〇

dukope 发表于 2012-11-7 12:47
大家都发出了oracle的,那我发一个mysql版的吧，mysql没有full out join

mysql> select * from newUser;
...

join oldUser a on a.dt=b.dt  where b.dt is null;
没有outer是什么含义？

〇〇

1. 
   
2. [oracle@redflag11012501 home]$ mysql -uroot
   
3. mysql: Unknown OS character set 'GB18030'.
   
4. mysql: Switching to the default character set 'latin1'.
   
5. Welcome to the MySQL monitor.  Commands end with ; or \g.
   
6. Your MySQL connection id is 1
   
7. Server version: 5.5.13-log MySQL Community Server (GPL)
   
8. 
   
9. Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
   
10. 
    
11. Oracle is a registered trademark of Oracle Corporation and/or its
    
12. affiliates. Other names may be trademarks of their respective
    
13. owners.
    
14. 
    
15. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
16. 
    
17. mysql> create table ou (dt date, uid int);
    
18. ERROR 1046 (3D000): No database selected
    
19. mysql> use test
    
20. Database changed
    
21. mysql> create table ou (dt date, uid int);
    
22. Query OK, 0 rows affected (0.02 sec)
    
23. 
    
24. mysql> create table nu (dt date, uid int);
    
25. Query OK, 0 rows affected (0.01 sec)
    
26. 
    
27. mysql> insert into ou values(date' 2012-11-1
    
28.     '> ',1);
    
29. Query OK, 1 row affected (0.01 sec)
    
30. 
    
31. mysql> insert into ou values(date' 2012-11-2
    
32. ',3);
    
33. Query OK, 1 row affected (0.01 sec)
    
34. 
    
35. mysql> insert into ou values(date' 2012-11-5
    
36. ',8);
    
37. Query OK, 1 row affected (0.00 sec)
    
38. 
    
39. mysql> insert into nu values(date' 2012-11-1
    
40. ',5);
    
41. Query OK, 1 row affected (0.00 sec)
    
42. 
    
43. mysql> insert into nu values(date' 2012-11-2
    
44. ',3);
    
45. Query OK, 1 row affected (0.00 sec)
    
46. 
    
47. mysql> select * from ou;
    
48. +------------+------+
    
49. | dt         | uid  |
    
50. +------------+------+
    
51. | 2012-11-01 |    1 |
    
52. | 2012-11-02 |    3 |
    
53. | 2012-11-05 |    8 |
    
54. +------------+------+
    
55. 3 rows in set (0.02 sec)
    
56. 
    
57. mysql> select * from nu;
    
58. +------------+------+
    
59. | dt         | uid  |
    
60. +------------+------+
    
61. | 2012-11-01 |    5 |
    
62. | 2012-11-02 |    3 |
    
63. +------------+------+
    
64. 2 rows in set (0.00 sec)
    
65. 
    
66. mysql> select o.dt,ou.uid,nu.uid from(select dt from ou union select dt from nu)o left join ou using(dt) left join nu using(dt);
    
67. +------------+------+------+
    
68. | dt         | uid  | uid  |
    
69. +------------+------+------+
    
70. | 2012-11-01 |    1 |    5 |
    
71. | 2012-11-02 |    3 |    3 |
    
72. | 2012-11-05 |    8 | NULL |
    
73. +------------+------+------+
    
74. 3 rows in set (0.00 sec)
    
75. 
    
76. mysql> select o.dt,ifnull(ou.uid,0)newu,ifnull(nu.uid,0)oldu from(select dt from ou union select dt from nu)o left join ou using(dt) left join nu using(dt);
    
77. +------------+------+------+
    
78. | dt         | newu | oldu |
    
79. +------------+------+------+
    
80. | 2012-11-01 |    1 |    5 |
    
81. | 2012-11-02 |    3 |    3 |
    
82. | 2012-11-05 |    8 |    0 |
    
83. +------------+------+------+
    
84. 3 rows in set (0.01 sec)
    
85. 
    
86. mysql>
    

复制代码

smarck

mysql直接插入好几条即可
INSERT INTO t able1 VALUES(1),(2),(3),(4),(5);

〇〇

smarck 发表于 2012-11-7 15:01
mysql直接插入好几条即可
INSERT INTO t able1 VALUES(1),(2),(3),(4),(5);

本来想写通用一些，结果mysql还没有nvl

grantguan

我来一个ＳＱＬ　Ｓｅｒｖｅｒ　２０１２　的：

1. create table a(
   
2.   id  int,
   
3.   Mydate Date
   
4. )
   
5. insert into a
   
6. select 1,'2012-11-1' union all
   
7. select 3,'2012-11-2' union all
   
8. select 8,'2012-11-5'
   
9. 
   
10. create table b(
    
11.   id  int,
    
12.   Mydate Date
    
13. )
    
14. insert into b
    
15. select 5,'2012-11-1' union all
    
16. select 3,'2012-11-2'
    
17. 
    
18. ;with cte as(
    
19.   select *,1 Num from a
    
20.   union all
    
21.   select *, 2 Num from b
    
22. )
    
23. select MyDate,
    
24.        max(case Num when 1 then id else 0 end) new_user,
    
25.            max(case Num when 2 then id else 0 end) old_user
    
26. from cte
    
27. group by myDate

复制代码