数据不一致的SQL怎么写？

Question

某学校开办两个兴趣班：书法班、绘画班和音乐班等，所有同学可以登记、报名一个或多个兴趣班，登记信息保存在t_dj表中，报名信息保存在t_bm表中，两张表的字段都是学生编号和兴趣编号。现在要求查询出所有登记和报名不一致的情况，最好用一个SQL语句，该怎么写？
SQL>select * from t_dj;
xs_id    xq_id
----------------------
 1001     1
 1001     2
 1002     2
 1002     3
 1003     2
 1003     1
 1003     3SQL>select * from t_bm;
xs_id    xq_id
----------------------
 1001     3
 1001     2
 1002     3
 1002     2
 1003     1
 1003     2
 1004     3对于上述数据要求能够查出一下结果
xs_id    t_dj.xq_id      t_bm.xq_id
-------------------------------------
 1001        1               3
 1003        3
 1004                        4

Answer 1

select * from 
(
select a.xs_id,a.xq_id
  from t_dj a
  left outer join t_bm b on a.xs_id = b.xs_id
                        and a.xq_id = b.xq_id
 where b.xq_id is null
)U full outer join (
 select a.xs_id,a.xq_id
  from t_bm a
  left outer join t_dj b on a.xs_id = b.xs_id
                        and a.xq_id = b.xq_id
 where b.xq_id is null
 )D on U.xs_id=D.xs_id有没有简单点的？

Answer 2

create table T_BM
(
  XS_ID VARCHAR2(4),
  XQ_ID CHAR(1)
)
;create table T_DJ
(
  XS_ID VARCHAR2(4),
  XQ_ID CHAR(1)
)
;insert into T_BM (XS_ID, XQ_ID)
values ('1003', '2');
insert into T_BM (XS_ID, XQ_ID)
values ('1003', '1');
insert into T_BM (XS_ID, XQ_ID)
values ('1002', '2');
insert into T_BM (XS_ID, XQ_ID)
values ('1002', '3');
insert into T_BM (XS_ID, XQ_ID)
values ('1001', '3');
insert into T_BM (XS_ID, XQ_ID)
values ('1001', '2');
insert into T_BM (XS_ID, XQ_ID)
values ('1004', '3');
commit;insert into T_DJ (XS_ID, XQ_ID)
values ('1003', '1');
insert into T_DJ (XS_ID, XQ_ID)
values ('1003', '2');
insert into T_DJ (XS_ID, XQ_ID)
values ('1002', '3');
insert into T_DJ (XS_ID, XQ_ID)
values ('1002', '2');
insert into T_DJ (XS_ID, XQ_ID)
values ('1001', '2');
insert into T_DJ (XS_ID, XQ_ID)
values ('1001', '1');
insert into T_DJ (XS_ID, XQ_ID)
values ('1003', '3');
commit;

Answer 3

select xs_id,
       sum(dj_id) as dj_id,
       sum(bm_id) as bm_id
from
(
 select xs_id,
        xq_id as dj_id,
        0 as bm_id
 from 
   (select * from t_dj
    minus
    select * from t_bm) t
 union
 select xs_id,
        0 as dj_id,
        bm_id as bm_id
 from 
   (select * from t_bm
    minus
    select * from t_dj)
)
group by xs_id只能处理小于三个不一致的情况...
完美版等高手吧.

Answer 4

首先谢谢鱼！
select xs_id, sum(dj_id) as dj_id, sum(bm_id) as bm_id
  from (select xs_id, xq_id as dj_id, '0' as bm_id
          from (select *
                  from t_dj
                minus
                select * from t_bm) t
        union
        select xs_id, '0' as dj_id, xq_id as bm_id
          from (select *
                  from t_bm
                minus
                select * from t_dj))
 group by xs_id
得到如下结果：
XS_ID      DJ_ID      BM_ID
----- ---------- ----------
1001           1          3
1003           3          0
1004           0          3因为兴趣编号不能求和，所以直接使用下面的SQL
select xs_id, xq_id as dj_id, '0' as bm_id
  from (select *
          from t_dj
        minus
        select * from t_bm) t
union
select xs_id, '0' as dj_id, xq_id as bm_id
  from (select *
          from t_bm
        minus
        select * from t_dj)
