mysql - 如何在特定行中查找具有空值的第一列名称

Question

下面的查询可以很好地返回空列名，我想要完成的是让它返回到具有空值的第一个列名。我还有另一个名为 email 的列，每一行都有一个唯一的电子邮件地址。我的问题是我无法找出执行此操作的适当语法。任何帮助表示赞赏!

为此我也在使用 php5.2。

 SELECT col FROM (SELECT 'GAME0' AS col, GAME0 AS value FROM USERNAME
       UNION ALL SELECT 'GAME1', GAME1 FROM USERNAME
       UNION ALL SELECT 'GAME2', GAME2 FROM USERNAME
       UNION ALL SELECT 'GAME3', GAME3 FROM USERNAME
       UNION ALL SELECT 'GAME4', GAME4 FROM USERNAME
       UNION ALL SELECT 'GAME5', GAME5 FROM USERNAME
       UNION ALL SELECT 'GAME6', GAME6 FROM USERNAME
       UNION ALL SELECT 'GAME7', GAME7 FROM USERNAME
       UNION ALL SELECT 'GAME8', GAME8 FROM USERNAME
       UNION ALL SELECT 'GAME9', GAME9 FROM USERNAME
       UNION ALL SELECT 'GAME10', GAME10 FROM USERNAME
       UNION ALL SELECT 'GAME11', GAME11 FROM USERNAME
       UNION ALL SELECT 'GAME12', GAME12 FROM USERNAME
       UNION ALL SELECT 'GAME13', GAME13 FROM USERNAME
       UNION ALL SELECT 'GAME14', GAME14 FROM USERNAME
       UNION ALL SELECT 'GAME15', GAME15 FROM USERNAME
       UNION ALL SELECT 'GAME16', GAME16 FROM USERNAME
       UNION ALL SELECT 'GAME17', GAME17 FROM USERNAME
       UNION ALL SELECT 'GAME18', GAME18 FROM USERNAME
       UNION ALL SELECT 'GAME19', GAME19 FROM USERNAME
    ) allValues 
    WHERE value is null and email='emailaddress'

编辑:如果不需要上面的查询，我需要像下面这样的东西，我可以在其中更改第一个空值的值。同样，语法是我最大的问题。

update username
SET CASE GAME0
    WHEN GAME0 IS NOT NULL THEN GAME1
    WHEN GAME1 IS NOT NULL THEN GAME2
    WHEN GAME2 IS NOT NULL THEN GAME3
    WHEN GAME3 IS NOT NULL THEN GAME4
    WHEN GAME4 IS NOT NULL THEN GAME5
    WHEN GAME5 IS NOT NULL THEN GAME6
    WHEN GAME7 IS NOT NULL THEN GAME8
    WHEN GAME8 IS NOT NULL THEN GAME9
    WHEN GAME9 IS NOT NULL THEN GAME10
    WHEN GAME10 IS NOT NULL THEN GAME11
    WHEN GAME11 IS NOT NULL THEN GAME12
    WHEN GAME12 IS NOT NULL THEN GAME13
    WHEN GAME13 IS NOT NULL THEN GAME14
    WHEN GAME14 IS NOT NULL THEN GAME15
    WHEN GAME15 IS NOT NULL THEN GAME16
    WHEN GAME16 IS NOT NULL THEN GAME17
    WHEN GAME17 IS NOT NULL THEN GAME18
    WHEN GAME18 IS NOT NULL THEN GAME19
  END where EMAIL='emailaddress'

Answer 1

您可以使此查询更简单，并通过 php 进行一些计算:

$arr_field = array();
foreach(range(0, 19) as $i)
    $arr_field[] = "GAME{$i}";

$str_add = implode(' + ', $arr_field);
$str_select = implode(', ', $arr_field);

$query = 
mysql_query("
    SELECT {$str_select}
    FROM USERNAME
    WHERE ({$str_add}) IS NULL AND email = 'emailaddress'
");

// Then get the NULL column through a loop

while($row = mysql_fetch_assoc($query))
    foreach($arr_field as $h)
        if ( ! isset($row[$h])) {

            echo "Found column! {$h}<br />";
            break;

        }

Baiscly 我将所有 20 个字段加在一起，如果这 20 个值中的任何一个为 NULL，则结果变为 NULL。我获取所有 20 个值，然后循环遍历 php 以查找 NULL 字段。

老实说，我会制作另一个结构，用第二个表来处理这些 GAME0 - GAME19 字段。