SQLinfo.ru - Все о MySQL

Форум пользователей MySQL

Задавайте вопросы, мы ответим

Вы не зашли.

#1 10.09.2009 18:20:28

smallhacker
Участник
Зарегистрирован: 10.09.2009
Сообщений: 9

Группировка в запросе

Дано:
1. Есть таблица групп.
2. Есть таблица потомков групп.

Реально ли одним запросом вывести по три потомка для каждой группы связая эти две таблицы?
Как бы не хочется писать цикл с запросами. Имхо нагрузка на сервер.


Комментарий модератора.
Реально. В статье Выбрать несколько записей из каждой группы приведено 6 вариантов решения в один запрос. Некоторые из них быстрее цикла с запросами.

Неактивен

 

#2 10.09.2009 19:08:37

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Группировка в запросе

set @n=3, @i=0, @p=0;

select * from таблица_групп t1 join таблица_потомков t2 on t1.x=t2.y where @i<@n and if(@p=x, @i:=@i+1,(@i:=0) or (@p:=x));
 

Неактивен

 

#3 10.09.2009 19:16:16

smallhacker
Участник
Зарегистрирован: 10.09.2009
Сообщений: 9

Re: Группировка в запросе

Нифига себе!!! Кул. ТОлько я немного торможу ... за что отвечают три параметра ?
И где можно про это почитать это вообще из какой "оперы"?

Отредактированно smallhacker (10.09.2009 19:19:51)

Неактивен

 

#4 10.09.2009 19:26:52

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Группировка в запросе

n - количество потомков, которое вы хотите выбрать для каждой группы
i - это счетчик, обнуляется при смене текущей группы.
p - переменная, в которой записывается текущая группа

Про пользовательские переменные см
http://sqlinfo.ru/forum/viewtopic.php?id=363

Неактивен

 

#5 10.09.2009 19:39:04

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Группировка в запросе

IF - функция языка и может использоваться в запросах. Имеет 3 аргумента - условие, значение в случае истинности условия и значение в случае ложности условия.

