Нужна помощь в SQL (GROUP BY ... HAVING)

[KislotLord]

На такой запрос:

SELECT p.LAST_NAME,p.FIRST_NAME,COUNT(*) AS C
FROM DTP AS d,DTP_Auto AS da, People AS p
WHERE d.ID=da.ID_DTP AND p.ID=da.ID_PEOPLE GROUP BY (p.ID);

имеем:

+------------+------------+---+
| LAST_NAME | FIRST_NAME | C |
+------------+------------+---+
| Belov | Sergei | 3 |
| Voronin | Nikolai | 1 |
| Grigorev | Yurii | 2 |
| Zhukov | Vladimir | 2 |
| Zhukov | Aleksei | 2 |
| Kovalev | Sergei | 1 |
| Lebedev | Aleksandr | 1 |
| Zhukov | Anatolii | 3 |
| Stognienko | Vladimir | 2 |
| Stubarev | Viktor | 1 |
| Fomin | Aleksei | 1 |
+------------+------------+---+

Нужно выбрать только максимальные коунты!
Кто сможет помочь?

PS: без процедур, и в одном запросе!

[Voice]

Только с тройкой?
Тогда можно добавить ORDER BY C DESC LIMIT 0, 1
Или использовать ф-цию MAX.

http://dev.mysql.com/doc/refman/5.1/en/exa...aximum-row.html

[KislotLord]

да, только с 3-ой.

[KislotLord]

Не совсем понятно как сделать, если нет атрибута "C" не в одной из таблиц.

SELECT p.LAST_NAME,p.FIRST_NAME,COUNT(*) AS C
FROM DTP AS d,DTP_Auto AS da, People AS p
WHERE d.ID=da.ID_DTP AND p.ID=da.ID_PEOPLE GROUP BY (p.ID) HAVING C= (SELECT MAX© FROM ??????);

Вот так работает, но нужно не оду а все максимальные:

SELECT p.LAST_NAME,p.FIRST_NAME,COUNT(*) AS C
FROM DTP AS d,DTP_Auto AS da, People AS p
WHERE d.ID=da.ID_DTP AND p.ID=da.ID_PEOPLE GROUP BY (p.ID) ORDER BY C DESC LIMIT 1;

[sash-kan]

какой sql-сервер?

[KislotLord]

какой sql-сервер?

MySQL 5.x

[sash-kan]

в mssql должно сработать где-то так:
SELECT p.LAST_NAME,p.FIRST_NAME,COUNT(*) AS C
FROM DTP AS d,DTP_Auto AS da, People AS p
WHERE d.ID=da.ID_DTP AND p.ID=da.ID_PEOPLE GROUP BY (p.ID)
having count(*) in (select top 1 count(*) from DTP AS d,DTP_Auto AS da, People AS p
WHERE d.ID=da.ID_DTP AND p.ID=da.ID_PEOPLE GROUP BY (p.ID) order by count(*) desc)

в mysql где-то так:
SELECT p.LAST_NAME,p.FIRST_NAME,COUNT(*) AS C
FROM DTP AS d,DTP_Auto AS da, People AS p
WHERE d.ID=da.ID_DTP AND p.ID=da.ID_PEOPLE GROUP BY (p.ID)
having count(*) in (select count(*) from DTP AS d,DTP_Auto AS da, People AS p
WHERE d.ID=da.ID_DTP AND p.ID=da.ID_PEOPLE GROUP BY (p.ID) order by count(*) desc limit 1)

главное — принцип. надеюсь, он понятен

[KislotLord]

Принцип - ясен, но MySQL проглотить с первого раза не хочет:

ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

[Voice]

Не совсем понятно как сделать, если нет атрибута "C" не в одной из таблиц.

Ой, извиняюсь. Втыкнул, просто на результат посмотрел.

[KislotLord]

Вот сделал с вложением:
SELECT p.LAST_NAME,p.FIRST_NAME,COUNT(*) AS C FROM DTP AS d,DTP_Auto AS da, People AS p WHERE d.ID=da.ID_DTP AND p.ID=da.ID_PEOPLE GROUP BY (p.ID) HAVING C=(SELECT COUNT(*) AS C1 FROM DTP AS d,DTP_Auto AS da WHERE d.ID=da.ID_DTP GROUP BY (da.ID_PEOPLE) ORDER BY C1 DESC LIMIT 1);


