try: --------------------------------------------------------------------------- select a.id, case when b.id is null then '/**/' else key1 end key1, case when b.id is null then '/**/' else key2 end key2 from OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:\aa.xls', [Sheet1$]) a left join (select min(c.id) as id from OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:\aa.xls', [Sheet1$]) c group by c.key1,c.key2 having count(*)=1) b on a.id = b.id
select a.id, key1=(case when b.key1 is null then a.key1 else '/**/' end), key2=(case when b.key2 is null then a.key2 else '/**/' end) from OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:\aa.xls', [Sheet1$]) a left join ( select c.key1,c.key2 from OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:\aa.xls', [Sheet1$]) c where c.key1 is not null and c.key2 is not null group by c.key1,c.key2 having count(*)>1 ) b on a.key1 = b.key1 and a.key2=b.key2谢谢 子陌红尘 的思路,我用了这个,应该快点了。
回复人: daijingjie2002(艰苦创业) ( ) 信誉:97 2005-01-28 15:38:00 得分: 0
exists 效率没有 in,=等操作快
说的完全错误,exists的效率比 in要快
---------------------------------------------------------------------------
select
a.id,
case when b.id is null then '/**/' else key1 end key1,
case when b.id is null then '/**/' else key2 end key2
from
OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:\aa.xls', [Sheet1$]) a
left join
(select min(c.id) as id
from
OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:\aa.xls', [Sheet1$]) c
group by
c.key1,c.key2
having count(*)=1) b
on
a.id = b.id
a.id,
key1=(case when b.key1 is null then a.key1 else '/**/' end),
key2=(case when b.key2 is null then a.key2 else '/**/' end)
from
OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:\aa.xls', [Sheet1$]) a
left join
(
select c.key1,c.key2
from
OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:\aa.xls', [Sheet1$]) c
where
c.key1 is not null and c.key2 is not null
group by
c.key1,c.key2
having count(*)>1
) b
on
a.key1 = b.key1 and a.key2=b.key2谢谢 子陌红尘 的思路,我用了这个,应该快点了。