如何编写SQL查询筛选九月销售额处于均值至均值+5%区间的数据？

Let's walk through how to solve this requirement step by step. We need to pull records where:

• The month is September (Sep)
• total_sales lies exactly between the given average (739632.18) and the average plus 5% (776613.78)

Basic Hardcoded Query #

If you want to use the fixed values provided, here's the straightforward SQL:

SELECT customer_id, customer_name, month_day, month, total_sales
FROM your_table_name  -- Don't forget to replace this with your actual table name
WHERE month = 'Sep'
  AND total_sales BETWEEN 739632.18 AND 776613.78;

Key Details:

• BETWEEN includes both the lower and upper bounds, which aligns perfectly with your requirement of values in that range.
• The WHERE clause first narrows down to September data, then filters the sales values.

Dynamic Version (For Updating Data) #

If your table data changes regularly and you don't want to hardcode the mean values, you can calculate them dynamically in the query:

SELECT customer_id, customer_name, month_day, month, total_sales
FROM your_table_name
WHERE month = 'Sep'
  AND total_sales BETWEEN 
    (SELECT AVG(total_sales) FROM your_table_name) 
    AND 
    (SELECT AVG(total_sales) * 1.05 FROM your_table_name);

This version automatically computes the average and average plus 5% every time you run the query, so it stays accurate even as new data is added.

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