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

〇〇

grantguan 发表于 2012-11-7 15:40
我来一个ＳＱＬ　Ｓｅｒｖｅｒ　２０１２　的：

oracle也能这么干

jingde2012

SELECT MyDate , nvl(A.ID, 0) , nvl(B.ID, 0)
FROM A FULL OUTER JOIN B
USING (MyDate);

要考虑3种情况：
1.A表有的日期，B表无
2.A表无的日期，A表有
3.A表有的日期，B表也有

只要符合这3种情况，写出来的sql都无啥问题。注意把null补成0.

hsu_wp

LuiseDalian 发表于 2012-11-7 10:25
oracle 11g下实测

就3楼兄弟的表结构，提供mysql 版本笨方法2个：
法1：
SELECT tmp.newdate,SUM(IF(tmp.flag=1,tmp.newid,0)),SUM(IF(tmp.flag=2,tmp.newid,0))
FROM(
SELECT A.mydate AS newdate,A.id AS newid,1 AS flag FROM A
UNION ALL
SELECT B.mydate AS newdate,B.id AS newid,2 AS flag FROM B
) as tmp
GROUP BY tmp.newdate;

法2：
SELECT A.mydate,A.id,IF(B.id is NULL ,0,B.id )
FROM A LEFT  JOIN B ON A.mydate=B.mydate
UNION
SELECT B.mydate,IF(A.id is NULL ,0,A.id ),B.id
FROM B LEFT  JOIN A ON A.mydate=B.mydate
;

asialee

很好的。学习~

liuliu0317

ORACLE 10G

CREATE TABLE tab_new
(new_no NUMBER,
new_date VARCHAR2(20));

CREATE TABLE tab_old
(old_no NUMBER,
old_date VARCHAR2(20));

INSERT INTO tab_new VALUES(1,'20121101');
INSERT INTO tab_new VALUES(2,'20121102');
INSERT INTO tab_new VALUES(3,'20121105');
COMMIT;
INSERT INTO tab_old VALUES(5,'20121101');
INSERT INTO tab_old VALUES(6,'20121102');
COMMIT;

SELECT NEW_DATE, NEW_NO, NVL(OLD_NO, 0)
  FROM TAB_NEW A
  FULL JOIN TAB_OLD B
    ON A.NEW_DATE = B.OLD_DATE;

           NEW_DATE        NEW_NO        NVL(OLD_NO,0)
1        20121101                       1         5
2        20121102                       2             6
3        20121105                       3        0

wutiangou133

select a.date as 日期,a.id as 新用户,nvl(b.id,0) as 老用户 from a left join b on a.date=b.date

lucaswang007

ORACLE 9i 调试通过。

create table TEMP1
(
  RDATA DATE,
  RUSER VARCHAR2(10)
);
create table TEMP2
(
  RDATA DATE,
  RUSER VARCHAR2(10)
);


insert into TEMP1 (RDATA, RUSER)
values (to_date('05-11-2012', 'dd-mm-yyyy'), 'NEW');
insert into TEMP1 (RDATA, RUSER)
values (to_date('02-11-2012', 'dd-mm-yyyy'), 'NEW');
insert into TEMP1 (RDATA, RUSER)
values (to_date('02-11-2012', 'dd-mm-yyyy'), 'NEW');
insert into TEMP1 (RDATA, RUSER)
values (to_date('02-11-2012', 'dd-mm-yyyy'), 'NEW');
insert into TEMP1 (RDATA, RUSER)
values (to_date('01-11-2012', 'dd-mm-yyyy'), 'NEW');
insert into TEMP1 (RDATA, RUSER)
values (to_date('05-11-2012', 'dd-mm-yyyy'), 'NEW');
insert into TEMP1 (RDATA, RUSER)
values (to_date('05-11-2012', 'dd-mm-yyyy'), 'NEW');
insert into TEMP1 (RDATA, RUSER)
values (to_date('05-11-2012', 'dd-mm-yyyy'), 'NEW');
insert into TEMP1 (RDATA, RUSER)
values (to_date('05-11-2012', 'dd-mm-yyyy'), 'NEW');
insert into TEMP1 (RDATA, RUSER)
values (to_date('05-11-2012', 'dd-mm-yyyy'), 'NEW');
insert into TEMP1 (RDATA, RUSER)
values (to_date('05-11-2012', 'dd-mm-yyyy'), 'NEW');
insert into TEMP1 (RDATA, RUSER)
values (to_date('05-11-2012', 'dd-mm-yyyy'), 'NEW');
commit;

insert into TEMP2 (RDATA, RUSER)
values (to_date('01-11-2012', 'dd-mm-yyyy'), 'OLD');
insert into TEMP2 (RDATA, RUSER)
values (to_date('01-11-2012', 'dd-mm-yyyy'), 'OLD');
insert into TEMP2 (RDATA, RUSER)
values (to_date('01-11-2012', 'dd-mm-yyyy'), 'OLD');
insert into TEMP2 (RDATA, RUSER)
values (to_date('01-11-2012', 'dd-mm-yyyy'), 'OLD');
insert into TEMP2 (RDATA, RUSER)
values (to_date('01-11-2012', 'dd-mm-yyyy'), 'OLD');
insert into TEMP2 (RDATA, RUSER)
values (to_date('02-11-2012', 'dd-mm-yyyy'), 'OLD');
insert into TEMP2 (RDATA, RUSER)
values (to_date('02-11-2012', 'dd-mm-yyyy'), 'OLD');
insert into TEMP2 (RDATA, RUSER)
values (to_date('02-11-2012', 'dd-mm-yyyy'), 'OLD');
commit;

SQL> select  new,a.rdata,b.rdata  from
  2  (select rdata as new,count(*)rdata from temp1 group by rdata ) a,
  3  (select rdata as old,count(*)rdata from temp2 group by rdata ) b
  4  where   a.new=b.old(+)
  5  ;

NEW              RDATA      RDATA
----------- ---------- ----------
2012-11-1            1          5
2012-11-2            3          3
2012-11-5            8

dukope

〇〇 发表于 2012-11-7 13:04
join oldUser a on a.dt=b.dt  where b.dt is null;
没有outer是什么含义？

就是语法里面不支持 a full out join b 这样的语句

dukope

〇〇 发表于 2012-11-7 15:22
本来想写通用一些，结果mysql还没有nvl

恩 有if ifnull函数代替 mysql中decode也跟oracle 不一样