Как подсчитать различные значения, которые удовлетворяют условию в MySQL?
Я пытаюсь написать запрос, чтобы найти различные значения в определенном поле, подсчитать количество вхождений этого значения, где для всех экземпляров этого конкретного значения выполняется другое значение столбца, а затем отобразить результаты следующим образом ( более подробное объяснение):
Пример db:
RowId Status MemberIdentifier
----- ------ ----------------
1 In Progress 111111111
2 Complete 123456789
3 Not Started 146782452
4 Complete 111111111
5 Complete 123456789
6 Not Started 146782452
7 Complete 111111111
Желаемый результат:
Status MemberIdentifierCount
------ ----------------------
Not Started 1
In Progress 1
Complete 1
В приведенном выше запросе подсчитывается и отображается количество отдельных MemberIdentifiers с данным статусом. Если MemberIdentifier имеет две строки с статусом "Завершен", но с статусом "Выполняется", он сгруппирован и считается выполненным (то есть MemberIdentifier = 111111111). Для группировки и подсчета элемента MemberIdentifier все его строки должны иметь статус "Complete" (то есть MemberIdentifier = 123456789). Любое понимание будет оценено (новичок в MySQL).
Ответы
Ответ 1
Per MemberIdentifier находит статус, который вы считаете соответствующим, например. 'In Progress' побеждает над 'Complete' и 'Not Started'. 'Not Started' побеждает над 'Complete'. Для этого используйте условную агрегацию.
select status, count(*)
from
(
select
case when sum(status = 'In Progress') > 0 then 'In Progress'
when sum(status = 'Not Started') > 0 then 'Not Started'
else 'Complete'
end as status
from mytable
group by memberidentifier
) statuses
group by status;
Ответ 2
SELECT max_status AS Status
, COUNT(*) AS ct
FROM (
SELECT MAX(Status) AS max_status
FROM tbl
GROUP BY MemberIdentifier
) AS a
GROUP BY max_status;
Это использует то, как эти строки сравниваются: "In Progress" > "Complete". При этом он делает случайные вещи для любого другого члена с несколькими статусами.
Ответ 3
SQL
SELECT AdjustedStatus AS Status,
COUNT(*) AS MemberIdentifierCount
FROM
(SELECT IF(Status='Complete',
IF(EXISTS(SELECT Status
FROM tbl t2
WHERE t2.Status = 'In Progress'
AND t2.MemberIdentifier = t1.MemberIdentifier),
'In Progress',
'Complete'),
Status) AS AdjustedStatus,
MemberIdentifier
FROM tbl t1
GROUP BY AdjustedStatus, MemberIdentifier) subq
GROUP BY AdjustedStatus;
Онлайн-демонстрация
http://rextester.com/FFGM6300
Объяснение
Первая функция IF() проверяет, является ли статус "завершен" , и если да, проверяет наличие другой записи с тем же MemberIdentifier, но со статусом "Выполняется" : это делается через IF(EXISTS(SELECT...))). Если найдено, статус "Выполняется" присваивается полю AdjustedStatus, в противном случае значение AdjustedStatus устанавливается из (нескорректированного) значения Status.
Если скорректированный статус был получен таким образом для каждой из строк таблицы, GROUP BY AdjustedStatus и MemberIdentifier, чтобы получить все уникальные комбинации этих двух значений поля. Затем это делается в подзапрос - псевдоним как subq. Затем запишите (GROUP BY) AdjustedStatus и подсчитайте количество вхождений, т.е. Количество уникальных MemberIdentifier для каждого.
Ответ 4
Я предполагаю, что у вас есть 2 таблицы ниже
CREATE TABLE table1 (RowId INT PRIMARY KEY, MemberIdentifier VARCHAR(255));
INSERT INTO table1 (RowId, MemberIdentifier)
VALUES
(1,'111111111'), (2, '123456789'), (3, '146782452'), (4, '111111111'),(5,'123456789'), (6,'146782452'), (7,'111111111');
CREATE TABLE table2 (RowId INT PRIMARY KEY, Status VARCHAR(255));
INSERT INTO table2 (RowId, Status)
VALUES
(1,'In Progress'), (2,'Complete' ), (3,'Not Started'), (4,'Complete' ), (5,'Complete' ), (6,'Not Started'), (7,'Complete' );
Предполагая, что в этих таблицах нет миллионов записей, вы можете использовать запрос ниже для достижения того, чего хотите.
SELECT CASE WHEN not_started.Status = 'Not Started'
THEN 'Not Started'
WHEN in_progress.Status = 'In Progress'
THEN 'In Progress'
WHEN complete.Status = 'Complete'
THEN 'Complete'
END AS over_all_status,
COUNT(*) AS MemberIdentifierCount
FROM (SELECT DISTINCT t1.MemberIdentifier
FROM table1 t1) main
LEFT OUTER JOIN
(SELECT DISTINCT t1.MemberIdentifier, t2.Status
FROM table1 t1,
table2 t2
WHERE t1.RowId = t2.RowId
AND t2.Status = 'In Progress') in_progress
ON (main.MemberIdentifier = in_progress.MemberIdentifier)
LEFT OUTER JOIN
(SELECT DISTINCT t1.MemberIdentifier, t2.Status
FROM table1 t1,
table2 t2
WHERE t1.RowId = t2.RowId
AND t2.Status = 'Not Started') not_started
ON (main.MemberIdentifier = not_started.MemberIdentifier)
LEFT OUTER JOIN
(SELECT DISTINCT t1.MemberIdentifier, t2.Status
FROM table1 t1,
table2 t2
WHERE t1.RowId = t2.RowId
AND t2.Status = 'Complete') complete
ON (main.MemberIdentifier = complete.MemberIdentifier)
GROUP BY over_all_status;
В основном запрос создает одну запись на членский идентификатор, содержащий все три состояния. Затем он группирует результат на основе общего состояния и выводит счетчик.
Результат запроса
![введите описание изображения здесь]()
Ответ 5
используйте следующий код, чтобы получить статус MemberIdentifier
select MemberIdentifier
,case
when total = cn then 'Complete'
when total < cn then 'In Progress'
when total is null then 'Not Started' END as Fstatus
from
(
select sum(stat) total,MemberIdentifier,(select count(MemberIdentifier) as cnt from tbldata t1
where t1.MemberIdentifier = C.MemberIdentifier
group by MemberIdentifier) as cn
from (
select MemberIdentifier,case status when 'In Progress' then -1
when 'Complete' Then 1
when 'Not Started' then null End as Stat from tbldata
) C
group by MemberIdentifier
) as f1
используйте следующий код, чтобы получить счетчик MemberIdentifiers в определенном состоянии.
Select count(fstatus) counts,fstatus from (
select MemberIdentifier
,case when total = cn then 'Complete'
when total < cn then 'In Progress'
when total is null then 'Not Started' END as Fstatus
from
(
select sum(stat) total,MemberIdentifier,(select count(MemberIdentifier) as cnt from tbldata t1
where t1.MemberIdentifier = C.MemberIdentifier
group by MemberIdentifier) as cn
from (
select MemberIdentifier
,case status when 'In Progress' then -1 when 'Complete' Then 1 when 'Not Started' then null End as Stat from tbldata
) C
group by MemberIdentifier
) as f1
) f2 group by fstatus
вывод:
counts fstatus
1 Complete
1 In Progress
1 Not Started
Ответ 6
Если порядок приоритета для status равен
Not Started
In Progress
Complete
Мы можем использовать ярлык...
SELECT t.memberIdentifier
, MAX(t.status) AS status
FROM mytable t
GROUP BY t.MemberIdentifier
Это дает нам отличный memberIdentifier.
Если для члена, имеющего строки в 'In Progress' и 'Complete', есть строки, запрос возвращает 'In Progress' как статус.
Мы получим статус 'Complete', возвращенный для члена, только если этот член не имеет строк со статусом больше 'Complete'.
Чтобы получить подсчеты от этого результата, мы можем ссылаться на этот запрос как встроенное представление:
SELECT q.status
, COUNT(q.memberIdentifier)
FROM (
SELECT t.memberIdentifier
, MAX(t.status) AS status
FROM mytable t
GROUP BY t.MemberIdentifier
) q
ORDER BY q.status
Подумайте, если так... MySQL запускает запрос между сначала parens (MySQL называет это "производной таблицей". Результаты запроса представляют собой набор строк, которые могут запрашиваться как таблица.
Мы могли бы сделать COUNT(DISTINCT q.memberIdentifier) или, предположив, что членIdentifier гарантированно будет не-NULL, мы могли бы сделать COUNT(1) или SUM(1) и получить эквивалентный результат. (GROUP BY в строчном представлении гарантирует нам, что memberIdentifier будет уникальным.)
В более общем случае, когда у нас нет удобного ярлыка алфавитного упорядочения для приоритета состояния... мы могли бы использовать выражение, которое возвращает значения, которые "находятся в порядке". Это делает запрос немного сложнее, но он будет работать одинаково.
Мы могли бы заменить t.status на что-то вроде этого:
CASE t.status
WHEN 'Complete' THEN 1
WHEN 'In Progress' THEN 2
WHEN 'Not Started' THEN 3
ELSE 4
END AS `status_priority`
И замените q.status на что-то обратное, чтобы преобразовать обратно в строки:
CASE q.status_priority
WHEN 1 THEN 'Complete'
WHEN 2 THEN 'In Progress'
WHEN 3 THEN 'Not Started'
ELSE NULL
END AS `status`
Нам нужно решить, как мы будем обрабатывать значения статуса, которые не являются одним из трех... те, которые будут игнорироваться, обрабатываются как более высокий или более низкий приоритет, чем любой другой. (В тестовом случае будут строки с status = 'Unknown' и строки с status = 'Abracadabra.
Ответ 7
Я только что изменил решение @thorsten-kettner, поскольку вы столкнулись с проблемой при соединении таблицы. Я предположил, что у вас две таблицы, таблица1 - у которых есть как минимум две строки (RowID и MemberIdentifier) и таблица2 - которая содержит не менее двух строк (RowID и Status)
select Status, count(*)
from(
select
case when sum(newTable.Status = 'In Progress') > 0 then 'In Progress'
when sum(newTable.Status = 'Not Started') > 0 then 'Not Started'
else 'Complete'
end as status
from (
select table1.RowId as RowId, table1.MemberIdentifier as MemberIdentifier, table2.Status as Status from table1 INNER JOIN table2 ON table1.RowId = table2.RowId
)newTable
group by newTable.MemberIdentifier
) statuses
group by Status;
Ответ 8
Другой способ использовать определенную таблицу для настройки порядка (сопоставить Power с целым числом).
Это сопоставление позволяет агрегату bit_or просто переносить данные.
http://rextester.com/edit/ZSG98543
-- Table bit_progression to determine priority
CREATE TABLE bit_progression (bit_status int PRIMARY KEY, Status VARCHAR(255));
INSERT INTO bit_progression (bit_status, Status)
VALUES
(1, 'Not Started'),
(2, 'Complete' ),
(4, 'In Progress');
select
Status,
count(*)
from
(
select
MemberIdentifier,max(bit_status) bit_status
from
tbl natural join bit_progression
group by
MemberIdentifier
) Maxi natural join bit_progression
group by
Status
;
произвести
Status count(*)
1 Complete 1
2 In Progress 1
3 Not Started 1
Дополнительно:
select
MemberIdentifier,
bit_or(bit_status) bits_status,
case when bit_or(bit_status) & 4 = 4 then true end as withStatusInProgress,
case when bit_or(bit_status) & 2 = 2 then true end as withStatusComplete,
case when bit_or(bit_status) & 1 = 1 then true end as withStatusNotStarted
from
tbl natural join bit_progression
group by
MemberIdentifier
;
произведите его:
MemberIdentifier bits_status withStatusInProgress withStatusComplete withStatusNotStarted
111111111 6 1 1 NULL
123456789 2 NULL 1 NULL
146782452 1 NULL NULL 1
