SQLの書き方 (ID:37604)

名前

ホームページ(ブログ、Twitterなど)のURL (省略可)

本文

見づらいので、sql文をかきなおします。

with query１ do
   begin
    close;
    sql.Clear;
    sql.add('select sum(age0) as 十歳未満, sum(age10) as 十代, 
              sum(age20) as 二十代');
    sql.add(', sum(age30) as 三十代, sum(age40) as 四十代, 
                sum(age50) as 五十代');
    sql.add(', sum(age60) as 六十代, sum(age70) as 七十代, 
            sum(age80) as 八十歳以上');
    sql.add('from (select 1 as age0, 0 as age10, 0 as age20, 
           0 as  age30, 0 as age40');
    sql.add(', 0 as age50, 0 as age60, 0 as age70, 
            0 as age80 from visitor where age < 10)');
    sql.add(' union all select 0 as age0, 1 as age10, 0 as age20, 
            0 as age30, 0 as age40');
    sql.add(', 0 as age50, 0 as age60, 0 as age70, 0 as age80 
            from visitor');
    sql.add(', where age between 10 and 19  union all select 
            0 as age0, 0 as age10');
    sql.add(', 1 as age20, 0 as age30, 0 as age40, 0 as age50, 
             0 as age60, 0 as age70');
    sql.add(', 0 as age80 from visitor where age between 
            20 and 29 union all');
    sql.add(' select 0 as age0, 0 as age10, 0 as age20, 
            1 as age30, 0 as age40, 0 as age50');
    sql.add(', 0 as age60, 0 as age70, 0 as age80 from visitor 
            where age between 30 and 39');
    sql.add(' union all select 0 as age0, 0 as age10, 0 as age20, 
              0 as age30, 1 as age40, 0 as age50');
    sql.add(', 0 as age60, 0 as age70, 0 as age80 
          from visitor where age');
    sql.add(', between 40 and 49 union all select 0 as age0, 
            0 as age10, 0 as age20');
    sql.add(', 0 as age30, 0 as age40, 1 as age50, 0 as age60, 
              0 as age70, 0 as age80');
    sql.add(' from visitor where age between 50 and 59 
              union all select 0 as age0');
    sql.add(', 0 as age10, 0 as age20, 0 as age30, 0 as age40, 
         0 as age50, 1 as age60');
    sql.add(', 0 as age70, 0 as age80 from visitor where age
           between 60 and 69');
    sql.add(' union all select 0 as age0, 0 as age10, 0 as age20,
            0 as age30, 0 as age40');
    sql.add(', 0 as age50, 0 as age60, 1 as age70, 0 as age80
             from visitor');
    sql.add('where age between 70 and 79union all select 
               0 as age0, 0 as age10, 0 as age20');
    sql.add(', 0 as age30, 0 as age40, 0 as age50, 0 as age60,
              0 as age70, 1 as age80');
    sql.add(' from visitor where age > 80)'); 
    sql.add('where ID = :i_ID');
    parambyName('i_ID').asinteger := strtoint(editID.Text);
    active := true;
   end;

←解決時は質問者本人がここをチェックしてください。

戻る