sql求写

[cunzaima123]

大家好.请问我现有表A,与B.其中A有字段m,n;B有字段j,k
如:
A:
m n
131 q
132 w
135 e
132 e
B:
j k
131 tt
132 mm

我现想查出,A中按n分组后.查出m不在B表j中存在的数值.及A表m总数值,请问SQL要怎样写?
结果模拟:select count(distinct m),(select count(distinct m) from a where a.m not in (select j from b )) from a group by n
上面是我想要结果的一个SQL结果模拟,麻烦高手帮忙指点.谢谢

[itcast123]

有知道的吗?

[lzf616]

你怎么写模拟结果的SQL出来？你把你需要的输出结果写出来就可以了~~

按你的想法，应该用分析函数很容易做到的~

你把你要的结果写出来吧，另外提供建表语句，测试数据等，这样别人才方便给你写SQL~~~

谢谢

[itcast123]

[Quote=引用 2 楼 lzf616 的回复:]
你怎么写模拟结果的SQL出来？你把你需要的输出结果写出来就可以了~~

按你的想法，应该用分析函数很容易做到的~

你把你要的结果写出来吧，另外提供建表语句，测试数据等，这样别人才方便给你写SQL~~~

谢谢
[/Quote]
A:
m n
131 q
132 w
135 e
132 e
B:
j k
131 tt
132 mm
查询出A中按n分组后.查出m不在B表j中存在的数值.及A表m总数值
结果:
q 1 0
w 1 0
e 2 1

[lzf616]

create table t1
(
m int,
n varchar2(20)

)
;
create table t2
(
 j int,
 k varchar2(10)

);

insert into t1 select 131,'q' from dual
union all
select 132,'w' from dual union all
select 135,'e' from dual union all
select 132,'e' from dual;
commit;

insert into t2 select 131,'tt' from dual
union all
select 132,'mm' from dual
commit;


--SQL语句
select k.n,k.cnt1, nvl(k1.cnt2,0) as cnt2
  from (select distinct n, count(*) over(partition by n order by n) as cnt1 from t1) k,
       (select distinct n, count(*) over(partition by n order by n) as cnt2
          from t1
         where t1.m not in (select j from t2)) k1
 where k.n = k1.n(+)

这个测试通过，可能有优化的方案吧，先这样，在上班呢

[cyousor]

Select Distinct n,
       Count(m) Over(Partition By n),
       (Count(m)
        Over(Partition By n) -
        (Select Count(m) Over(Partition By n)
           From a
          Where Not Exists (Select 1 From b Where b.j = a.m)))
  From a

试试

[itcast123]

[Quote=引用 5 楼 zhangcunhua 的回复:]
SQL code

Select Distinct n,
Count(m) Over(Partition By n),
(Count(m)
Over(Partition By n) -
(Select Count(m) Over(Partition By n)
From a
Whe……
[/Quote]
请问,这里Where Not Exists (Select 1 From b Where b.j = a.m)))是不是有点问题?