基于输入获取MATCH_RULES表最高匹配分的SQL查询需求

Alright, let's figure out how to solve this problem of fetching the highest match score record from the MATCH_RULES table based on your specific Y/N criteria. Here's a step-by-step breakdown and working solutions:

Approach #

The core logic has two key parts:

1. Filter matching records: First, narrow down the MATCH_RULES table to only include rows where each field (FN, LN, EMAIL, TAX, DOB, MOBILE) exactly matches your input Y/N values.
2. Fetch highest score: From those filtered records, select the one with the maximum match_score. If multiple records tie for the highest score, you can choose to return just one or all of them (we'll cover both scenarios).

Solution 1: Using Subquery for Maximum Score #

This is a straightforward approach that first finds the highest match score among your filtered records, then retrieves the full record(s) with that score.

For example, if your input criteria are FN=Y&LN=Y&EMAIL=Y&TAX=N&DOB=N&MOBILE=Y, the SQL would look like this:

SELECT *
FROM MATCH_RULES
WHERE FN = 'Y' 
  AND LN = 'Y' 
  AND EMAIL = 'Y' 
  AND TAX = 'N' 
  AND DOB = 'N' 
  AND MOBILE = 'Y'
AND match_score = (
    -- Subquery to get the highest match score for the same criteria
    SELECT MAX(match_score)
    FROM MATCH_RULES
    WHERE FN = 'Y' 
      AND LN = 'Y' 
      AND EMAIL = 'Y' 
      AND TAX = 'N' 
      AND DOB = 'N' 
      AND MOBILE = 'Y'
);

• If you run this with the all-Y criteria (FN=Y&LN=Y&EMAIL=Y&TAX=Y&DOB=Y&MOBILE=Y), it will return the record with id=1 and match_score=100.
• For the partial-Y criteria example, it returns id=5 and match_score=40.

Solution 2: Using Window Functions (Flexible for Ties) #

If you want more control over how ties are handled (e.g., return all top-scoring records instead of just one), window functions like RANK() or ROW_NUMBER() are ideal.

Option A: Return one top-scoring record (even if there are ties) #

WITH ranked_rules AS (
    SELECT *,
           -- Assign a rank where 1 = highest match score
           ROW_NUMBER() OVER (ORDER BY match_score DESC) AS rank_num
    FROM MATCH_RULES
    WHERE FN = 'Y' 
      AND LN = 'Y' 
      AND EMAIL = 'Y' 
      AND TAX = 'N' 
      AND DOB = 'N' 
      AND MOBILE = 'Y'
)
SELECT id, FN, LN, EMAIL, TAX, DOB, MOBILE, match_score
FROM ranked_rules
WHERE rank_num = 1;

Option B: Return all top-scoring records (if multiple have the same max score) #

Just replace ROW_NUMBER() with RANK():

WITH ranked_rules AS (
    SELECT *,
           -- All top-scoring records get rank 1
           RANK() OVER (ORDER BY match_score DESC) AS rank_num
    FROM MATCH_RULES
    WHERE FN = 'Y' 
      AND LN = 'Y' 
      AND EMAIL = 'Y' 
      AND TAX = 'N' 
      AND DOB = 'N' 
      AND MOBILE = 'Y'
)
SELECT id, FN, LN, EMAIL, TAX, DOB, MOBILE, match_score
FROM ranked_rules
WHERE rank_num = 1;

Quick Tip #

To adapt these queries to different input criteria, simply replace the 'Y'/'N' values in the WHERE clause with your actual input values.

内容的提问来源于stack exchange，提问作者Developer