I have a data table as follows
FName SName DOB PCode Addr1
==== ==== ====== ===== ==开发者_运维技巧=====
Bob Test 231280 S66TH 1 Test Lane
Bobby Test 240977 S66TH 2 Test Drive
Robert Test 240977 xxxxx xxxxxx
Tim Test 140977 S78YH 5 Hill Road
I am trying to write a function / SP that will accept Paramters FNAme, SName, Dob, PCode and Addr1. I want to be able to match records where ANY 3 out of the 5 parameters are equal. I'm not sure how to do this WITHOUT writing a long series of AND OR statements? I would quite like the solution to be scalable too so if in future another column is added then 3 out of 6 must match (or even 4 out of 6). I dont mind the query being represented by Linq either.
Essentially Im looking for a matching algorithm That can match x out of y pieces of data.
Convert true comparisons to 1, false comparisons to 0, add them all together, and test if the sum is at least 3. Something like
where (case FName when @FName then 1 else 0 end)
+ (case SName when @Sname then 1 else 0 end)
+ (case DOB when @DOB then 1 else 0 end)
+ (case PCode when @PCode then 1 else 0 end)
+ (case Addr1 when @Addr1 then 1 else 0 end)
>= 3
精彩评论