SQL查询中的For循环

Question

我有一张名为“水源型”的桌子，它是由水类型组成的。

链接：水源型表

另一张由家庭编号组成的表health_and_sanitation。watersource_id我有一个查询：

SELECT h.purok_number,
    SUM(CASE WHEN hs.watersystem = 'Community water system-own' THEN 1 ELSE 0 END) AS a,
    SUM(CASE WHEN hs.watersystem = 'Community water system-shared' THEN 1 ELSE 0 END) AS b,
    SUM(CASE WHEN hs.watersystem = 'Deep well-own' THEN 1 ELSE 0 END) AS c,
    SUM(CASE WHEN hs.watersystem = 'Deep well-shared' THEN 1 ELSE 0 END) AS d,
    SUM(CASE WHEN hs.watersystem = 'Artesian well-own' THEN 1 ELSE 0 END) AS e,
    SUM(CASE WHEN hs.watersystem = 'Artesian well-shared' THEN 1 ELSE 0 END) AS f,
    SUM(CASE WHEN hs.watersystem = 'Dug/shallow well-own' THEN 1 ELSE 0 END) AS g,
    SUM(CASE WHEN hs.watersystem = 'Dug/shallow well-shared' THEN 1 ELSE 0 END) AS h,
    SUM(CASE WHEN hs.watersystem = 'River, stream, lake, spring, bodies of water' THEN 1 ELSE 0 END) AS i,
    SUM(CASE WHEN hs.watersystem = 'Bottled water' THEN 1 ELSE 0 END) AS j,
    SUM(CASE WHEN hs.watersystem = 'Tanker truck/Peddler' THEN 1 ELSE 0 END) AS k
FROM health_and_sanitation AS hs, house_hold AS h, f_member as f 
WHERE
     h.brgy_name='$brgy_name' AND 
     h.hh_number=hs.hh_number AND 
     h.hh_number=f.hh_number AND
     f.is_household='HOUSEHOLD' AND 
     EXTRACT(YEAR FROM f.reg_date) BETWEEN '$sel_year' AND '$sel_year' 
group by h.purok_number 
order by h.purok_number

我想要的是在上面的sql查询中放置一个for循环，因为表水源地类型是动态的，很快就会将另一个数据添加到水源地类型中，所以我不必在上述查询的case语句中定义水系统。查询应该如下所示：

$qry = pg_query("select cwatertype from tbl_watersourcetype");

SQL：

SELECT h.purok_number, 
           // is this possible ? putting a while loop or forloop inside a query in PHP ?
          while($row = pg_fetch_array($qry)) 
          {
           SUM(CASE WHEN hs.watersystem = '$row['cwatertype']' THEN 1 ELSE 0 END) AS a 
          }
FROM health_and_sanitation AS hs, house_hold AS h, f_member as f 
WHERE
    h.brgy_name='$brgy_name' AND 
    h.hh_number=hs.hh_number AND 
    h.hh_number=f.hh_number AND
    f.is_household='HOUSEHOLD' AND 
    EXTRACT(YEAR FROM f.reg_date) BETWEEN '$sel_year' AND '$sel_year' 
group by h.purok_number 
order by h.purok_number

这有可能吗？

Answer 1

如果我正确理解您的查询，您将尝试检索每个家庭的水源类型数，每个家庭返回一条记录。如果确实如此，您需要使用来自crosstab()扩展的tablefunc函数。

但是，如果您可以通过多行提供每个家庭每个水源地类型的源数，那么您可以简单和更有效地使用SELECT语句中的SELECT聚合(具有一些体面的可读格式)：

SELECT h.purok_number, hs.watersystem, COUNT(hs.watersystem) AS num
   FROM health_and_sanitation AS hs,
        house_hold AS h,
        f_member AS f
   WHERE h.brgy_name='$brgy_name'
     AND h.hh_number=hs.hh_number
     AND h.hh_number=f.hh_number
     AND f.is_household='HOUSEHOLD'
     AND EXTRACT(YEAR FROM f.reg_date) BETWEEN '$sel_year' AND '$sel_year'
   GROUP BY h.purok_number, hs.watersystem
   ORDER BY h.purok_number;

此外，我假设您的health_and_sanitation表是watersourcetype表上的一个视图；否则，您将在两个表中复制数据，这两个表(通常)是一个大boo。另外，考虑到附加水源类型的问题，您可能想看看您的数据库设计是否使用了3NF。

干杯，帕特里克