解决方案 »
- Oracle10g字符集问题
- 我这样写只能查询 T2.SN = '2303' 的一个结果,我想一次同时查询出 SN = 2021,2022,2023,2024......对应的NAME结果集
- 找一老师
- 急!问个sql语句问题,多谢
- 建这个view为什么没有权限
- 关于oracle数据库的连接问题。
- 如何替换to_char(<date>, <format>), 使它成为标准SQL
- 用JDBC连ORACLE,这个JDBC的环境要用什么呢,JSWDK?JAVA WEB SERVER?好象有很多可以的,菜菜迷茫ING
- 哪位大侠可否告知哪有oracle的电子书下载?
- 大量的db file sequential read和log file sync等待事件,有何解决方法
- DBMS_CHANGE_NOTIFICATION 问题,100分求答案
- 求助一个sql怎么写
SELECT 2,'1002','2014-03-05' FROM dual UNION ALL
SELECT 3,'1001','2014-03-05' FROM dual UNION ALL
SELECT 4,'1005','2014-03-01' FROM dual ),
b AS(
SELECT 1 ID,'1003' usercode,'2014-03-05' createdate FROM dual UNION ALL
SELECT 2,'1002','2014-03-05' FROM dual UNION ALL
SELECT 3,'1004','2014-03-05' FROM dual UNION ALL
SELECT 4,'1005','2014-03-05' FROM dual UNION ALL
SELECT 5,'1005','2014-03-05' FROM dual )
SELECT DECODE(C.USERCODE, NULL, D.USERCODE, C.USERCODE),
DECODE(C.ANUM, NULL, 0, C.ANUM),
DECODE(D.BNUM, NULL, 0, D.BNUM)
FROM ((SELECT USERCODE, COUNT(1) ANUM FROM A GROUP BY USERCODE) C FULL JOIN
(SELECT USERCODE, COUNT(1) BNUM FROM B GROUP BY USERCODE) D ON
C.USERCODE = D.USERCODE)
ORDER BY DECODE(C.USERCODE, NULL, D.USERCODE, C.USERCODE)DECODE(C.USERCODE,NULL,D.USERC DECODE(C.ANUM,NULL,0,C.ANUM) DECODE(D.BNUM,NULL,0,D.BNUM)
------------------------------ ---------------------------- ----------------------------
1001 2 0
1002 1 1
1003 0 1
1004 0 1
1005 1 2你写的结果有误吧
SELECT 1 ID,'1001' usercode,'2013-03-05' updatecode FROM dual UNION ALL
SELECT 2,'1002','2014-03-05' FROM dual UNION ALL
SELECT 3,'1001','2014-03-05' FROM dual UNION ALL
SELECT 4,'1005','2014-03-01' FROM dual ),
b AS(
SELECT 1 ID,'1003' usercode,'2014-03-05' createdate FROM dual UNION ALL
SELECT 2,'1002','2014-03-05' FROM dual UNION ALL
SELECT 3,'1004','2014-03-05' FROM dual UNION ALL
SELECT 4,'1005','2014-03-05' FROM dual UNION ALL
SELECT 5,'1005','2014-03-05' FROM dual )
SELECT DECODE(C.USERCODE, NULL, D.USERCODE, C.USERCODE),
DECODE(C.ANUM, NULL, 0, C.ANUM),
DECODE(D.BNUM, NULL, 0, D.BNUM)
FROM ((SELECT USERCODE, COUNT(1) ANUM FROM A GROUP BY USERCODE) C FULL JOIN
(SELECT USERCODE, COUNT(1) BNUM FROM B GROUP BY USERCODE) D ON
C.USERCODE = D.USERCODE)
ORDER BY DECODE(C.USERCODE, NULL, D.USERCODE, C.USERCODE);
不好意思,才代码少复制一行。。
select 1 id, '1001' usercode ,'2014-3-5' updatecode from dual
union all
select 2 id, '1002' usercode ,'2014-3-5' updatecode from dual
union all
select 3 id, '1001' usercode ,'2014-3-5' updatecode from dual
union all
select 4 id, '1005' usercode ,'2014-3-1' updatecode from dual
),b as(
select 1 id, '1003' usercode ,'2014-3-5' createdate from dual
union all
select 2 id, '1002' usercode ,'2014-3-5' createdate from dual
union all
select 3 id, '1004' usercode ,'2014-3-5' createdate from dual
union all
select 4 id, '1005' usercode ,'2014-3-5' createdate from dual
union all
select 5 id, '1005' usercode ,'2014-3-5' createdate from dual
)
select t.usercode,
(select count(1)
from a
where a.usercode = t.usercode
and updatecode = '2014-3-5') countA,
(select count(1)
from b
where b.usercode = t.usercode
and createdate = '2014-3-5') countB
from (select usercode from a where updatecode = '2014-3-5'
union
select usercode from b where createdate = '2014-3-5') t
from
(
select usercode,count(*) a_num
from 表a
where createdate =to_char(sysdate,'yyy-mm-dd')
) tab_a,
(
select usercode,count(*) b_num
from 表b
where createdate =to_char(sysdate,'yyy-mm-dd')
) tab_b,
where
tab_a.usercode = tab_b.usercode