SQL按范围计算行和组中的非空单元格

Question

我在main table中有一些数据

| id | Attendance              | Accountability          | Respect                    |   |
|----|-------------------------|-------------------------|----------------------------|---|
| 1  | John was always on time | John is accountable     | John is always respectful  |   |
| 2  | Ann never missed a day  | Ann is very accountable |                            |   |
| 3  |                         |                         | Dan was very disrespectful |   |

我需要计算每一行的非空单元格的数量，然后对范围进行排序。

示例：

首先计算非空细胞。

| id | Non-empty |
|----|-----------|
| 1  | 3         | # Row 1 has 3 comments
| 2  | 2         | # Row 2 has 2 comments and 1 empty cell
| 3  | 1         | # Row 3 has 1 comment and 2 empty cells

让我们把这个结果称为“表A”

然后根据左注释的范围创建一个分组。这是最后的结果.

| id | Range        | Count |
|----|--------------|-------|
| 1  | 1-2 comments | 2     | # 2 rows have between 1 and 2 comments
| 2  | 3-4 comments | 1     | # 1 row has between 3 and 4 comments
| 3  | 5+ comments  | 0     | # 0 rows have more than 5 comments

让我们把这个结果称为“表B”

我试过什么

select 
  case 
    when Non-empty between 1 and 2 then '1-2 comments'
    when Non-empty between 3 and 4 then '3-4 comments'
    else then '5+ comments'
  end as `Range`,
 count(1) as `Count`
from `Table A`
group by `Range`

此查询应从表A生成表B。

我需要什么

从主表生成表B的单个SQL查询。

所以，我想我需要一个可以生成表A的查询，然后将它与上面的查询结合起来。

如果有更简单的方法从主表中获取表B，那么我欢迎它！

Answer 1

这是对我有用的最后一个查询。

谢谢你！你的回答最接近我了。

SELECT
    case 
        when comments between 1 and 2 then '1-2 comments'
        when comments between 3 and 4 then '3-4 comments'
        else '5+ comments'
    end as `Range`,
    count(1) as `Count` 
FROM( 
    SELECT id, 
        SUM(IF(Respect != '', 1, 0)) +
        SUM(IF(Accountability != '', 1, 0)) + 
        SUM(IF(Attendance != '', 1, 0))
    FROM `main table` 
    GROUP BY id
)AS T GROUP BY `Range`;

Answer 2

SELECT
    CASE
        WHEN Total BETWEEN 1 AND 2 THEN '1-2 Comments'
        WHEN Total BETWEEN 3 AND 4 THEN '3-4 Comments'
        WHEN Total >= 5 THEN '5+ Comments'
    END AS Range,
    COUNT(*) AS `Count`
FROM (
    SELECT
        id,
        SUM(CASE Attendance WHEN LENGTH(Attendence) > 0 OR Attendence IS NOT NULL THEN 1 ELSE 0 END CASE) + SUM(CASE Accountability WHEN LENGTH(Accountability) > 0 OR Accountability IS NOT NULL THEN 1 ELSE 0 END CASE) + SUM(CASE Respect WHEN LENGTH(Respect) > 0 OR Respect IS NOT NULL THEN 1 ELSE 0 END CASE) AS Total
    FROM
        MainTable
    GROUP BY
        id
) AS t
GROUP BY
    CASE
        WHEN Total BETWEEN 1 AND 2 THEN '1-2 Comments'
        WHEN Total BETWEEN 3 AND 4 THEN '3-4 Comments'
        WHEN Total >= 5 THEN '5+ Comments'
    END

Answer 3

在本例中，我使用了：

• 案例表达 -识别非空单元格
• 在两者之间和总和操作符-获取范围和行数
• 约翰总是准时的，约翰负责的，约翰总是恭敬的，( 2，'Ann从不错过一天‘，'Ann非常负责’，NULL )，( 3，NULL，'Dan非常无礼‘)-查询ROW_NUMBER() OVER() OVER(ORDER BY col) id，col范围，val计数从(选择SUM(当ctr介于1和2之间，然后选择其他0结尾) 1-2注释，SUM( ctr介于3和4之间，然后1其他0结束) 3-4注释，SUM(当ctr >= 5，然后1 5+ 0结束时)5+注释(选择考勤+问责+尊重ctr )(选择考勤不为空的情况)或者考勤<> '‘那么其他一个0结束出勤，如果问责不为空或问责<>‘则为10结束问责，当尊重不为零或尊重<> '‘时，则为1其他0结束尊重TBL UNPIVOT( val FOR col ( 1-2注释，3-4注释，5+注释))

结果

    id                   range               count
    -------------------- ------------------- -----------
    1                    1-2 comments        2
    2                    3-4 comments        1
    3                    5+ comments         0

    (3 row(s) affected)