通过分配"-"返回所有行，包括不存在的行.价值 - return all rows include non-existed row by assign "-" value

我有一个表"AA"下面。其中由这段代码创建的表结构

CREATE TABLE "AA" (
height character varying DEFAULT '-'::character varying,
class character varying NOT NULL,
gender character varying NOT NULL,
origin character varying NOT NULL
);

则数据为

Height  class   gender  origin
162        1      m      a
169        1      f      a
172        1      m      b
169        2      f      b
171        2      f      a

然后我想得到包含类(1,3)和性别(m)和起源(a,b)的组合的查询如果我想要的结果看起来像这样，我怎么才能实现它呢

Height  class   gender  origin
162       1       m       a
172       1       m       b
-         3       m       a
-         3       m       b

我试一试

Select COALESCE(height), class, gender, origin 
FROM AA 
WHERE class in ('1','3') and gender in ('m') and origin in ('a','b')

，但它只返回第二表的两行以上。

那我试试这个//在php中

<?php
    $class = array('1', '3');
    $gender = array('m');
    $origin = array('a', 'b');
    for ($i = 0; $i < count($class); $i++) {
        for ($j = 0; $j < count($gender); $j++) {
            for ($k = 0; $k < count($origin); $k++) {
                $query = 'select * from "AA" where ' .
                        " class = '" . $class[$i] .
                        "' and gender = '" . $gender[$j] .
                        "' and origin = '" . $origin[$k] . "' ";
                $result = pg_query($query) or die('Query failed: ' . pg_last_error());
                $row = pg_fetch_assoc($result);
                if (!$row) {
                    $row['height'] = "-";
                    $row['class'] = $class[$i];
                    $row['gender'] = $gender[$j];
                    $row['origin'] = $origin[$k];
                }
                if (is_null($row['height'])) {
                    $row['height'] = '-';
                }
                print_r($row);
            }
        }
    }
    ?>

。是否有任何有效的技术来检查零行结果并将"-"值分配给特定的列?b.在php或sql中是否有更好的迭代，以及如何使动态迭代，如果字段大于3(按比例增加)?

问候。

1从sql中读取所有现有值到一个关联数组中，该数组按类、性别和关联索引。

2遍历所有可能的组合，然后使用isset检测&填写缺失的组合。

$query = "Select height, class, gender, origin FROM AA WHERE class in ('1','3') and gender in ('m') and origin in ('a','b')";
$result = pg_query($query) or die(pg_last_error());
while($row = pg_fetch_assoc($result)) {
    //read in all existing values and index by class, gender, origin combo
    $key = $row['class'] . $row['gender'] . $row['origin'];
    $rows[$key] = $row;
}
$classes = array('1', '3');
$genders = array('m');
$origins = array('a', 'b');
//iterate over all possible combinations
foreach($classes as $class) {
    foreach($genders as $gender) {
        foreach($origins as $origin) {
            $key = $class . $gender . $origin;
            if(!isset($rows[$key])) { 
                //we're missing a value for the given class/origin/gender combo, so fill it in
                $rows[$key] = array(
                    'height' => '-',
                    'class' => $class,
                    'gender' => $gender,
                    'origin' => $origin,
                );
            }
        }
    }
}
print_r($rows);

其中最重要的部分是使所有列表正确。第二步是把'-'放到正确的位置。您可以使用cross join完成第一部分，通过明智地使用cast()和left outer join完成第二部分:

select (case when t.height is null then '-' else cast(height as varchar(255)) end) as height,
       c.theclass. g.gender, o.origin
from (select 1 as theclass union all select 2) c cross join
     (select 'm' as gender) g cross join
     (select 'a' as origin union all select 'b') o left outer join
     table t
     on t.class = c.theclass and t.gender = g.gender  and t.origin = o.origin;

编辑:

如果class是字符，试试:

select (case when t.height is null then '-' else cast(height as varchar(255)) end) as height,
       c.class. g.gender, o.origin
from (select '1' as class union all select '2') c cross join
     (select 'm' as gender) g cross join
     (select 'a' as origin union all select 'b') o left outer join
     table t
     on t.class = c.class and t.gender = g.gender  and t.origin = o.origin;