| telepath |
| Отправлено: 09.03.2005, 15:05 |
|
Станционный диспетчер
Группа: Участник
Сообщений: 132
|
Можно ли получить результат сгруппированный по двум столбцам, если третий результат статистической функции.
Например, есть таблица REC
поля:
rec_id (id записи)
years (за какой год запись)
per_id (за какой период)
org_id (от какой организации)
мне нужно получить список id записей последних периодов за год
запрос типа:
| CODE |
SELECT rec.org_id, max(rec.per_id) FROM rec WHERE
rec.years_num=2004
group by rec.org_id |
работает, но выводит только org_id и max
никак не могу прикрутить сюда rec_id.
В книжке прочитал, что при группировке по двум столбцам sql обеспечивает только один уровень группировки. Можно ли это как-то обойти или сделать по другому?
Субд FireBird, работаю через FIBPlus |
|
| olegenty |
| Отправлено: 09.03.2005, 15:34 |
|
Ветеран
Группа: Модератор
Сообщений: 2412
|
а что,
| SQL | SELECT
rec.rec_id,
rec.org_id,
max(rec.per_id)
FROM rec WHERE rec.years_num=2004
group by
rec.rec_id,
rec.org_id
|
не даёт желаемого результата?
кстати, если rec_id — уникальный ID записи, то такой запрос бессмысленен, записей будет столько, сколько rec_id придётся на условие rec.years_num=2004
|
|
| AVC |
| Отправлено: 09.03.2005, 15:42 |
|
Ветеран
Группа: Модератор
Сообщений: 1583
|
А может нужен первый встречный rec_id (0 или 1) для каждого org_id у которого per_id максимален внутри группы org_id ? |
|
| telepath |
| Отправлено: 09.03.2005, 15:51 |
|
Станционный диспетчер
Группа: Участник
Сообщений: 132
|
да, rec_id уникальный идентификатор
нужна выборка max(per_id) из групп org_id и rec_id этих записей
т.е. записей в результате должно быть не больше кол-ва организаций
Отредактировано telepath — 09/03/2005, 15:54 |
|
| olegenty |
| Отправлено: 09.03.2005, 15:59 |
|
Ветеран
Группа: Модератор
Сообщений: 2412
|
тогда тебе нужно делать это в 2 этапа:
1. получить org_id и max(per_id)
2. по некоторому правилу зацепить rec_id, например, максимальный или минимальный rec_id для записи, удовлетворяющей НД действия 1
в Firebird 1.5 ещё НЕ поддерживает подзапросов, поэтому, как ни грустно, действие п.1 надо выносить либо во VIEW, либо в ХП (предпочтительно ХП с условием по years_num), а затем результат VIEW/ХП джоинить по условию с этой же таблицей для вытягивания rec_id
|
|
| AVC |
| Отправлено: 09.03.2005, 16:02 |
|
Ветеран
Группа: Модератор
Сообщений: 1583
|
Рабочий вариант (если я правильно понял вопрос)
| CODE |
/*
create table rec
(rec_id integer
,year_num integer
,org_id integer
,per_id integer
);
*/
--delete from rec;
--insert into rec Values (1,2005,1,1);
--insert into rec Values (2,2005,1,1);
--insert into rec Values (3,2005,1,2);
--insert into rec Values (4,2005,1,2);
--insert into rec Values (5,2005,2,1);
--insert into rec Values (6,2005,2,1);
--insert into rec Values (7,2005,2,2);
--insert into rec Values (8,2005,2,2);
--insert into rec Values (9,2005,2,2);
-- Select * From rec
Select
-- r.*
-- ,r.org_id * 10000 + r.per_id
max(rec_id) as rec_id
From rec r
Where 1 = 1
and (r.org_id * 10000 + r.per_id) in
(
Select (r1.org_id * 10000 + max(r1.per_id)) From rec r1
Where r1.year_num = 2005
Group by r1.org_id
)
Group by r.org_id, r.per_id
-- возвращает Rec_id = 4, 9
|
Жаль, что это FB
|
|
| telepath |
| Отправлено: 09.03.2005, 16:02 |
|
Станционный диспетчер
Группа: Участник
Сообщений: 132
|
понятно, жаль...
Спасибо  |
|
| olegenty |
| Отправлено: 09.03.2005, 16:10 |
|
Ветеран
Группа: Модератор
Сообщений: 2412
|
я припозднился с ответом, но, всё же, прочти сообщение, что до AVC, предлагаемое там решение — рабочее.
|
|
| telepath |
| Отправлено: 09.03.2005, 16:55 |
|
Станционный диспетчер
Группа: Участник
Сообщений: 132
|
про ХП я писал, у нас админы дурные 
если только представление сделать.
Попробую что быстрее получится, хотя этот запрос достаточно редко будет использоваться |
|
| olegenty |
| Отправлено: 09.03.2005, 17:06 |
|
Ветеран
Группа: Модератор
Сообщений: 2412
|
кстати, если правило для rec_id будет заключаться в min/max, то всё ещё проще:
| SQL | SELECT org_id, max(rec_id), max(per_id)
FROM rec WHERE years_num=2004
group by
rec.org_id
|
|
|
| AVC |
| Отправлено: 09.03.2005, 17:15 |
|
Ветеран
Группа: Модератор
Сообщений: 1583
|
Красиво. |
|
| telepath |
| Отправлено: 09.03.2005, 17:38 |
|
Станционный диспетчер
Группа: Участник
Сообщений: 132
|
ух!
Спасибо |
|
| AVC |
| Отправлено: 09.03.2005, 18:17 |
|
Ветеран
Группа: Модератор
Сообщений: 1583
|
Извиняюсь. Красиво, но неправильно.
Мы находим max(rec_id) для данного org_id, а нужно max(rec_id) для данного org_id и для данного (максимального) per_id
Например, если добавить в мой скрипт
insert into rec Values (10, 2005, 2, 1);
то все увидете сами |
|
| olegenty |
| Отправлено: 10.03.2005, 08:19 |
|
Ветеран
Группа: Модератор
Сообщений: 2412
|
да, правильно, я такое повидение и имел в виду для простейшего правила (оно же вырожденное). а общий случай — как раньше сказал...
идеально решается как select ... from (select ... from rec ...) x join rec r ...
|
|
| olegenty |
| Отправлено: 10.03.2005, 08:42 |
|
Ветеран
Группа: Модератор
Сообщений: 2412
|
2 telepath вот тебе реальное рабочее решение для Firebird (для других СУБД это будет скорее изврат), которое я имел в виду (оттестил, даже работает):
| SQL | /*create table rec
(rec_id integer
,year_num integer
,org_id integer
,per_id integer
);*/
--delete from rec; --insert into rec Values (1,2005,1,1);
--insert into rec Values (2,2005,1,1);
--insert into rec Values (3,2005,1,2);
--insert into rec Values (4,2005,1,2);
--insert into rec Values (5,2005,2,1);
--insert into rec Values (6,2005,2,1);
--insert into rec Values (7,2005,2,2);
--insert into rec Values (8,2005,2,2);
--insert into rec Values (9,2005,2,2);
--insert into rec Values (10,2005,2,1);
/*create procedure sp_ord_max_per(year integer)
returns (org_id integer, per_id integer)
as begin for select org_id, max(per_id)
FROM rec WHERE year_num = :year
group by org_id
into :org_id, :per_id
do
suspend;
end*/
--select * from sp_ord_max_per(2005) select max(r.rec_id) rec_id, s.org_id, s.per_id
from sp_ord_max_per(2005) s
inner join rec r on s.org_id = r.org_id and s.per_id = r.per_id
group by s.org_id, s.per_id
|
результат как раз тот, что был нужен:
REC_ID ORG_ID PER_ID
4 1 2
9 2 2
|
|
| olegenty |
| Отправлено: 10.03.2005, 08:47 |
|
Ветеран
Группа: Модератор
Сообщений: 2412
|
грёбаный тег SQL всё испортил, имелось в виду следующее:
| CODE |
/*create table rec
(rec_id integer
,year_num integer
,org_id integer
,per_id integer
);*/
--delete from rec;
--insert into rec Values (1,2005,1,1);
--insert into rec Values (2,2005,1,1);
--insert into rec Values (3,2005,1,2);
--insert into rec Values (4,2005,1,2);
--insert into rec Values (5,2005,2,1);
--insert into rec Values (6,2005,2,1);
--insert into rec Values (7,2005,2,2);
--insert into rec Values (8,2005,2,2);
--insert into rec Values (9,2005,2,2);
--insert into rec Values (10,2005,2,1);
/*create procedure sp_ord_max_per(year integer)
returns (org_id integer, per_id integer)
as
begin
for select org_id, max(per_id)
FROM rec
WHERE year_num = :year
group by org_id
into :org_id, :per_id
do
suspend;
end*/
--select * from sp_ord_max_per(2005)
select max(r.rec_id) rec_id, s.org_id, s.per_id
from sp_ord_max_per(2005) s
inner join rec r on s.org_id = r.org_id
and s.per_id = r.per_id
group by s.org_id, s.per_id
|
2 2
|
|
| telepath |
| Отправлено: 10.03.2005, 11:15 |
|
Станционный диспетчер
Группа: Участник
Сообщений: 132
|
спасибо, учту, сейчас бьюсь за использование ХП |
|
|
