循环选择查询 [英] Looping in select query

问题描述

我想做这样的事情：

select id,
    count(*) as total,
    FOR temp IN SELECT DISTINCT somerow FROM mytable ORDER BY somerow LOOP
    sum(case when somerow = temp then 1 else 0 end) temp,
    END LOOP;
from mytable
group by id
order by id

I创建的工作选择：

select id,
    count(*) as total,
    sum(case when somerow = 'a' then 1 else 0 end) somerow_a,
    sum(case when somerow = 'b' then 1 else 0 end) somerow_b,
    sum(case when somerow = 'c' then 1 else 0 end) somerow_c,
    sum(case when somerow = 'd' then 1 else 0 end) somerow_d,
    sum(case when somerow = 'e' then 1 else 0 end) somerow_e,
    sum(case when somerow = 'f' then 1 else 0 end) somerow_f,
    sum(case when somerow = 'g' then 1 else 0 end) somerow_g,
    sum(case when somerow = 'h' then 1 else 0 end) somerow_h,
    sum(case when somerow = 'i' then 1 else 0 end) somerow_i,
    sum(case when somerow = 'j' then 1 else 0 end) somerow_j,
    sum(case when somerow = 'k' then 1 else 0 end) somerow_k 
from mytable
group by id
order by id

这可行，但是它是静态的-如果将一些新值添加到 somerow中，我将不得不手动更改sql以从somerow列中获取所有值，这就是为什么我想知道是否有可能

this works, but it is 'static' - if some new value will be added to 'somerow' I will have to change sql manually to get all the values from somerow column, and that is why I'm wondering if it is possible to do something with for loop.

所以我想得到的是：

id    somerow_a    somerow_b    ....
0     3            2            ....
1     2            10           ....
2     19           3            ....
.     ...          ...
.     ...          ...
.     ...          ...

所以我想做的是计算所有里面有一些特定的字母，并按id分组（此id不是主键，但它是重复的-id可能有大约80个不同的值）。

So what I'd like to do is to count all the rows which has some specific letter in it and group it by id (this id isn't primary key, but it is repeating - for id there are about 80 different values possible).

http://sqlfiddle.com/#!15/18feb/2

推荐答案

数组对您有好处吗？ （SQL小提琴）

Are arrays good for you? (SQL Fiddle)

select
    id,
    sum(totalcol) as total,
    array_agg(somecol) as somecol,
    array_agg(totalcol) as totalcol
from (
    select id, somecol, count(*) as totalcol
    from mytable
    group by id, somecol
) s
group by id
;
 id | total | somecol | totalcol 
----+-------+---------+----------
  1 |     6 | {b,a,c} | {2,1,3}
  2 |     5 | {d,f}   | {2,3}

在9.2中，可能有一组JSON对象（小提琴）

In 9.2 it is possible to have a set of JSON objects (Fiddle)

select row_to_json(s)
from (
    select
        id,
        sum(totalcol) as total,
        array_agg(somecol) as somecol,
        array_agg(totalcol) as totalcol
    from (
        select id, somecol, count(*) as totalcol
        from mytable
        group by id, somecol
    ) s
    group by id
) s
;
                          row_to_json                          
---------------------------------------------------------------
 {"id":1,"total":6,"somecol":["b","a","c"],"totalcol":[2,1,3]}
 {"id":2,"total":5,"somecol":["d","f"],"totalcol":[2,3]}

在9.3中，添加了侧面，单个对象（小提琴）

In 9.3, with the addition of lateral, a single object (Fiddle)

select to_json(format('{%s}', (string_agg(j, ','))))
from (
    select format('%s:%s', to_json(id), to_json(c)) as j
    from
        (
            select
                id,
                sum(totalcol) as total_sum,
                array_agg(somecol) as somecol_array,
                array_agg(totalcol) as totalcol_array
            from (
                select id, somecol, count(*) as totalcol
                from mytable
                group by id, somecol
            ) s
            group by id
        ) s
        cross join lateral
        (
            select
                total_sum as total,
                somecol_array as somecol,
                totalcol_array as totalcol
        ) c
) s
;
                                                                to_json                                                                
---------------------------------------------------------------------------------------------------------------------------------------
 "{1:{\"total\":6,\"somecol\":[\"b\",\"a\",\"c\"],\"totalcol\":[2,1,3]},2:{\"total\":5,\"somecol\":[\"d\",\"f\"],\"totalcol\":[2,3]}}"

在9.2中也可以使用子查询而不是 lateral

In 9.2 it is also possible to have a single object in a more convoluted way using subqueries in instead of lateral

这篇关于循环选择查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！