Вот сделал с view:
DROP VIEW IF EXISTS view1; CREATE VIEW view1 AS (SELECT p.ID,p.LAST_NAME,p.FIRST_NAME,COUNT(*) AS C FROM DTP AS d,DTP_Auto AS da, People AS p WHERE d.ID=da.ID_DTP AND p.ID=da.ID_PEOPLE GROUP BY (p.ID) ORDER BY ©); SELECT * FROM view1 WHERE C=(SELECT MAX© FROM view1); DROP VIEW view1;

Кто сумеет оптимизировать? Есть идеи?

[CPU]

KislotLord
Выложи, плиз, скрипт на создание обсуждаемых таблиц и на заполнение тестовыми данными (короче, дамп всего этого дела в студию!)
Это позволит предлагать уже проверенные и работоспособные решения.

[KislotLord]

Код:

drop table Type_Auto; create table `Type_Auto`( ID int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT, NAME varchar(20)); drop table Mark_Auto; create table Mark_Auto( ID int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT, NAME varchar(20) NOT NULL DEFAULT 'Unknown', TYPE_AUTO int(11)); drop table People; create table People( ID int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT, LAST_NAME varchar(20) NOT NULL, FIRST_NAME varchar(20) NOT NULL, DOCUM_NUM varchar(10) NOT NULL, DOCUM_DATE date ); drop table Auto; create table Auto( ID int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT, MARK_AUTO int(11) NOT NULL, NUM varchar(10) NOT NULL, OWNER int(11) NOT NULL, REG_DATE date, NALOG int(11) NOT NULL ); drop table DTP; create table DTP( ID int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT ,DTP_DATE date ,USHERB int(11) NOT NULL ); drop table DTP_Auto; create table DTP_Auto( ID_DTP int(11) NOT NULL ,ID_AUTO int(11) NOT NULL ,ID_PEOPLE int(11) NOT NULL ,VINOVNIK int(11) NOT NULL); insert into Type_Auto values(1, 'motorcycle'); insert into Type_Auto values(2, 'car'); insert into Type_Auto values(3, 'truck'); insert into Type_Auto values(4, 'bus'); insert into Type_Auto values(5, 'trailer'); insert into Type_Auto values(6, 'tractor'); insert into Mark_Auto values(1, 'Honda', 1); insert into Mark_Auto values(2, 'Kawasaki', 1); insert into Mark_Auto values(3, 'Lada', 2); insert into Mark_Auto values(4, 'Volga', 2); insert into Mark_Auto values(5, 'BMW', 2); insert into Mark_Auto values(6, 'Volvo', 2); insert into Mark_Auto values(7, 'Toyota', 2); insert into Mark_Auto values(8, 'Zil', 3); insert into Mark_Auto values(9, 'Kamaz', 3); insert into Mark_Auto values(10, 'Volvo', 4); insert into Mark_Auto values(11, 'Zil', 4); insert into Mark_Auto values(12, 'Trailer', 5); insert into Mark_Auto values(13, 'K-700', 6); insert into Mark_Auto values(14, 'BMW', 6); INSERT INTO People values(1, 'Arehin', 'Sergei', 'A 000001', STR_TO_DATE('04.02.2005', '%d.%m.%Y')); INSERT INTO People values(2, 'Belov', 'Sergei', 'A 000002', STR_TO_DATE('28.03.2005', '%d.%m.%Y')); INSERT INTO People values(3, 'Voronin', 'Nikolai', 'A 000003', STR_TO_DATE('16.04.2005', '%d.%m.%Y')); INSERT INTO People values(4, 'Grigorev', 'Yurii', 'A 000004', STR_TO_DATE('14.05.2005', '%d.%m.%Y')); INSERT INTO People values(5, 'Zhukov', 'Vladimir', 'A 000005', STR_TO_DATE('25.01.2005', '%d.%m.%Y')); INSERT INTO People values(6, 'Zhukov', 'Aleksei', 'A 000006', STR_TO_DATE('02.03.2005', '%d.%m.%Y')); INSERT INTO People values(7, 'Kovalev', 'Sergei', 'A 000007', STR_TO_DATE('27.04.2005', '%d.%m.%Y')); INSERT INTO People values(8, 'Lebedev', 'Aleksandr', 'A 000008', STR_TO_DATE('15.07.2005', '%d.%m.%Y')); INSERT INTO People values(9, 'Zhukov', 'Anatolii', 'A 000009', STR_TO_DATE('19.04.2005', '%d.%m.%Y')); INSERT INTO People values(10, 'Stognienko', 'Vladimir', 'A 000010', STR_TO_DATE('07.05.2005', '%d.%m.%Y')); INSERT INTO People values(11, 'Stubarev', 'Viktor', 'A 000011', STR_TO_DATE('08.03.2005', '%d.%m.%Y')); INSERT INTO People values(12, 'Fomin', 'Aleksei', 'A 000012', STR_TO_DATE('13.01.2005', '%d.%m.%Y')); INSERT INTO People values(13, 'Fomin', 'Nikolai', 'A 000013', STR_TO_DATE('18.06.2005', '%d.%m.%Y')); INSERT INTO People values(14, 'Hazhoyan', 'Margarita', 'A 000014', STR_TO_DATE('13.06.2005', '%d.%m.%Y')); INSERT INTO People values(15, 'Horev', 'Aleksei', 'A 000015', STR_TO_DATE('03.07.2005', '%d.%m.%Y')); insert into Auto values(1, 13, 'D100', 12 ,STR_TO_DATE('08.03.2005', '%d.%m.%Y'),330); insert into Auto values(2, 4, 'D101', 2 ,STR_TO_DATE('13.01.2005', '%d.%m.%Y'),60); insert into Auto values(3, 7, 'D102', 4 ,STR_TO_DATE('18.06.2005', '%d.%m.%Y'),60); insert into Auto values(4, 9, 'D103', 5 ,STR_TO_DATE('13.06.2005', '%d.%m.%Y'),50); insert into Auto values(5, 1, 'D104', 9 ,STR_TO_DATE('03.07.2005', '%d.%m.%Y'),80); insert into Auto values(6, 2, 'D105', 6 ,STR_TO_DATE('04.06.2005', '%d.%m.%Y'),210); insert into Auto values(7, 6, 'D106', 11 ,STR_TO_DATE('21.02.2005', '%d.%m.%Y'),50); insert into Auto values(8, 10, 'D107', 8 ,STR_TO_DATE('22.11.2005', '%d.%m.%Y'),320); insert into Auto values(9, 14, 'D108', 7 ,STR_TO_DATE('17.10.2005', '%d.%m.%Y'), 220); insert into Auto values(10, 3, 'D109', 10,STR_TO_DATE('09.08.2005', '%d.%m.%Y'), 40); insert into Auto values(11, 12, 'D110', 1 ,STR_TO_DATE('13.08.2005', '%d.%m.%Y'), 150); insert into Auto values(12, 12, 'S100', 4 ,STR_TO_DATE('25.01.2005', '%d.%m.%Y'), 190); insert into Auto values(13, 1, 'S101', 14,STR_TO_DATE('02.03.2005', '%d.%m.%Y'), 70); insert into Auto values(14, 3, 'S102', 8 ,STR_TO_DATE('27.04.2005', '%d.%m.%Y'), 70); insert into Auto values(15, 4, 'S103', 5 ,STR_TO_DATE('15.07.2005', '%d.%m.%Y'),330); insert into Auto values(16, 6, 'S104', 13,STR_TO_DATE('19.04.2005', '%d.%m.%Y'),60); insert into Auto values(17, 5, 'S105', 7 ,STR_TO_DATE('07.05.2005', '%d.%m.%Y'),60); insert into Auto values(18, 11, 'S106', 8 ,STR_TO_DATE('04.06.2005', '%d.%m.%Y'),50); insert into Auto values(19, 2, 'S107', 9 ,STR_TO_DATE('21.02.2005', '%d.%m.%Y'),80); insert into Auto values(20, 10, 'S108', 10,STR_TO_DATE('22.11.2005', '%d.%m.%Y'),210); insert into Auto values(21, 7, 'S109', 1 ,STR_TO_DATE('17.10.2005', '%d.%m.%Y'),50); insert into Auto values(22, 14, 'S110', 2 ,STR_TO_DATE('09.08.2005', '%d.%m.%Y'),320); insert into Auto values(23, 5, 'W101', 6 ,STR_TO_DATE('13.08.2005', '%d.%m.%Y'), 220); insert into Auto values(24, 9, 'W102', 3 ,STR_TO_DATE('04.06.2005', '%d.%m.%Y'), 40); insert into Auto values(25, 3, 'W103', 5 ,STR_TO_DATE('21.02.2005', '%d.%m.%Y'), 150); --DTP insert into DTP values(1, STR_TO_DATE('03.01.2005', '%d.%m.%Y'),80); insert into DTP values(2, STR_TO_DATE('10.07.2005', '%d.%m.%Y'),280); insert into DTP values(3, STR_TO_DATE('13.07.2005', '%d.%m.%Y'),180); insert into DTP values(4, STR_TO_DATE('04.06.2005', '%d.%m.%Y'),210); insert into DTP values(5, STR_TO_DATE('14.06.2005', '%d.%m.%Y'),10); insert into DTP values(6, STR_TO_DATE('21.02.2005', '%d.%m.%Y'),50); insert into DTP values(7, STR_TO_DATE('19.04.2005', '%d.%m.%Y'),560); insert into DTP values(8, STR_TO_DATE('19.04.2005', '%d.%m.%Y'),260); insert into DTP_Auto values(1, 2 , 2 , 0 ); insert into DTP_Auto values(1, 5 , 9 , 1 ); insert into DTP_Auto values(1, 23 , 6 , 0 ); insert into DTP_Auto values(2, 1 , 12 , 0 ); insert into DTP_Auto values(2, 25 , 5 , 1 ); insert into DTP_Auto values(3, 7 , 11 , 1 ); insert into DTP_Auto values(3, 20 , 10 , 0 ); insert into DTP_Auto values(4, 13 , 6 , 0 ); insert into DTP_Auto values(4, 15 , 5 , 0 ); insert into DTP_Auto values(4, 4 , 9 , 1 ); insert into DTP_Auto values(4, 22 , 2 , 0 ); insert into DTP_Auto values(5, 3 , 4 , 0 ); insert into DTP_Auto values(5, 9 , 7 , 1 ); insert into DTP_Auto values(5, 17 , 2 , 0 ); insert into DTP_Auto values(6, 12 , 4 , 1 ); insert into DTP_Auto values(7, 8 , 8 , 0 ); insert into DTP_Auto values(7, 4 , 10 , 1 ); insert into DTP_Auto values(8, 5 , 9 , 1 ); insert into DTP_Auto values(8, 13 , 3 , 0 );

[sash-kan]

Кто сумеет оптимизировать?

а куда еще оптимизировать? с заменой «in» на «=» в having-е запрос заработал? вот это и есть самое оптимальное решение.

[sash-kan]

SELECT p.LAST_NAME,p.FIRST_NAME,COUNT(*) AS C FROM DTP AS d,DTP_Auto AS da, People AS p WHERE d.ID=da.ID_DTP AND p.ID=da.ID_PEOPLE GROUP BY (p.ID) HAVING C=(SELECT COUNT(*) AS C1 FROM DTP AS d,DTP_Auto AS da WHERE d.ID=da.ID_DTP GROUP BY (da.ID_PEOPLE) ORDER BY C1 DESC LIMIT 1);

только сейчас обратил внимание. Вы же совсем другой запрос сделали в having-е.
ну, если Вам нужно именно так, то, конечно. но, вообще-то, получится не совсем то, чего Вы хотели изначально.
возьмите тот запрос, что я написал двумя постами выше и замените «in» на «=».
должно получиться именно то, что требовалось в самом первом посте.