可以得到如下结果，这样也就不存在个数限制了吧
XS_ID DJ_ID BM_ID
----- ----- -----
1001  0     3
1001  1     0
1003  3     0
1004  0     3但是这样不能满足客户的要求。
另：minus 和关联的效率哪个更好一点？

Answer 5

to Croatia：
可以多行显示，也可以单行显示，比如下面的格式都可以
XS_ID DJ_ID BM_ID
----- ----- ----- ----- -----
1005  1     3
1005  2     6
1005  5     
1005  4   
或
XS_ID DJ_ID BM_ID
----- ----- ----- ----- -----
1005  1     6
1005  2     3
1005  5     
1005  4 
或
XS_ID   DJ_ID     BM_ID
----- ----- ----- ----- -----
1005  1,2,5,4     3,6

Answer 6

假如LZ的数据，可以保证在0..1:0..1的情况下，LZ自己的语句也可以了。假如数据有N:0..1的情况下，我写了这个。select t1.xs_id,t1.xq_id,t1.rnum,t2.xs_id,t2.xq_id,t2.rnum from 
(
    select a.xs_id,a.xq_id,row_number() over(partition by a.xs_id order by a.xq_id)  rnum
    from   t_dj   a 
    where not exists (select 1 from t_bm b where a.xs_id   =   b.xs_id and   a.xq_id   =  b.xq_id )
) t1
full outer join 
(
    select a.xs_id,a.xq_id ,row_number() over(partition by a.xs_id order by a.xq_id)  rnum
    from   t_bm   a 
    where not exists (select 1 from t_dj b where a.xs_id   =   b.xs_id and   a.xq_id   =  b.xq_id )
) t2
on t1.xs_id = t2.xs_id and t1.rnum = t2.rnum 
order by  t1.xs_id,t1.xq_id,t2.xs_id,t2.xq_id就是用一个rownum来建立连接。我的句子，主要用了not exist来做，不用minus的原因，在于，避免minus默认的sort操作，省掉系统的一些Cost.在我的10.1.0.2的数据库上面测试，发现结果有问题。部分数据重复显示了。感觉像是BUG。于是修改为如下：
select t1.xs_id,t1.xq_id,t1.rnum,t2.xs_id,t2.xq_id,t2.rnum from 
(
    select a.xs_id,a.xq_id ,(select count(c.xq_id ) from t_dj c where c.xs_id   =   a.xs_id and c.xq_id > a.xq_id)  rnum
    from   t_dj   a 
    where not exists (select 1 from t_bm b where a.xs_id   =   b.xs_id and   a.xq_id   =  b.xq_id )
) t1
full outer join 
(
    select a.xs_id,a.xq_id ,(select count(c.xq_id ) from t_bm c where c.xs_id   =   a.xs_id and c.xq_id > a.xq_id )  rnum
    from   t_bm   a 
    where not exists (select 1 from t_dj b where a.xs_id   =   b.xs_id and   a.xq_id   =  b.xq_id )
) t2
on t1.xs_id = t2.xs_id and t1.rnum = t2.rnum 

Answer 7

with a as(
  select xs_id from t_dj
  union
  select xs_id from t_bm
)
select a.xs_id, b.sq_id, c.xq_id
  from a,t_dj b,t_bm c
 where a.xs_id = b.xs_id(+)
   and a.xs_id = c.xs_id(+);

Answer 8

SELECT   NVL (j1.xs_id, j2.xs_id) AS xs_id, j2.t_dj_xq_id, j1.t_bm_xq_id
    FROM (SELECT t1.xs_id, t1.xq_id AS t_bm_xq_id, t2.xq_id AS t_dj_xq_id
            FROM t_bm t1 LEFT OUTER JOIN t_dj t2
                 ON t1.xs_id = t2.xs_id AND t1.xq_id = t2.xq_id
           WHERE t2.xq_id IS NULL) j1
         FULL OUTER JOIN
         (SELECT t1.xs_id, t1.xq_id AS t_dj_xq_id, t2.xq_id AS t_bm_xq_id
            FROM t_dj t1 LEFT OUTER JOIN t_bm t2
                 ON t1.xs_id = t2.xs_id AND t1.xq_id = t2.xq_id
           WHERE t2.xq_id IS NULL) j2 ON j1.xs_id = j2.xs_id
ORDER BY NVL (j1.xs_id, j2.xs_id)这样子我试过可以的，楼主试试看。