SELECT IF(2>3,"Fantastic','Thats real');


В приведенном выше запросе, если текущая группа не изменилась @p=x, то увеличивается значение счетчика @i:=@i+1 и функция возвращает положительное число.
Если текущая группа изменилась, то обнуляется значение счетчика @i:=0 (данная операция возвращает 0) и в переменную @p записывается значение новой группы @p:=x (данная операция возвращает 1). Так как они связаны оператором OR, то результатом будет 1.

Таким образом IF(@p=x, @i:=@i+1,(@i:=0) OR (@p:=x)) всегда возвращает положительное число, т.е. не исключает строки из результирующей выборки, а выполняет вспомогательную функцию - увеличение счетчика и его обнуление при смене текущей группы.

Условие @i<@n обеспечивает выбор не более n (в данном случае 3) строк для каждой группы.

Неактивен

 

#6 10.09.2009 19:44:32

smallhacker
Участник
Зарегистрирован: 10.09.2009
Сообщений: 9

Re: Группировка в запросе

Огромное спасибо! Ни когда таким не пользовался. Надо будет по экспериментировать. А на скорость выполнения это сильно влияет ?

Неактивен

 

#7 10.09.2009 19:50:40

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Группировка в запросе

Тесты не проводил, но по логике явно быстрее чем цикл с запросами.

Неактивен

 

#8 10.09.2009 20:08:40

smallhacker
Участник
Зарегистрирован: 10.09.2009
Сообщений: 9

Re: Группировка в запросе

Спасибо еще раз. Отличный у Вас форум!

Неактивен

 

#9 10.09.2009 20:33:04

smallhacker
Участник
Зарегистрирован: 10.09.2009
Сообщений: 9

Re: Группировка в запросе

Не много переделал:

SET @n=4, @i=0, @p=0;

SELECT * FROM таблица_групп t1 JOIN таблица_потомков t2 ON t1.x=t2.y WHERE @i<=@n AND IF(@p=x, @i:=@i+1,(@i:=0) OR (@p:=x));

А то тот вариант выводил только по две записи почему-то sad

Неактивен

 

#10 23.09.2010 14:31:20

Герман Клюшин
Участник
Откуда: Севастополь
Зарегистрирован: 17.11.2008
Сообщений: 13

Re: Группировка в запросе

Правильно что по две - там опечатка - счетчик проверяется через "меньше чем" (<) а надо меньше или равно


Иисус - Бог неба и земли!

Неактивен

 

#11 18.11.2010 15:57:33

deadka
Администратор
Зарегистрирован: 14.11.2007
Сообщений: 2419

Re: Группировка в запросе

Возможно, не совсем понял, что является группой, а что потомком, поясните плиз.
Вот есть две таблицы - g (Группы) и p (Потомки).

DROP TABLE IF EXISTS `test`.`g`;
CREATE TABLE  `test`.`g` (
  `x` int(4) unsigned default NULL
) ENGINE=MyISAM;
insert into `test`.`g` values(3),(2),(4),(5),(1);

DROP TABLE IF EXISTS `test`.`p`;
CREATE TABLE  `test`.`p` (
  `y` int(4) unsigned default NULL,
  `data` int(4) unsigned default NULL
) ENGINE=MyISAM;
insert into `test`.`p` values
(3,172),(3,721),(3,652),(3,444),(3,781)
,(2,48),(2,27),(2,13),(2,16),(2,99)
,(4,4854),(4,2723),(4,1327),(4,1663),(4,9953)
,(5,48545),(5,27423),(5,12327),(5,16639),(5,99530)
,(1,4),(1,7),(1,3),(1,6),(1,9);

Выборка select * from g t1 join p t2 on t1.x=t2.y:

+------+------+-------+
| x    | y    | data  |
+------+------+-------+
|    3 |    3 |   172 |
|    3 |    3 |   721 |
|    3 |    3 |   652 |
|    3 |    3 |   444 |
|    3 |    3 |   781 |
|    2 |    2 |    48 |
|    2 |    2 |    27 |
|    2 |    2 |    13 |
|    2 |    2 |    16 |
|    2 |    2 |    99 |
|    4 |    4 |  4854 |
|    4 |    4 |  2723 |
|    4 |    4 |  1327 |
|    4 |    4 |  1663 |
|    4 |    4 |  9953 |
|    5 |    5 | 48545 |
|    5 |    5 | 27423 |
|    5 |    5 | 12327 |
|    5 |    5 | 16639 |
|    5 |    5 | 99530 |
|    1 |    1 |     4 |
|    1 |    1 |     7 |
|    1 |    1 |     3 |
|    1 |    1 |     6 |
|    1 |    1 |     9 |
+------+------+-------+
25 rows in set (0.00 sec)

То есть 3,2,4,5,1 - номера групп.

set @n=3, @i=0, @p=0;

Указанный Васей запрос

Даёт результат:

mysql> select * from g t1 join p t2 on t1.x=t2.y where @i<@n and if(@p=x, @i:=@i+1,(@i:=0) or (@p:=x));
+------+------+------+
| x    | y    | data |
+------+------+------+
|    3 |    3 |  172 |
|    3 |    3 |  721 |
|    3 |    3 |  652 |
|    3 |    3 |  444 |
+------+------+------+
4 rows in set (0.00 sec)

А по идее должен же вывести по три из каждой группы? Проясните плиз...


Зеленый свет для слабаков, долги отдают только трусы, тру гики работают только в консоли...

Неактивен

 

#12 18.11.2010 19:44:09

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Группировка в запросе

Нужно поменять порядок условий в части where (я просто писал по смыслу без проверки).
Если сначала идет @i<@n, то при невыполнении этого условия второе не проверяется (так как они идут через and). А нам нужно, чтобы наша конструкция с if выполнялась над каждой строкой.

Поэтому должно быть:
WHERE if(@p=x, @i:=@i+1,(@i:=0) or (@p:=x)) and @i<@n;

Неактивен

 

#13 26.11.2010 17:26:39

nexus
Завсегдатай
Зарегистрирован: 26.11.2010
Сообщений: 35

Re: Группировка в запросе

vasya написал:

WHERE if(@p=x, @i:=@i+1,(@i:=0) or (@p:=x)) and @i<@n;

Что-то я не понял! А здесь такое х? Поле по которому идет объединение таблиц или что?

Неактивен

 

#14 26.11.2010 18:12:00

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Группировка в запросе

Это имя колонки, в которой содержатся значения групп.
Посмотрите сообщение http://sqlinfo.ru/forum/viewtopic.php?pid=20266#p20266 там указаны команды для создания таблиц.

Неактивен

 

#15 26.11.2010 18:43:36

nexus
Завсегдатай
Зарегистрирован: 26.11.2010
Сообщений: 35

Re: Группировка в запросе

Скажите, а почему тогда у меня не работает следующий запрос
set @n=3, @i=0,@p=0;
SELECT * FROM site_teams_join_tournaments AS c1
JOIN site_teams AS  c2 ON `c1`.`team_id` = `c2`.`team_id`
LEFT JOIN `site_tournament_groups` AS c4 ON `c1`.`group_id` = `c4`.`group_id`
WHERE `c1`.`tournament_id` = 5 and if(@p = group_id, @i:=@i+1,(@i:=0) or (@p:=group_id)  and @i< @n

Неактивен

 

#16 26.11.2010 18:46:16

deadka
Администратор
Зарегистрирован: 14.11.2007
Сообщений: 2419

Re: Группировка в запросе

Приведите код создания таблиц. И уточните, что значит "не работает" - пустую выборку возвращает или ошибку? Если ошибку, то код и текст ошибки тоже приведите ).


Зеленый свет для слабаков, долги отдают только трусы, тру гики работают только в консоли...

Неактивен

 

#17 26.11.2010 18:54:05

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Группировка в запросе

Не знаю, телепатия не является моей сильной стороной smile

В чем выражается "не работает"? Какой клиент используете?

Неактивен

 

#18 26.11.2010 18:58:30

nexus
Завсегдатай
Зарегистрирован: 26.11.2010
Сообщений: 35

Re: Группировка в запросе

Я в phpMyAdmin выполняю этот запрос и выдает следующую ошибку:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 0, 30' at line 6

А вот код создания таблиц:

CREATE TABLE IF NOT EXISTS `site_teams_join_tournaments` (
  `id` int(11) NOT NULL auto_increment,
  `tournament_id` int(11) NOT NULL,
  `group_id` int(11) NOT NULL,
  `team_id` int(11) NOT NULL,
  `games` tinyint(3) unsigned NOT NULL,
  `wins` tinyint(3) unsigned NOT NULL,
  `draws` tinyint(3) unsigned NOT NULL,
  `loss` tinyint(3) unsigned NOT NULL,
  `yellow_cards` tinyint(3) unsigned NOT NULL,
  `red_cards` tinyint(3) unsigned NOT NULL,
  `goals_scored` tinyint(3) unsigned NOT NULL,
  `goals_against` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `tournament_id` (`tournament_id`),
  KEY `group_id` (`group_id`),
  KEY `team_id` (`team_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 ;

CREATE TABLE IF NOT EXISTS `site_teams` (
  `team_id` int(11) NOT NULL auto_increment,
  `country_id` int(11) NOT NULL,
  `league_id` int(11) NOT NULL,
  `cat_id` int(11) NOT NULL,
  `name` varchar(25) NOT NULL,
  `content` text NOT NULL,
  `achieve` text NOT NULL,
  `create_time` date NOT NULL,
  `stadium` varchar(50) NOT NULL,
  `capacity` int(11) NOT NULL,
  PRIMARY KEY  (`team_id`),
  KEY `country_id` (`country_id`),
  KEY `league_id` (`league_id`),
  KEY `cat_id` (`cat_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `site_tournament_groups` (
  `group_id` int(11) NOT NULL auto_increment,
  `tournament_id` int(11) NOT NULL,
  `group_name` varchar(20) NOT NULL,
  PRIMARY KEY  (`group_id`),
  KEY `tournament_id` (`tournament_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

Неактивен

 

#19 26.11.2010 19:05:13

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Группировка в запросе

В приведенном вами запросе никаких "LIMIT 0, 30" нет, так что какая синтаксическая ошибка рядом с "LIMIT 0, 30" я вам сказать не могу (см предыдущий ответ).

По существу: используйте родной клиент mysql и будет вам счастье.

Неактивен

 

#20 26.11.2010 19:11:18

nexus
Завсегдатай
Зарегистрирован: 26.11.2010
Сообщений: 35

Re: Группировка в запросе

vasya написал:

В приведенном вами запросе никаких "LIMIT 0, 30" нет, так что какая синтаксическая ошибка рядом с "LIMIT 0, 30" я вам сказать не могу (см предыдущий ответ).

"LIMIT 0, 30" это phpMyAdmin автоматически вставляет в конец запроса: Итого вот такой запрос:

SELECT *
FROM site_teams_join_tournaments AS c1
JOIN site_teams AS c2 ON `c1`.`team_id` = `c2`.`team_id`
LEFT JOIN `site_tournament_groups` AS c4 ON `c1`.`group_id` = `c4`.`group_id`
WHERE `c1`.`tournament_id` =5
AND if( @p = group_id, @i := @i +1, (
@i :=0
)
OR (
@p := group_id
)
AND @i < @n
LIMIT 0 , 30

Неактивен

 

#21 26.11.2010 19:13:55

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Группировка в запросе

Скобку закрывающую потеряли.

P.S. А пхпадмин всё равно лучше на помойку.

Неактивен

 

#22 30.11.2010 13:38:39

nexus
Завсегдатай
Зарегистрирован: 26.11.2010
Сообщений: 35

Re: Группировка в запросе

Точно, скобки не хватало! спасибо!

А не подскажите, как составить запрос, чтобы можно было вытащить всех команд определенного количества групп?!
Чуть выше я приводил пример структуры базы данных.

Идея в следующем. Имеется, например, 10 групп. В каждой группе по 4 команды. Так как одним запросом можно вытащить все команды, например, 2 групп?!

Неактивен

 

#23 30.11.2010 13:48:34

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Группировка в запросе

select * from .. where `идентификатор группы` in ('группа 1','группа 2');

Неактивен

 

#24 30.11.2010 14:58:30

nexus
Завсегдатай
Зарегистрирован: 26.11.2010
Сообщений: 35

Re: Группировка в запросе

ДА, но если я не знаю id групп?

Неактивен

 

#25 30.11.2010 15:02:25

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Группировка в запросе

Ну, на нет и суда нет smile

А как вы собираетесь определять какие группы выбрать или задача заключается в выборке произвольных двух групп?

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson