与SQL相关的查询以获取计数记录

本文介绍了与SQL相关的查询以获取计数记录的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一张反馈表，我在其中收到客户的反馈.他的桌子看起来像这样

I have a table for feedback in which i am getting feed backs from customers. he table looks like this

ID Service Food Behavior Environment OverallExprience Flight Date 1 Excellent VeryGood Good Bad Poor EK0603 2018-03-29 2 Excellent Good VeryGood Excellent Poor EK0603 2018-03-29

我想从员工那里搜索日期(从--->到)，并获得简短的报告，其中包括

All I want from employee is to search the date (from ---> to) and get the brief report which includes

• 优秀人数
• 很好的数量
• 好数量
• 不良数量
• 差

对于所有给定的选项. 就像在给定的数据中一样，如果Employe选择了 2018-03-29 到 2018-03-29

for all the given options. Like in the given Data if Employe selected the date range of 2018-03-29 to 2018-03-29

他可以看到这样的记录

Excellent Very Good Good Bad poor Service 2 0 0 0 0 Food 0 1 1 0 0 Behavior 0 1 1 0 0 Environment 1 0 0 1 0

卡在这里需要帮助.如果答案是在LINQ查询中，将是有意义的

Stuck here need help . will be appreciable if the answer is in LINQ query

推荐答案

由于您要将列转置为行，因此我想您需要一些UNION. 对于每个类别"，您可能需要一个不同的查询，对于每个类别，您应该计算每个选项获得多少票.

Because you want to transpose columns to rows, I guess you need a few UNIONs. You would probably need a different query for each "category", and for each category you should count how many votes do they get for each option.

请尝试执行此查询，它应该这样做:

Please try this query out, it should do it:

SELECT MAX('Service') AS Category, COUNT(CASE WHEN t.Servive = 'Excellent' THEN 1 END) AS Excellent, COUNT(CASE WHEN t.Servive = 'VeryGood' THEN 1 END) AS Very_Good, COUNT(CASE WHEN t.Servive = 'Good' THEN 1 END) AS Good, COUNT(CASE WHEN t.Servive = 'Bad' THEN 1 END) AS Bad, COUNT(CASE WHEN t.Servive = 'Poor' THEN 1 END) AS Poor FROM table1 t WHERE t.Date BETWEEN '2018-03-29' AND '2018-03-29' UNION SELECT MAX('Food') AS Category, COUNT(CASE WHEN t.Food = 'Excellent' THEN 1 END) AS Excellent, COUNT(CASE WHEN t.Food = 'VeryGood' THEN 1 END) AS Very_Good, COUNT(CASE WHEN t.Food = 'Good' THEN 1 END) AS Good, COUNT(CASE WHEN t.Food = 'Bad' THEN 1 END) AS Bad, COUNT(CASE WHEN t.Food = 'Poor' THEN 1 END) AS Poor FROM table1 t WHERE t.Date BETWEEN '2018-03-29' AND '2018-03-29' UNION SELECT MAX('Behavior') AS Category, COUNT(CASE WHEN t.Behavior = 'Excellent' THEN 1 END) AS Excellent, COUNT(CASE WHEN t.Behavior = 'VeryGood' THEN 1 END) AS Very_Good, COUNT(CASE WHEN t.Behavior = 'Good' THEN 1 END) AS Good, COUNT(CASE WHEN t.Behavior = 'Bad' THEN 1 END) AS Bad, COUNT(CASE WHEN t.Behavior = 'Poor' THEN 1 END) AS Poor FROM table1 t WHERE t.Date BETWEEN '2018-03-29' AND '2018-03-29' UNION SELECT MAX('Environment') AS Category, COUNT(CASE WHEN t.Environment = 'Excellent' THEN 1 END) AS Excellent, COUNT(CASE WHEN t.Environment = 'VeryGood' THEN 1 END) AS Very_Good, COUNT(CASE WHEN t.Environment = 'Good' THEN 1 END) AS Good, COUNT(CASE WHEN t.Environment = 'Bad' THEN 1 END) AS Bad, COUNT(CASE WHEN t.Environment = 'Poor' THEN 1 END) AS Poor FROM table1 t WHERE t.Date BETWEEN '2018-03-29' AND '2018-03-29'