基于SQL的客户反馈表按日期区间统计各维度评价数量需求

Got it, let's break down how to solve this. You need to create a summary report that counts every occurrence of each rating (Excellent, VeryGood, Good, Bad, Poor) across all your feedback dimensions—Service, Food, Behavior, Environment, and OverallExprience—filtered by a specific date range. Below are two practical approaches, depending on the report format you want.

Option 1: Horizontal Summary (Conditional Aggregation) #

This gives you a compact table where each row represents a feedback dimension, and columns show the count of each rating. Perfect for a quick, at-a-glance view.

SELECT
  'Service' AS Dimension,
  COUNT(CASE WHEN Service = 'Excellent' THEN 1 END) AS Excellent_Count,
  COUNT(CASE WHEN Service = 'VeryGood' THEN 1 END) AS VeryGood_Count,
  COUNT(CASE WHEN Service = 'Good' THEN 1 END) AS Good_Count,
  COUNT(CASE WHEN Service = 'Bad' THEN 1 END) AS Bad_Count,
  COUNT(CASE WHEN Service = 'Poor' THEN 1 END) AS Poor_Count
FROM feedback
WHERE Date BETWEEN @from_date AND @to_date -- Replace with your actual date parameters

UNION ALL

SELECT
  'Food' AS Dimension,
  COUNT(CASE WHEN Food = 'Excellent' THEN 1 END) AS Excellent_Count,
  COUNT(CASE WHEN Food = 'VeryGood' THEN 1 END) AS VeryGood_Count,
  COUNT(CASE WHEN Food = 'Good' THEN 1 END) AS Good_Count,
  COUNT(CASE WHEN Food = 'Bad' THEN 1 END) AS Bad_Count,
  COUNT(CASE WHEN Food = 'Poor' THEN 1 END) AS Poor_Count
FROM feedback
WHERE Date BETWEEN @from_date AND @to_date

UNION ALL

SELECT
  'Behavior' AS Dimension,
  COUNT(CASE WHEN Behavior = 'Excellent' THEN 1 END) AS Excellent_Count,
  COUNT(CASE WHEN Behavior = 'VeryGood' THEN 1 END) AS VeryGood_Count,
  COUNT(CASE WHEN Behavior = 'Good' THEN 1 END) AS Good_Count,
  COUNT(CASE WHEN Behavior = 'Bad' THEN 1 END) AS Bad_Count,
  COUNT(CASE WHEN Behavior = 'Poor' THEN 1 END) AS Poor_Count
FROM feedback
WHERE Date BETWEEN @from_date AND @to_date

UNION ALL

SELECT
  'Environment' AS Dimension,
  COUNT(CASE WHEN Environment = 'Excellent' THEN 1 END) AS Excellent_Count,
  COUNT(CASE WHEN Environment = 'VeryGood' THEN 1 END) AS VeryGood_Count,
  COUNT(CASE WHEN Environment = 'Good' THEN 1 END) AS Good_Count,
  COUNT(CASE WHEN Environment = 'Bad' THEN 1 END) AS Bad_Count,
  COUNT(CASE WHEN Environment = 'Poor' THEN 1 END) AS Poor_Count
FROM feedback
WHERE Date BETWEEN @from_date AND @to_date

UNION ALL

SELECT
  'OverallExprience' AS Dimension,
  COUNT(CASE WHEN OverallExprience = 'Excellent' THEN 1 END) AS Excellent_Count,
  COUNT(CASE WHEN OverallExprience = 'VeryGood' THEN 1 END) AS VeryGood_Count,
  COUNT(CASE WHEN OverallExprience = 'Good' THEN 1 END) AS Good_Count,
  COUNT(CASE WHEN OverallExprience = 'Bad' THEN 1 END) AS Bad_Count,
  COUNT(CASE WHEN OverallExprience = 'Poor' THEN 1 END) AS Poor_Count
FROM feedback
WHERE Date BETWEEN @from_date AND @to_date;

Option 2: Vertical Summary (Unpivot + Group By) #

If you want a more flexible, row-based format (great for further filtering or visualization), first "unpivot" your dimension columns into rows, then group and count.

For SQL Server/Oracle:

WITH unpivoted_feedback AS (
  SELECT
    Date,
    Dimension,
    Rating
  FROM feedback
  UNPIVOT (
    Rating FOR Dimension IN (Service, Food, Behavior, Environment, OverallExprience)
  ) AS unpvt
)
SELECT
  Dimension,
  Rating,
  COUNT(*) AS Rating_Count
FROM unpivoted_feedback
WHERE Date BETWEEN @from_date AND @to_date
GROUP BY Dimension, Rating
ORDER BY Dimension, 
         CASE Rating 
           WHEN 'Excellent' THEN 1 
           WHEN 'VeryGood' THEN 2 
           WHEN 'Good' THEN 3 
           WHEN 'Bad' THEN 4 
           WHEN 'Poor' THEN 5 
         END;

For PostgreSQL/MySQL (no built-in UNPIVOT):

Use UNION ALL to manually unpivot the columns:

WITH unpivoted_feedback AS (
  SELECT Date, 'Service' AS Dimension, Service AS Rating FROM feedback
  UNION ALL
  SELECT Date, 'Food' AS Dimension, Food AS Rating FROM feedback
  UNION ALL
  SELECT Date, 'Behavior' AS Dimension, Behavior AS Rating FROM feedback
  UNION ALL
  SELECT Date, 'Environment' AS Dimension, Environment AS Rating FROM feedback
  UNION ALL
  SELECT Date, 'OverallExprience' AS Dimension, OverallExprience AS Rating FROM feedback
)
SELECT
  Dimension,
  Rating,
  COUNT(*) AS Rating_Count
FROM unpivoted_feedback
WHERE Date BETWEEN @from_date AND @to_date
GROUP BY Dimension, Rating
ORDER BY Dimension, 
         CASE Rating 
           WHEN 'Excellent' THEN 1 
           WHEN 'VeryGood' THEN 2 
           WHEN 'Good' THEN 3 
           WHEN 'Bad' THEN 4 
           WHEN 'Poor' THEN 5 
         END;

Key Notes: #

• Replace @from_date and @to_date with your actual date range parameters (e.g., '2018-03-29' and '2018-03-29' for your sample data).
• The CASE statement in the ORDER BY clause ensures ratings are sorted from best to worst, making the report easier to read.
• Both queries will correctly process your sample data: for example, you'll see Service has 2 Excellent ratings, Food has 1 Excellent and 1 Good, etc.

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