SQL-запрос на вывод повторяющихся строк таблицы
При работе с базами данных довольно часто возникает ситуация, когда нужно вывести повторяющиеся строки в таблице или повторяющиеся значения некоторых из полей строк.
Предположим, есть таблица TableName, содержащая некое поле DuplicateField. Нужно выбрать все строки, содержащие дублирующиеся значения поля DuplicateField и их количество. Т.е. выбрать те строки, в которых в поле записано одно и тоже значение более одного раза. Тогда запрос на выбор дублирующихся строк для поля DuplicateField может выглядеть так:
SELECT DuplicateField, count(DuplicateField) FROM TableName GROUP BY DuplicateField HAVING count(DuplicateField)>1;
Чтобы выбрать конкретные дублирующиеся элементы, содержащие только значение @SomeValue (параметр), можно добавить условие «WHERE»:
SELECT DuplicateField, count(DuplicateField) FROM TableName WHERE DuplicateField=@SomeValue GROUP BY DuplicateField HAVING count(DuplicateField)>1;
Если нужно провести выборку по нескольким повторяющимся полям, то sql-запрос может выглядеть так:
SELECT DuplicateField1, DuplicateField2 FROM TableName GROUP BY DuplicateField1, DuplicateField2 HAVING (count(DuplicateField2)>1 AND count(DuplicateField2)>1);
И, наконец, если нужно вывести полные строки, содержащие все поля (выбрать *), в которых дублируется одно поле, сделать это можно таким sql-запросом:
SELECT * FROM TableNeme WHERE DuplicateField IN ( SELECT DuplicateField, count(DuplicateField) FROM TableName GROUP BY DuplicateField HAVING count(DuplicateField)>1 );
Если нужно выбрать все поля и строки таблицы, в которых в разных строках повторяются комбинации значений двух и более полей, то sql-запрос будет немного сложнее. Для sql-сервера (Transact-SQL), с применением логического оператора Exists, запрос sql может быть таким :
SELECT * FROM TableName t1 WHERE EXISTS ( SELECT DuplicateField1, DuplicateField2 FROM TableName t2 WHERE t1.DuplicateField1=t2.DuplicateField1 AND t1.DuplicateField1 = t2.DuplicateField2 GROUP BY DuplicateField1, DuplicateField2 HAVING (count(DuplicateField2)>1 AND count(DuplicateField2)>1) )
Количество совпадающих записей с COUNT в MySQL
Избыточность данных возникает по множеству причин. Некоторые из сложных задач, с которыми вы должны справиться при работе с системами баз данных, — это попытка обнаружить повторяющиеся значения. Для этого мы будем использовать агрегатный метод COUNT(). Метод COUNT() возвращает сумму строк, находящихся в определенной таблице. Функция COUNT() позволяет суммировать все строки или только строки, соответствующие заданному условию. В этом руководстве вы узнаете, как определять повторяющиеся значения для одного или нескольких столбцов MySQL с помощью COUNT(). Метод COUNT() имеет три типа:
- COUNT(*)
- COUNT(expression)
- COUNT(DISTINCT expression)
Убедитесь, что в вашей системе установлен MySQL. Откройте клиентскую оболочку командной строки MySQL и введите свой пароль, чтобы продолжить. Мы рассмотрим несколько примеров подсчета совпадающих значений с помощью метода COUNT().
У нас есть таблица «social» в нашей схеме «data». Проверим его запись с помощью следующего запроса.
>> SELECT * FROM data.social;
COUNT(*) в MySQL
Метод COUNT(*) используется для подсчета количества строк, находящихся в таблице, или подсчета количества строк в соответствии с заданным условием. Чтобы проверить общее количество строк в таблице, «social» попробуйте запрос ниже.
>> SELECT COUNT(*) FROM data.social;
Взгляните на метод COUNT(*) при определении некоторых условий. Нам нужно получить количество строк, в которых имя пользователя совпадает с «AndreyEx».
>> SELECT COUNT(*) FROM data.social WHERE User = ‘AndreyEx’;
Чтобы получить общую сумму строк, в которых веб-сайт пользователя — «Instagram», попробуйте запрос, указанный ниже.
>> SELECT COUNT(*) FROM data.social WHERE Website = ‘Instagram’;
Чтобы получить общее количество строк, в которых «Возраст» больше 18, выполните следующие действия:
>> SELECT COUNT(*) FROM data.social WHERE Age > 18;
Давайте возьмем данные столбцов «User» и «Website» из таблицы, где имя пользователя начинается с буквы «M». Попробуйте приведенную ниже инструкцию по оболочке.
>> SELECT User, Website FROM data.social WHERE User like ‘M%’;
COUNT(выражение)
В MySQL метод COUNT (выражение) используется только тогда, когда вы хотите подсчитать ненулевые значения столбца «выражение». «Выражение» будет именем любого столбца. Давайте рассмотрим простой пример. Мы подсчитывали только ненулевые значения столбца «Веб-сайт», который связан со столбцом «Age», имеющим значение, равное «25».
>> SELECT COUNT(Website) FROM data.social WHERE Age = 25;
Читать В чем разница между статическим и динамическим SQL?
COUNT (DISTINCT выражение)
В MySQL метод COUNT(DISTINCT выражение) используется для суммирования ненулевых значений и различных значений столбца «выражение». Чтобы подсчитать определенное количество ненулевых значений в столбце «Age», мы использовали следующий запрос.
>> SELECT COUNT(DISTINCT Age) FROM data.social;
COUNT (if(выражение))
Для большего акцента вы должны объединить COUNT() с функциями управления потоком. Для начала, для части выражения, используемого в методе COUNT(), вы можете использовать функцию IF(). Это может быть очень полезно для быстрой разбивки информации в базе данных. Мы будем подсчитывать количество строк с разными возрастными условиями и разделять их на три разных столбца, которые можно назвать категориями. Во-первых, COUNT(IF) будет подсчитывать строки, возраст которых меньше 20, и сохранять это количество в новом столбце с именем «Teenage». Второй COUNT(IF) подсчитывает строки с возрастом от 20 до 30, сохраняя его в столбце «Young». В-третьих, последний подсчитывает строки старше 30 лет и сохраняет их в столбце «Mature».
>> SELECT COUNT(IF(Age < 20,1,NULL)) ‘Teenage’, COUNT(IF(Age BETWEEN 20 AND 30,1,NULL)) ‘Young’, COUNT(IF(Age >30,1,NULL)) ‘Mature’ FROM data.social;
COUNT(*) с предложением GROUP BY
Оператор GROUP BY — это инструкция SQL, которая используется для группировки строк с одинаковыми значениями. Он возвращает общее количество значений, находящихся в каждой группе. Например, если вы хотите проверить номер каждого пользователя отдельно, вы должны определить столбец «User» с помощью предложения GROUP BY, подсчитывая записи для каждого пользователя с помощью COUNT(*).
>> SELECT User, COUNT(*) FROM data.social GROUP BY User;
Вы можете выбрать более двух столбцов при выполнении подсчета строк вместе с предложением GROUP BY, как показано ниже.
>> SELECT User, Age, Website, COUNT(*) FROM data.social GROUP BY Website;
Если мы хотим подсчитать строки при использовании предложения WHERE с некоторыми условиями в нем наряду с GROUP BY и COUNT(*), вы также можете это сделать. Приведенный ниже запрос будет извлекать и подсчитывать записи столбцов: «User», «Website» и «Age», где значение веб-сайта — только «Instagram» и «Snapchat». Как видите, у нас есть только 1 запись для обоих веб-сайтов для разных пользователей.
>> SELECT User, Website, Age, COUNT(*) FROM data.social WHERE Website = ‘Instagram’ Or Website = ‘Snapchat’ GROUP BY Website, Age;
Читать Установка MySQL на Debian 9
COUNT(*) с предложением GROUP BY и ORDER BY
Давайте попробуем предложения GROUP BY и ORDER BY совместно с методом COUNT(). Давайте возьмем и посчитаем строки таблицы social, упорядочивая данные в порядке убывания с помощью этого запроса:
>> SELECT User, Website, Age, COUNT(*) FROM data.social GROUP BY Age ORDER BY COUNT(*) DESC;
Приведенный ниже запрос сначала подсчитает строки, а затем отобразит только записи, имеющие COUNT больше 2 в порядке возрастания.
>> SELECT User, Age, COUNT(*) FROM data.social GROUP BY Age HAVING COUNT(*) > 2 ORDER BY COUNT(*) ASC;
Вывод
Мы рассмотрели все возможные методы для подсчета совпадающих или повторяющихся записей, используя метод COUNT() с различными другими предложениями.
Если вы нашли ошибку, пожалуйста, выделите фрагмент текста и нажмите Ctrl+Enter.
Посчитать максимальное количество одинаковых значений подряд
Всем привет. Столкнулся с такой задачей: необходимо посчитать максимальное и минимальное количество одинаковых значений подряд. Есть столбец, в котором идут положительные и отрицательные значения. Нужно посчитать максимальное количество ПОДРЯД. Т.е. к примеру: 22, 33, 56, -85, -45, 68 — здесь получается 3 подряд положительных и 2 подряд отрицательных. Помогите составить запрос SQL
94731 / 64177 / 26122
Регистрация: 12.04.2006
Сообщений: 116,782
Ответы с готовыми решениями:
Найти максимальное количество одинаковых чисел, идущих подряд
Дан массив A(n). Найти максимальное количество одинаковых чисел, идущих подряд.
Определить максимальное количество одинаковых символов, идущих подряд
Ввести строку с клавиатуры. Определить максимальное количество одинаковых символов, идущих подряд.
Посчитать количество одинаковых подряд стоящих элементов
Здравствуйте! Есть такое задание. Дан массив. Найти количество одинаковых подряд стоящих.
Необходимо посчитать количество подряд идущих одинаковых элементов
есть массив, состоящий из 0 и 1 нужно посчитать наибольшее количество подряд идущих одинаковых.
Форум пользователей MySQL
Подскажите селект для выборки идентичных (повторяющихся либо похожих) значений в определенном столбике таблицы.
Для примера:
таблица — «table»
столбик — «column»
#2 20.12.2008 23:28:29
vasya Архат Откуда: Орел Зарегистрирован: 07.03.2007 Сообщений: 5818
Re: Запрос для выборки одинаковых значений
Не понятно, что значит похожих. Для выборки повторяющихся значений без повторов:
SELECT ` column ` FROM ` table ` GROUP BY ` column ` HAVING count ( * ) > 1 ;
Для выборки всех повторяющихся значений (с повторами):
SELECT ` column ` FROM ` table ` WHERE ` column ` LIKE (
SELECT ` column ` FROM ` table ` GROUP BY ` column ` HAVING count ( * ) > 1 ) ;
#3 20.12.2008 23:50:07
Fader Завсегдатай Зарегистрирован: 07.05.2008 Сообщений: 54
Re: Запрос для выборки одинаковых значений
Пытаюсь выполнить в phpmyadmin тот селект что «Для выборки всех повторяющихся значений (с повторами)» — получаю ошибку:
#1242 — Subquery returns more than 1 row
Под похожими подразумеваю фразы имеющие одинаковое начало или окончание или «корень»:
Firefox 3.0.5 Final
Firefox 3.0.5 Final Russian
Браузер Firefox 3.0.5
#4 21.12.2008 01:23:18
vasya Архат Откуда: Орел Зарегистрирован: 07.03.2007 Сообщений: 5818
Re: Запрос для выборки одинаковых значений
Замените LIKE на IN
SELECT ` COLUMN ` FROM ` table ` WHERE ` COLUMN ` IN ( SELECT ` COLUMN ` FROM ` table ` GROUP BY ` COLUMN ` HAVING count ( * ) > 1 ) ;
#5 20.07.2010 18:49:43
Fader Завсегдатай Зарегистрирован: 07.05.2008 Сообщений: 54
Re: Запрос для выборки одинаковых значений
Спасибо за запрос. В свое время он мне здорово помог.
Но сейчас проблема возникла снова:
За это время база существенно подросла и запрос выполняется неимоверно долго. (в таблице около 25к строк). Ждал около часа — надоело.
Пытался оптимизировать производительность mysql сервера, надеялся что запрос будет выполняться быстрей — ничего не вышло.
Подскажите как решить проблему?
#6 20.07.2010 19:30:21
paulus Администратор Зарегистрирован: 22.01.2007 Сообщений: 6756
Re: Запрос для выборки одинаковых значений
Хм. А вообще подзапрос нужен?
SELECT `colname` FROM `tablename` GROUP BY 1 HAVING COUNT(*) > 1;
Нужен ключик по (`colname`).
#7 20.07.2010 21:24:44
Fader Завсегдатай Зарегистрирован: 07.05.2008 Сообщений: 54
Re: Запрос для выборки одинаковых значений
Хм. работает! Спасибо!
Только что вы имели ввиду: «Нужен ключик по (`colname`)»?
#8 21.07.2010 13:38:41
paulus Администратор Зарегистрирован: 22.01.2007 Сообщений: 6756
Re: Запрос для выборки одинаковых значений
Слово «индекс» Вам больше нравится?
#9 28.07.2010 02:11:35
Fader Завсегдатай Зарегистрирован: 07.05.2008 Сообщений: 54
Re: Запрос для выборки одинаковых значений
возникла необходимость усложнить запрос.
К данному селекту нужно добавить условие:
среди повторяющихся значений выбрать нужно то, где дата в столбце ‘date’ (type=datetime) более поздняя
#10 28.07.2010 11:17:02
paulus Администратор Зарегистрирован: 22.01.2007 Сообщений: 6756
Re: Запрос для выборки одинаковых значений
SELECT `colname`, MAX(`date`) FROM `tablename` GROUP BY 1 HAVING COUNT(*) > 1;
#11 10.02.2011 18:52:13
nexus Завсегдатай Зарегистрирован: 26.11.2010 Сообщений: 35
Re: Запрос для выборки одинаковых значений
А как изменить запрос, если мне надо выбрать только те записи,у которых значение столбца COLUM повторяется у всех элементов с индексом (1-10).
Ну например, имеется таблица
CREATE TABLE IF NOT EXISTS `site_autos_params` (
`id` int ( 11 ) NOT NULL auto_increment ,
`auto_id` int ( 11 ) NOT NULL ,
`param_id` int ( 11 ) NOT NULL ,
`variant_id` int ( 11 ) NOT NULL ,
`value` varchar ( 255 ) NOT NULL ,
PRIMARY KEY ( `id` ) ,
KEY `auto_id` ( `auto_id` ) ,
KEY `param_id` ( `param_id` )
) ENGINE = MyISAM DEFAULT CHARSET =utf8 AUTO_INCREMENT = 14 ;
INSERT INTO `site_autos_params` ( `id`, `auto_id`, `param_id`, `variant_id`, `value` ) VALUES
( 1 , 6 , 25 , 0 , ‘Белый’ ) ,
( 2 , 5 , 25 , 0 , ‘Бронзовый’ ) ,
( 3 , 4 , 25 , 0 , ‘Белый’ ) ,
( 4 , 3 , 25 , 0 , ‘Белый’ ) ,
( 5 , 1 , 25 , 0 , ‘Белый’ ) ,
( 6 , 6 , 21 , 0 , ‘Бензин’ ) ,
( 7 , 5 , 21 , 0 , ‘Бензин/Газ’ ) ,
( 8 , 3 , 21 , 0 , ‘Бензин’ ) ,
( 9 , 7 , 21 , 0 , ‘Газ’ ) ,
( 10 , 7 , 25 , 0 , ‘Голубой’ ) ;
Мне нужно выбрать те записи у которых auto_id IN (‘4’, ‘5’, ‘6’, ‘7’) и которые имеют одинаковый param_id.
Т.е. нужно выбрать только те параметры, которые встречаются у всех автомобилей!
Пробывал делать так:
SELECT `auto_id`, `param_id`, `value`
FROM ( `site_autos_params` )
WHERE `param_id` IN ( SELECT `param_id` FROM site_autos_params GROUP BY `param_id` HAVING count ( * ) > 1 )
AND `auto_id` IN ( ‘4’ , ‘5’ , ‘6’ , ‘7’ )
Но этот запрос не правильно отрабатывает. Мне именно надо получить те параметры, которые есть у всех авто!