mysql - 在 json 数据中插入缺失的值范围

Question

我正在查询 mysql 数据库以返回用户输入某个疼痛评分的次数。查询如下所示:

SELECT 
    User,
    CAST(Value as UNSIGNED)as Pain_Score, 
    COUNT(Value) as Pain_Score_Count
FROM
    pain_score
WHERE
    Text = 'Pain Score'
AND
    User = :user
GROUP BY 
    Value
ORDER BY
    Pain_Score
ASC

表架构:

Field   Type
id      int(10)
Text    varchar(250)
User    varchar(255)
Value   varchar(15000)

查询返回 json 格式的数据，如下所示:

[
    {
        "User": "Test, User",
        "Pain_Score": "0",
        "Pain_Score_Count": "6"
    },
    {
        "User": "Test, User",
        "Pain_Score": "5",
        "Pain_Score_Count": "4"
    },
    {
        "User": "Test, User",
        "Pain_Score": "6",
        "Pain_Score_Count": "3"
    },
    {
        "User": "Test, User",
        "Pain_Score": "7",
        "Pain_Score_Count": "1"
    },
    {
        "User": "Test, User",
        "Pain_Score": "8",
        "Pain_Score_Count": "4"
    }
]

但是对于返回的任何给定数据集，我需要一个从 0 到 10 的 Pain_Score 范围，即:

[
        {
            "User": "Test, User",
            "Pain_Score": "0",
            "Pain_Score_Count": "6"
        },
        {
            "User": "Test, User",
            "Pain_Score": "1",
            "Pain_Score_Count": "0"
        },
        {
            "User": "Test, User",
            "Pain_Score": "2",
            "Pain_Score_Count": "0"
        },
        {
            "User": "Test, User",
            "Pain_Score": "3",
            "Pain_Score_Count": "0"
        },
        {
            "User": "Test, User",
            "Pain_Score": "4",
            "Pain_Score_Count": "0"
        },
        {
            "User": "Test, User",
            "Pain_Score": "5",
            "Pain_Score_Count": "4"
        },
        {
            "User": "Test, User",
            "Pain_Score": "6",
            "Pain_Score_Count": "3"
        },
        {
            "User": "Test, User",
            "Pain_Score": "7",
            "Pain_Score_Count": "1"
        },
        {
            "User": "Test, User",
            "Pain_Score": "8",
            "Pain_Score_Count": "4"
        },
        {
            "User": "Test, User",
            "Pain_Score": "9",
            "Pain_Score_Count": "0"
        },
        {
            "User": "Test, User",
            "Pain_Score": "10",
            "Pain_Score_Count": "0"
        }
    ]

是否有一种方法可以通过编辑查询来返回 Pain_Score 值的缺失范围，或者是否有一种简单的方法可以为一组给定的随机 Pain_Score 值插入缺失值？

Answer 1

您需要综合所有想要包含的数字并加入其中。这是一种方法。 DBFiddle

SELECT
    User,
    num as Pain_Score,
    COUNT(Value) as Pain_Score_Count
FROM
    pain_score
RIGHT JOIN (select 0 num union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) nums
    ON num = Value
    AND User = "Test, User"
    AND Text = "Pain Score"
GROUP BY 
    num, User
ORDER BY num ASC

在此示例中，我对用户进行了硬编码，但您应该能够调整 SELECT 和 JOIN 语句来替换 User和 "Test, User" 分别使用 :User 变量。