求两条sql语句...

[伊凡yifan]

两个表结构一模一样如下：

A表 LoverA B表loverB

两表字段相同：id int
phone varchar
time datetime
is_valid bit

两个表的数据不同，如LOVERA 有100条记录 LOVERB有200条记录

用一条sql语句 根据phone 列出两个表不相同的记录集合

用一条sql语句 根据phone 统计两个表的不相同的记录集的总数

[dawugui]

--不知道你到底需要哪个?
select a.* from lovera a where phone not in (select phone from loverb)
select b.* from loverb b where phone not in (select phone from lovera)

select count(*) from lovera a where phone not in (select phone from loverb)
select count(*) from loverb b where phone not in (select phone from lovera)

select m.cnt + n.cnt from
(select cnt = count(*) from lovera a where phone not in (select phone from loverb)) m,
(select cnt = count(*) from loverb b where phone not in (select phone from lovera)) n

[pt1314917]

列出A表中不在B表中的记录
select * from LoverA  a where not exists(select 1 from LoverB where id=a.id and phone=a.phone
and time=a.time and is_valid=a.is_valid)

列出A表中不在B表中的记录的总数：
select count(1) from LoverA  a where not exists(select 1 from LoverB where id=a.id and phone=a.phone and time=a.time and is_valid=a.is_valid)

列出B表中不在A表中的记录：
select * from LoverB  a where not exists(select 1 from LoverA where id=a.id and phone=a.phone
and time=a.time and is_valid=a.is_valid)

列出B表中不在A表中的记录的总数：
select count(1) from LoverB  a where not exists(select 1 from LoverA where id=a.id and phone=a.phone and time=a.time and is_valid=a.is_valid)