如何获取Member_Details最新记录关联BMI_Data，插入符合条件成员到results表

Alright, let's tackle this SQL problem step by step. Here's a robust solution that meets all your requirements:

First, let's outline the core tasks we need to complete:

• Grab the most recent record for each member_ID in the Member_Details table
• Compare each member's BMI against the corresponding target_BMI in BMI_Data
• Filter out members where their BMI is below the target
• Insert the qualifying member_ID, First_Name, and BMI values into the new results table

Step 1: Get the Latest Record Per Member #

We'll use a window function (ROW_NUMBER()) to rank records for each member, ordered by recency. I'm assuming your Member_Details table has a timestamp/date field like record_date to determine which entry is newest—if you use an auto-incrementing ID instead, just swap record_date DESC with id DESC:

WITH latest_member_records AS (
    SELECT 
        member_ID,
        First_Name,
        BMI,
        -- Assign a rank where 1 = latest record for the member
        ROW_NUMBER() OVER (PARTITION BY member_ID ORDER BY record_date DESC) AS record_rank
    FROM Member_Details
)

Step 2: Filter & Insert into the Results Table #

Next, we'll join this common table expression (CTE) with BMI_Data, apply our BMI filter, and insert the valid records into results:

INSERT INTO results (member_ID, First_Name, BMI)
SELECT 
    lmr.member_ID,
    lmr.First_Name,
    lmr.BMI
FROM latest_member_records lmr
-- Join with BMI_Data to get the target BMI for each member
JOIN BMI_Data bd ON lmr.member_ID = bd.member_ID
WHERE 
    lmr.record_rank = 1  -- Only keep the latest record per member
    AND lmr.BMI < bd.target_BMI;  -- Filter members with BMI below target

Key Notes for Adjustment: #

• Recency Identifier: If your Member_Details doesn't have a date/timestamp field, use an auto-incrementing column (like id) in the ORDER BY clause of the window function to ensure we pick the most recent entry.
• Join Key: Double-check that member_ID is the correct common key between Member_Details and BMI_Data—adjust the join condition if your schema uses a different identifier (e.g., user_id).
• Results Table Setup: If the results table doesn't exist yet, create it first with a schema that matches your source tables:

  CREATE TABLE results (
      member_ID INT,  -- Match the data type from Member_Details
      First_Name VARCHAR(100),  -- Adjust length as needed
      BMI DECIMAL(5,2)  -- Use appropriate precision for BMI values
  );
  

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