两个表,A与B,A与B都包括一个字段,BDHM,A与B还各自有其它列,但BDHM内容一样,只是A表与B表行数不同。比如说A的某个BDHM有二行,而B中出现了四行。
现在有没有办法能用SQL语句找到这几行,同时如果不关注其它内容的,我只想把多的几行删除,使行数多的表与行数少的表一样?
现在有没有办法能用SQL语句找到这几行,同时如果不关注其它内容的,我只想把多的几行删除,使行数多的表与行数少的表一样?
调试欢乐多
(select BDHM,count(1) as a_count
from a)a ,
(select BDHM,count(1) as b_count
from b)b
where a.BDHM = b.BDHM and a.a_count <> b.b_count
(select BDHM,count(*) as a_count from A) TA
inner join
(select BDHM,count(*) as b_count from B) TB
on Ta.BDHM = Tb.BDHM
where Ta.a_count <> Tb.b_count然后再关联,删除
(
select a.BDHM,a_count,b_count from
(select BDHM,count(1) as a_count
from a)a ,
(select BDHM,count(1) as b_count
from b)b
where a.BDHM = b.BDHM and a.a_count > b.b_count
)C
????
另我写了
select a.bdhm,a_count,b_count from
(select 保单号码 as bdhm,count(*) as a_count
from [03hzqd] )a ,
(select BDHM,count(*) as b_count
from h_dlf03hz ) b
where a.BDHM = b.BDHM and a.a_count <> b.b_count报错服务器: 消息 8118,级别 16,状态 1,行 1
列 '03hzqd.保单号码' 在选择列表中无效,因为该列未包含在聚合函数中,并且没有 GROUP BY 子句。
服务器: 消息 8118,级别 16,状态 1,行 1
列 'h_dlf03hz.bdhm' 在选择列表中无效,因为该列未包含在聚合函数中,并且没有 GROUP BY 子句。都加了group by 却报列名bdhm无效,奇怪啊
(select BDHM,count(*) as a_count from A group by BDHM) TA
inner join
(select BDHM,count(*) as b_count from B group by BDHM) TB
on Ta.BDHM = Tb.BDHM
where Ta.a_count <> Tb.b_count
(select 保单号码 as bdhm,count(*) as a_count from [03hzqd] group by BDHM) TA
inner join
(select BDHM,count(*) as b_count from h_dlf03hz group by BDHM) TB
on Ta.BDHM = Tb.BDHM
where Ta.a_count <> Tb.b_count报 bdhm无效啊
(select 保单号码 as bdhm,count(*) as a_count from [03hzqd] group by 保单号码) TA
inner join
(select 保单号码,count(*) as b_count from h_dlf03hz group by 保单号码) TB
on Ta.保单号码 = Tb.保单号码
where Ta.a_count <> Tb.b_count
group by a.BDHM having count(*)>1