用空行填充查询结果 [英] Populating query results with empty rows

问题描述

我有以下查询，用于返回游戏中的最高分，但如果记录少于 10 条，我希望它由空行填充:

I have the following query which I use to return the highest scores in a game but I would like it to be populated by empty rows if there is less than 10 records:

SELECT id, MAX(score) mscore FROM scores WHERE id IN (".implode(',', $data).") GROUP BY id ORDER BY mscore DESC"

逻辑是将一组用户ID传递给查询，然后对得分最高的ID进行排序并形成前十名，很多ID还没有得分，因此一些前十名查询仅返回 3 或 4 行.如何填写查询结果直到我有 10 个?身份证件参考照片等.

The logic is that an array of user IDs will be passed to the query and then the highest scoring IDs will be sorted and a top ten will be formed, a lot of IDs do not have a score yet and therefore some top ten query return only 3 or 4 rows. How can I fill out the query results until i have 10? The ID references photos and so on.

推荐答案

就像@Thariaman 所说的 (+1) 不要在 MySql 中这样做，因为它在未来会更加麻烦且难以支持.

Like @Thariaman said (+1) don't do that in MySql as it's much more hassle and harder to support in the future.

我会尝试这样的事情(用空键填充数组，这样你的脚本就不会在你尝试访问它们(或其他东西)时抛出警告)

I'd try something like this (filling the array with empty keys so you script doesn't throw Warnings if you try to access them (or something))

function getMaxScores($gameid) {

    $result = query("SELECT id, MAX(score) mscore FROM scores ".
        "WHERE id IN (".implode(',', $data).")".
        "GROUP BY id ORDER BY mscore DESC LIMIT 10"
    );
    if(count($result) < 10) {
        $result = array_pad($result, 10, array("id" => "", "mscore" => "");
    }
    return $result;
}

这篇关于用空行填充查询结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！