Строки подсчета SQL с одинаковым значением в столбце и группировка по идентификатору?
у меня есть такая таблица:
id | value
1 | 1
2 | 1
3 | 2
4 | 3
интересно, можно ли подсчитать строки с одинаковым значением и сгруппировать их по идентификатору, но каждый раз, когда код будет возвращать счет 1, если я сгруппирую их по идентификатору, а не по значению
желаемый результат:
id | count
1 | 2(there are 2 rows with value 1)
2 | 2
3 | 1
4 | 1
мне не нужно знать значение только идентификатор и количество строк с одинаковым значением
Ответы 5
В MySQL 8+ вы должны использовать оконные функции:
select t.*, count(*) over (partition by value) as cnt
from t;
Спасибо за ваш ответ, но, к сожалению, я использую mysql 5.5.
Я думаю, то, что вы хотите, не имеет смысла. Как я вижу, столбец id является автоматически увеличивающимся целым числом, поэтому его нельзя сгруппировать по идентификатору и сгруппировать более одного значения. Если вам нужна таблица, которая сохраняет число в одном столбце и количество раз, которое это число появляется в другом столбце, вам нужно будет выполнить это:
SELECT value AS id, COUNT(value) AS count FROM table GROUP BY value ORDER BY value ASC;
Вам нужно подсчитать значение столбца value для каждого идентификатора:
select
t.id,
(select count(*) from tablename where value = t.value) count
from tablename t
Смотрите демо
или:
select t.id, g.count
from tablename t inner join (
select value, count(value) count
from tablename
group by value
) g on g.value = t.value
См. демо
В версиях MySQL без оконных функций вы можете добиться желаемых результатов с помощью самостоятельного соединения value, подсчитав количество значений во второй таблице:
SELECT t1.id, COUNT(t2.value) AS cnt
FROM table1 t1
JOIN table1 t2 ON t2.value = t1.value
GROUP BY t1.id
Вывод:
id cnt
1 2
2 2
3 1
4 1
Если вам действительно нужны результаты, указанные в вашем вопросе, я предлагаю следующее:
declare @Orig table (ID int, val int)
insert into @Orig values (1, 1),
(2, 1),
(3, 2),
(4, 3)
--select * from @Orig --show example table
declare @Stat table (val int, cnt int)
insert into @Stat
select val as ID, count(val) as count from @Orig group by val order by val asc
--select * from @Stat --L. Ribo's query results from group/order query
select o.ID, s.cnt from @Orig o
inner join @Stat s on o.val = s.val
Результаты:
ID cnt
1 2
2 2
3 1
4 1
Соедините таблицу статистики с исходной таблицей. Конечно, вероятно, есть какой-то элегантный способ сделать это без временных таблиц, но мне проще решить и понять, выполнив это пошагово. Это было сделано в SQL Server. Временные таблицы в других вариантах SQL (например, MySQL) имеют другой синтаксис.
Таким образом, вам не нужно знать, какое значение имеет какой счет