php - SQL查询中的for循环

Question

我有一个名为 watersourcetype 的表，其中包含水的类型。

链接:watersourcetype TABLE

和另一个表 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 循环，因为表 watersourcetype 是动态的，另一个数据将很快添加到 watersourcetype，所以我不必在我的 case 语句中定义上面的查询 watersystem。查询应如下所示:

$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

如果我对您的查询的理解正确，您正在尝试检索每个家庭的水源类型数量，每个家庭返回一条记录。如果确实是这种情况，您需要使用 tablefunc 扩展中的 crosstab() 函数。

但是，如果您可以通过多行给出每个家庭每种水源类型的水源数量，那么您可以简单而更有效地使用 SELECT 中的 COUNT() 聚合code> 语句(带有一些体面的格式以便于阅读):

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 表的一个 View ；否则，您将在两个表中复制数据，这(通常)是一个大问题。考虑到您对其他水源类型的问题，您可能想看看您的数据库设计是否在 3NF 中。 .

干杯，帕特里克