|
我有一个带有rowID,经度,纬度,businessName,url,标题的表。可能看起来像:/ Z2 k0 B, s# b3 L+ a& z
rowID | long | lat | businessName | url | caption
! a/ i" f. R5 _$ O 1 20 -20 Pizza Hut yum.com null' I# o/ H0 i. c9 r8 U
如何删除所有重复项,但仅保留一个具有URL的副本(第一优先级),或者如果另一个不具有URL的标题(第二优先级),则保留具有标题的副本,然后删除其余重复项?8 V3 J3 z. D0 \* W
$ w* ]5 c; c/ r2 V- V
解决方案:
4 Y/ [* L4 R+ M5 o+ B# U
6 w: k5 D2 s% } z
5 J& G3 w% M9 @6 o* O# b p; ^2 t8 J! |% }" H" z: I7 w7 f6 y. A
这是我的循环技术。这可能会因为没有成为主流而被否决-我对此很满意。+ v4 ^3 V& g8 t0 [
DECLARE @LoopVar int% m# k# P7 {! ~' B1 @; P3 U
DECLARE5 L8 g5 v0 [7 `
@long int,
" C; p1 T6 e9 u8 [ @lat int,
" X4 N4 b4 j9 O M, e @businessname varchar(30),
& f+ u4 Q0 y& e1 d" e; N3 W @winner int9 V# @7 V ?$ C) J% k
SET @LoopVar = (SELECT MIN(rowID) FROM Locations)/ c) N: k0 v% f& m5 Q
WHILE @LoopVar is not null
3 Q% e# P3 {8 v# D9 VBEGIN
6 d# x& ^8 e9 E9 K8 e2 f6 G --initialize the variables.
& M- h; B7 S, H/ `5 [$ D3 D SELECT : y. _% A& q" E- q7 @* _
@long = null,
6 ?' s' l8 Q; }2 _: J. G- Q( V @lat = null,
* `3 K: x1 w' L @businessname = null,
$ n5 Y0 \3 L* G0 @0 o @winner = null9 J; Z4 A' }( ~ O
-- load data from the known good row. 8 o/ b- D! G& b8 t* j
SELECT
7 c. \. K4 x) M* f0 N$ w/ H& X+ I @long = long,2 k) L; u, y5 H. J
@lat = lat,
) s/ N0 |. h; [/ W; ^# m @businessname = businessname
: q7 k) S( x8 D5 W4 _ h+ l FROM Locations
9 c9 m( i$ |$ F$ V) M/ s" c3 | WHERE rowID = @LoopVar8 p* ?8 d F( C3 T
--find the winning row with that data; [* _, Q# n1 l/ x# {/ D- `9 I
SELECT top 1 @Winner = rowID
- X4 q1 J% `* K7 q FROM Locations
$ v& L' r5 w- J1 i/ D WHERE @long = long8 R- g* w! T2 `1 K! m" c
AND @lat = lat
, e" B! l; A7 ]! |* d, i( R; P1 S AND @businessname = businessname7 e- n5 b& N, [
ORDER BY
, p- m; A7 G2 [# @1 v6 p7 h" W CASE WHEN URL is not null THEN 1 ELSE 2 END,
: u4 a9 _' o5 F; p$ d+ T. f, ~ CASE WHEN Caption is not null THEN 1 ELSE 2 END,: S5 w( a K& S6 k& X
RowId
" ?' S/ T- H7 \! V! z --delete any losers.
' `$ u6 h/ y7 E2 p DELETE FROM Locations
7 a& J0 K/ |9 b0 N* C- y6 v WHERE @long = long1 j7 [" Z: ^3 O9 A) g
AND @lat = lat
]; S& c7 p* e- h. C8 D/ } AND @businessname = businessname- n* m3 M. T# v( k% x2 |% \4 B
AND @winner != rowID" |& a- G1 K9 ]5 S& E+ f; k/ ~
-- prep the next loop value.
- i/ U. v1 W. y* [$ r SET @LoopVar = (SELECT MIN(rowID) FROM Locations WHERE @LoopVar < rowID)# G0 k, L0 [. G# S$ X- C$ E
END |
|