LINUX.ORG.RU

SQL. Помогите с запросом.

 , , ,


0

2

Новичек в SQL. Столкнулся с такой проблемой.

Есть таблица A:

id: integer

Есть несколько таблиц от B-F:

a_id: integer,
mystr: varchar(100)

Требуется посчитать количество записей в A, для которых __количество__ mystr (по всем таблицам B-F) > N (ну скажем N=100). Т.е. на выходе долно получиться одно число.


with 
	a as (select distinct (generate_series(1,1000) * random())::int as id),
	b as (select (generate_series(1,10000) * random())::int as id, 'b' as str),
	c as (select (generate_series(1,10000) * random())::int as id, 'c' as str),
    d as (select (generate_series(1,10000) * random())::int as id, 'd' as str),
	e as (select (generate_series(1,10000) * random())::int as id, 'e' as str),
	f as (select (generate_series(1,10000) * random())::int as id, 'f' as str)

select count(*) from (
    select sum(x), id from (
        select count(b.id) as x, a.id from a inner join b on a.id = b.id group by 2
        union all
        select count(c.id) as x, a.id from a inner join c on a.id = c.id group by 2
        union all
        select count(d.id) as x, a.id from a inner join d on a.id = d.id group by 2
        union all
        select count(e.id) as x, a.id from a inner join e on a.id = e.id group by 2
        union all
        select count(f.id) as x, a.id from a inner join f on a.id = f.id group by 2
    ) _ group by 2
    having sum(x) > 40
) _
drsm ()
Последнее исправление: drsm (всего исправлений: 1)