求SQL话语两个表，求差，有重复数据

2013-07-01

求SQL语句两个表，求差，有重复数据A表B110011001100110011001100110022001100 2200220

求SQL语句两个表，求差，有重复数据
A表==========         B==========
1            100         1               100
1            100         1               100
1            100         1               100
1            100         2               200
1            100
2            200
2            200
3            300
...
  查询出来结果
1            100
1            100
2            200
3            300
..
[解决办法]

with A as
(
select 1 a,100 b from dual union all
select 1,100 from dual union all
select 1,100 from dual union all
select 1,100 from dual union all
select 1,100 from dual union all
select 2,200 from dual union all
select 2,200 from dual union all
select 3,300 from dual
),
B as
(
select 1 a,100 b from dual union all
select 1,100 from dual union all

select 1,100 from dual union all
select 2,200 from dual
)
select a,b from (
select a,b,row_number()over(partition by a order by b)  from A
minus
select a,b,row_number()over(partition by a order by b)  from b
)
         A          B
---------- ----------
         1        100
         1        100
         2        200
         3        300

[解决办法]

引用:

SQL code?1234567891011121314151617181920212223242526272829with A as (select 1 a,100 b from dual union all select 1,100 from dual union all select 1,100 from dual union all select 1,100 fr……

支持，先加个序列号，再用minus相减，
[解决办法]
select distinct id,number from table_a
minus
select distinct id,number from table_b;
------
[解决办法]
select a,b,row_number()over(partition by a order by b) from A
minus
select a,b,row_number()over(partition by a order by b) from b
[解决办法]

引用:

select a,b,row_number()over(partition by a order by b) from A
minus
select a,b,row_number()over(partition by a order by b) from b

你这个不符合要求吧。
我觉得这样就可以：
select a,b from A
minus
select a,b from b
如果非要用分析函数可以使用以下：
select a,max(b)over(partition by a order by b desc)from a
minus
select a,max(b)over(partition by a order by b desc)from b
------解决方案--------------------

引用:

Quote: 引用:

select a,b,row_number()over(partition by a order by b)  from A
minus
select a,b,row_number()over(partition by a order by b)  from b

你这个不符合要求吧。
我觉得这样就可以：
select a,b  from A
minus
select a,b  from b
如果非要用分析函数可以使用以下：
select a,max(b)over(partition by a order by b desc)from a
minus
select a,max(b)over(partition by a order by b desc)from b

检验答案是否正确最好的办法就是在机器上运行一下

热点排行

oracle

求SQL话语两个表，求差，有重复数据