SQLinfo.ru - Все о MySQL

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

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

Вы не зашли.

#1 24.09.2017 14:49:55

AlexJN
Участник
Зарегистрирован: 24.09.2017
Сообщений: 10

SELECT по обязательному совпадению значений в нескольких строках

Есть таблица
CREATE TABLE IF NOT EXISTS `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `p_1` int(11) NOT NULL,
  `p_2` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0 ;

INSERT INTO `test` (`name`, `p_1`, `p_2`) VALUES
( 'A', 1, 3),( 'A', 2, 4),( 'A', 3, 1),('B', 1, 4),( 'B', 4, 4),( 'B', 3, 1),( 'C', 1, 3),( 'C', 2, 4),( 'C', 3, 1);

Необходимо произвести выборку только тех строк   с именем "name"      у которых  обязательно имеются:
  p_1=1 и   p_2=3   а также обязазательно  имеются: p_1=2  и   p_2=4
В данном примере это должны быть  строки  id= 1,2,7,8

Неактивен

 

#2 25.09.2017 10:26:40

klow
Старожил
Зарегистрирован: 06.12.2014
Сообщений: 411

Re: SELECT по обязательному совпадению значений в нескольких строках

Или я не понял или задача решается элементарно

SELECT * FROM test WHERE p_1=1 AND p_2=3 OR p_1=2 AND p_2=4

Отредактированно klow (25.09.2017 10:27:18)

Неактивен

 

#3 25.09.2017 11:25:53

AlexJN
Участник
Зарегистрирован: 24.09.2017
Сообщений: 10

Re: SELECT по обязательному совпадению значений в нескольких строках

Прошу прощения
Была допущена ошибка в sql импорта таблицы test
правильный вариант будет такой
CREATE TABLE IF NOT EXISTS `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `p_1` int(11) NOT NULL,
  `p_2` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0 ;

INSERT INTO `test` (`name`, `p_1`, `p_2`) VALUES
( 'A', 1, 3),( 'A', 2, 4),( 'A', 3, 1),('B', 1, 4),( 'B', 1, 3),( 'B', 3, 1),( 'C', 1, 3),( 'C', 2, 4),( 'C', 3, 1);

По предложенному выше варианту запроса выбираются строки в кторых id=1,2,4,7,8
А надо что бы были выбраны только  id=1,2,7,8

Неактивен

 

#4 25.09.2017 11:42:01

klow
Старожил
Зарегистрирован: 06.12.2014
Сообщений: 411

Re: SELECT по обязательному совпадению значений в нескольких строках

Проверьте еще раз, 4 значение ('B', 1, 4) не может выбираться условием

p_1=1 AND p_2=3 OR p_1=2 AND p_2=4

Неактивен

 

#5 25.09.2017 11:51:27

AlexJN
Участник
Зарегистрирован: 24.09.2017
Сообщений: 10

Re: SELECT по обязательному совпадению значений в нескольких строках

Пардон ещё раз. По запросу  выбираются строки id=1,2,5,7,8
А повторяюсь надо только id=1,2,7,8

Неактивен

 

#6 25.09.2017 11:55:59

klow
Старожил
Зарегистрирован: 06.12.2014
Сообщений: 411

Re: SELECT по обязательному совпадению значений в нескольких строках

А почему не должна выбираться 5 строка? Согласно условиям "у которых  обязательно имеются:
  p_1=1 и   p_2=3 " - должна.

Неактивен

 

#7 25.09.2017 11:56:37

klow
Старожил
Зарегистрирован: 06.12.2014
Сообщений: 411

Re: SELECT по обязательному совпадению значений в нескольких строках

Возможно есть еще какие-то условия не озвученные?

Неактивен

 

#8 25.09.2017 12:34:29

AlexJN
Участник
Зарегистрирован: 24.09.2017
Сообщений: 10

Re: SELECT по обязательному совпадению значений в нескольких строках

Из строк содержащих значения p_1=1 , p_2=3 а также  p_1=2 , p_2=4 выбираются только те, в которых для каждой группы параметров  ( p_1=1 AND  p_2=3)  OR  ( p_1=2 AND p_2=4) имеется присутвие обеих групп для определённого значения `name`
То есть  применительно к таблице `test` :
Проверяем - имеется для `name` = A значения p_1=1 , p_2=3 а также  p_1=2 , p_2=4 то обе строки выбираются
То же самое имеем  для сторок  `name` = С
Для строк  `name` = B выборка не происходит так как  нет присутствия обеих групп параметров в строках где `name` = B

Неактивен

 

#9 25.09.2017 13:20:45

AlexJN
Участник
Зарегистрирован: 24.09.2017
Сообщений: 10

Re: SELECT по обязательному совпадению значений в нескольких строках

Результат приближённый к нужному даёт следующий запрос.
SELECT t1.* , t2.p_1 AS t2_p1 , t2.p_2 AS t2_p2     FROM `test` AS t1
LEFT JOIN test AS t2 on  t1.name =t2.name 
WHERE  t1.p_1=1 AND t1.p_2=3 AND t2.p_1=2 AND t2.p_2=4 AND   t2.p_1 is NOT NULL
Но это не совсем то что требуется. В результате запроса выводится две строки,
а нодо что было   четыре строки  из исходной таблицы

