基于员工体重表计算体重差值及磅值转换的SQL需求

Alright, let's work through this problem to get exactly the output you need. Here's a straightforward SQL solution that hits all your requirements:

SELECT
    EmployeeID,
    MIN(Weight) AS Min,
    MAX(Weight) AS Max,
    (MAX(Weight) - MIN(Weight)) AS WeightLost,
    (MAX(Weight) - MIN(Weight)) * 2.2 AS WeightLostLBS
FROM
    YourEmployeeWeightTable -- Replace this with your actual table name
GROUP BY
    EmployeeID;

Breakdown of the Query: #

• GROUP BY EmployeeID: Ensures we calculate all metrics per individual employee, not across the entire dataset.
• MIN(Weight) AS Min & MAX(Weight) AS Max: Pulls the smallest and largest recorded weight values for each employee, matching your required output fields.
• (MAX(Weight) - MIN(Weight)) AS WeightLost: Calculates the total weight difference (I used max minus min here to match your sample output of 20 for EmployeeID 1. If you intended to calculate MIN(Weight) - MAX(Weight) for a negative value, just swap the order of the two functions!)
• WeightLostLBS: Converts the weight difference from kilograms to pounds using the conversion factor 2.2, as requested.

When you run this with your sample data (EmployeeID 1 with weights 100 and 120), you'll get the exact output you expect:

EmployeeID: 1, Min: 100, Max: 120, WeightLost: 20, WeightLostLBS: 44

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