よく使うSQL文メモ（PostgreSQL）

f:id:tsumayouzi:20150319180732p:plain

table.AにあってBにない数系

select count(distinct id ) from テーブルA where id not in (select s_id from テーブルB group by id);

項目の軸が複数あるとき

rowでうまく取れないとき

//最初に縦列を取得
$sql1 = "SELECT c.schedule_id FROM camp_gift_view as c JOIN {$table_name} as a USING(gift_id) where c.schedule_id is not null group by c.schedule_id order by schedule_id desc";
$result = pg_query($conn, $sql1);
$data = pg_fetch_all($result);
$data_num = pg_num_rows($result);

#テーブル出力
print "<table border=1 class=\"data-table\"><tr><td>スケジュールID</td><td>在庫</td><td>当選中</td><td>受け取り済</td></tr>";
for ($i =0; $i <= $data_num; $i++) {
    
    echo "<tr>";
    echo "<td>{$data[$i]['schedule_id']}</td>";
    if(isset($data[$i]['schedule_id'])){
    //スケジュールIDごとステータスを取得
    $sql1 = "SELECT status, count(*) FROM camp_gift_view as c JOIN {$table_name} as a USING(gift_id) where schedule_id = {$data[$i]['schedule_id']} group by c.status,c.schedule_id";
    $result = pg_query($conn, $sql1);
    $data2 = pg_fetch_all($result);
    $data2_num = pg_num_rows($result);

    for ($m =0; $m <= $data2_num; $m++) {
        if($data2[$m]['status']==0){ $status0 = $data2[$m]['count']; }
        if($data2[$m]['status']==1){ $status1 = $data2[$m]['count']; }
        if($data2[$m]['status']==2){ $status2 = $data2[$m]['count']; }
    }
print <<<HTML
    <th>{$status0}</th>
    <th>{$status1}</th>
    <th>{$status2}</th>
HTML;
    }
    echo "</tr>";
    
}
print "</table><br>";