Неактивен

 

#10 25.09.2017 13:32:27

klow
Старожил
Зарегистрирован: 06.12.2014
Сообщений: 411

Re: SELECT по обязательному совпадению значений в нескольких строках

SELECT * FROM test t1 WHERE t1.p_1=1 and t1.p_2=3
AND EXISTS(SELECT * FROM test t2 WHERE t1.Name = t2.name AND t2.p_1=2 AND t2.p_2=4)

Неактивен

 

#11 25.09.2017 13:51:41

AlexJN
Участник
Зарегистрирован: 24.09.2017
Сообщений: 10

Re: SELECT по обязательному совпадению значений в нескольких строках

Спасибо за ответ но к сожалениию этот запрос также как и мой выводит две строки , а не четыре  которые содержатся в исходной таблице.     При этом теряется возможность  использования встроенными в движок средствами  постраничной навигации. (Если конечно её  не модернизировать добавив какойнибудь костыль чего очень не хочется )

Неактивен

 

#12 25.09.2017 14:34:25

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

Re: SELECT по обязательному совпадению значений в нескольких строках

select * from test where name in (
select name from test where ( p_1=1 AND  p_2=3)  OR  ( p_1=2 AND p_2=4) group by name having count(*)=2)
and ( p_1=1 AND  p_2=3)  OR  ( p_1=2 AND p_2=4);
+----+------+-----+-----+
| id | name | p_1 | p_2 |
+----+------+-----+-----+
|  1 | A    |   1 |   3 |
|  2 | A    |   2 |   4 |
|  7 | C    |   1 |   3 |
|  8 | C    |   2 |   4 |
+----+------+-----+-----+
 

Неактивен

 

#13 25.09.2017 14:45:56

AlexJN
Участник
Зарегистрирован: 24.09.2017
Сообщений: 10

Re: SELECT по обязательному совпадению значений в нескольких строках

Вынужден ещё раз попросить прощения, так как был некорректно  составлен пример таблицы test
Последний вариант запроса не работает для следующей таблицы


CREATE TABLE IF NOT EXISTS `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `p_1` int(11) NOT NULL,
  `p_2` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0 ;

INSERT INTO `test` (`name`, `p_1`, `p_2`) VALUES
( 'A', 1, 3),( 'A', 2, 4),( 'A', 3, 1),('B', 1, 3),( 'B', 1, 3),( 'B', 3, 1),( 'C', 1, 3),( 'C', 2, 4),( 'C', 3, 1);

 

Неактивен

 

#14 25.09.2017 14:53:28

AlexJN
Участник
Зарегистрирован: 24.09.2017
Сообщений: 10

Re: SELECT по обязательному совпадению значений в нескольких строках

Необходимо также добавить что в реальной  задаче выборка ведётся по трём параметрам

Неактивен

 

#15 25.09.2017 15:17:10

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

Re: SELECT по обязательному совпадению значений в нескольких строках

select * from test where name in (
select name from
(select distinct name, p_1, p_2 from test where ( p_1=1 AND  p_2=3)  OR  ( p_1=2 AND p_2=4) ) x
where ( p_1=1 AND  p_2=3)  OR  ( p_1=2 AND p_2=4) group by name having count(*)=2
)
and (( p_1=1 AND  p_2=3)  OR  ( p_1=2 AND p_2=4));
+----+------+-----+-----+
| id | name | p_1 | p_2 |
+----+------+-----+-----+
|  1 | A    |   1 |   3 |
|  2 | A    |   2 |   4 |
|  7 | C    |   1 |   3 |
|  8 | C    |   2 |   4 |
+----+------+-----+-----+

Неактивен

 

#16 25.09.2017 15:18:23

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

Re: SELECT по обязательному совпадению значений в нескольких строках

AlexJN написал:

Необходимо также добавить что в реальной  задаче выборка ведётся по трём параметрам

добавьте доп условия, только count(*)= не забудьте изменить

Неактивен

 

#17 25.09.2017 15:29:21

AlexJN
Участник
Зарегистрирован: 24.09.2017
Сообщений: 10

Re: SELECT по обязательному совпадению значений в нескольких строках

Спасибо за помощь. Мне необходимо разобраться с вашим запросом, как он работает , добавить параметры и оценить нагрузку.
Хотя проблем по идее не должно быть, таблица содержит 40 000 записей.

Неактивен

 

#18 25.09.2017 17:02:07

AlexJN
Участник
Зарегистрирован: 24.09.2017
Сообщений: 10

Re: SELECT по обязательному совпадению значений в нескольких строках

Следующий вариант оказался наиболее оптимальным по производительности и пониманию работы


SELECT t1.*     FROM `test` AS t1
LEFT JOIN test AS t2 on  t1.name =t2.name
WHERE (t1.p_1=1 AND t1.p_2=3 AND  t2.p_1=2 AND t2.p_2=4 )
OR (t1.p_1=2 AND t1.p_2=4 AND  t2.p_1=1 AND t2.p_2=3 )

 

Неактивен

 

Board footer

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