两个表比较的SQL语句怎么写？谢谢。。
两个表比较的SQL语句怎么写？谢谢。。
本周时间是2011-12-7到2011-12-12,本周的时间可变的，从本周清单取。
有两个表。1.本周故障清单，2.前期故障清单（数据量很大），比较得出重复故障数和清单。
重复故障是指：本周发生的故障与以前发生的故障的故障名称相同的故障，如有重复，那本周和以前都要统计。
如果非本周有重复的故障，本周没有发生，不要统计。
重复故障数：包括本周和以前一起重复的总数。
1.前期的故障清单
故障名称发生时间修复时间
aa2011-12-05 11:262011-12-10 09:30
bb2011-12-05 11:552011-12-05 15:31
cc2011-12-05 11:552011-12-05 14:30
dd2011-12-05 12:352011-12-05 17:40
ee2011-12-05 16:392011-12-05 18:10
ff2011-12-05 17:232011-12-05 18:05
mm2011-12-05 19:372011-12-07 23:26
gg2011-12-06 10:452011-12-06 14:34
hh2011-12-06 11:002011-12-06 14:30
ii2011-12-06 12:152011-12-06 16:47
jj2011-12-06 16:042011-12-06 16:33
kk2011-12-06 16:192011-12-07 16:42
ll2011-12-06 17:002011-12-07 15:06
mm2011-12-06 17:372011-12-07 12:26
nn2011-12-06 19:282011-12-06 20:37
nn2011-12-06 22:282011-12-06 23:37
-----------------------------------------------
2.本周故障清单
故障名称发生时间修复时间
oo2011-12-07 00:142011-12-07 00:21
pp2011-12-07 09:032011-12-07 12:06
qq2011-12-07 09:572011-12-07 10:21
aa2011-12-07 10:292011-12-09 11:13
xx2011-12-07 11:312011-12-07 14:37
cc2011-12-07 15:152011-12-07 17:08
ff2011-12-07 18:322011-12-07 20:29
aa22011-12-08 10:562011-12-09 08:45
aa32011-12-08 11:412011-12-08 13:00
aa42011-12-08 20:452011-12-09 10:00
bb2011-12-08 20:562011-12-08 21:10
aa2011-12-10 11:102011-12-10 16:33
bb2011-12-10 11:372011-12-10 11:48
aa2011-12-11 09:552011-12-11 14:45
hh2011-12-11 13:242011-12-11 16:30
----------------------------------------------------
结果：
故障名称重复次数
aa4
bb3
cc2
ff2
hh2
重复清单：
故障名称发生时间修复时间
aa2011-12-05 11:262011-12-10 09:30
aa2011-12-07 10:292011-12-09 11:13
aa2011-12-10 11:102011-12-10 16:33
aa2011-12-11 09:552011-12-11 14:45
bb2011-12-05 11:552011-12-05 15:31
bb2011-12-08 20:562011-12-08 21:10
bb2011-12-10 11:372011-12-10 11:48
cc2011-12-05 11:552011-12-05 14:30
cc2011-12-07 15:152011-12-07 17:08
ff2011-12-05 17:232011-12-05 18:05
ff2011-12-07 18:322011-12-07 20:29
hh2011-12-06 11:002011-12-06 14:30
hh2011-12-11 13:242011-12-11 16:30
[解决办法]
在两表 故障名称 上建立索引
select a.故障名称,count(*) as js from 本周故障清单 a inner join 前期的故障清单 b
on a.故障名称=b.故障名称 having count(*)>=2 into curs dd
select * from (select * from 前期的故障清单 union all select * from 本周故障清单) a
inner join dd b on a.故障名称=b.故障名称
or
select * from (select * from 前期的故障清单 union all select * from 本周故障清单) a1
inner join
(select a.故障名称,count(*) as js from 本周故障清单 a inner join 前期的故障清单 b
on a.故障名称=b.故障名称 having count(*)>=2) b1
on a1.故障名称=b1.故障名称
[解决办法]
select a.故障名称,a.CNT_CW+count(b.故障名称)
from
(
select 故障名称,count(*) as CNT_CW
from 本周故障清单
group by 故障名称
) a left join (
select 故障名称
from 前期的故障清单
group by 故障名称
) b
group by a.故障名称,a.CNT_CW
having a.CNT_CW+count(b.故障名称)>1
