SELECTnid=row_number()OVER(ORDERBYid),dbo.订单.id,dbo.订单.地址,dbo.订单.客户,dbo.区站.区站,dbo区站.关键字
FROMdbo.订单LEFTOUTERJOIN
dbo.区站ONdbo.订单.地址LIKE'%'+dbo.区站.关键字+'%'
SELECTnid,id,地址,客户,区站,关键字
FROMdbo.result
WHERE(nidNOTIN
(SELECTMAX(nid)FROMdbo.resultASresult_1
GROUPBYid
HAVING(COUNT(nid)>1)))
以上是sqlserver2005去掉重复行
SELECTnid=IDENTITY(int,1,1),dbo.订单.id,dbo.订单.地址,dbo.订单.客户,dbo.区站.区站,dbo.区站.关键字
INTOt
FROMdbo.订单LEFTOUTERJOIN
dbo.区站ONdbo.订单.地址LIKE'%'+dbo.区站.关键字+'%'
SELECTnid,id,地址,客户,区站,关键字
FROMdbo.t
WHERE(nidNOTIN
(SELECTMAX(nid)ASExpr1
FROMdbo.tASt_1
GROUPBYid
HAVING(COUNT(*)>1)))
以上是sqlserver2000去掉重复行
附2005中的另外一种方法:
select * from t1 left join
(
select *
from
(
select a.*,row_number() over(partition by b3 order by b1) r
from t2 a
)
where r = 1
) t2
on a1=b3