Запрос, чтобы узнать, сколько раз элемент появляется
У меня есть таблица, как это в моей БД:
id --- owner --- product ---- type
0 --- john --- mustang ---- car
1 --- tim --- a360 ---- plane
2 --- john --- camry ---- car
3 --- dan --- a380 ---- plane
4 --- tim --- ninja ---- bike
5 --- dan --- accord ---- car
Я пытаюсь получить номер каждого типа, который есть у владельца. Что-то вроде этого:
John
Car = 2
Plane = 0
Bike = 0
-------------
Tim
Car = 0
Plane = 1
Bike = 1
-------------
Dan
Car = 1
Plane = 1
Bike = 0
-------------
Я не смог решить это.
Другая проблема заключается в том, что моя база данных может принимать новые типы. Например, кто-то может добавить велосипед в качестве типа.
Есть какой-либо способ сделать это?
Решение
Чтобы решить эту проблему, сначала вы должны сгенерировать выходные строки, а затем получить количество:
select o.owner, p.product, count(t.owner)
from (select distinct owner from table) o cross join
(select distinct product from table) p left join
table t
on t.owner = o.owner and t.product = p.product
group by o.owner, p.product;
Если у вас есть справочные таблицы для владельцев и продуктов, то вы можете использовать их вместо select distinct подзапросы.
РЕДАКТИРОВАТЬ:
Группировка по типу — это в основном та же идея:
select o.owner, ty.type, count(t.owner)
from (select distinct owner from table) o cross join
(select distinct type from table) ty left join
table t
on t.owner = o.owner and t.type = ty.type
group by o.owner, ty.type;
Другие решения
То, как я подошел к этому, было немного сложно.
Я начал с создания одного результирующего набора, который использовал декартово произведение, чтобы получить по одной строке для каждого человека и комбинации типов.
SELECT m.owner, t.type
FROM myTypes t, myTable m
GROUP BY m.owner, t.type
Затем я сделал еще один набор результатов, который захватил владельца, тип и номер каждого типа для этого владельца. Однако это вернуло только строки для существующих комбинаций типа владелец. Он не будет возвращать никаких значений, скажем, «Джон» и «Плоскость», потому что у него нет произведения плоскости.
SELECT m.owner, t.type, COUNT(*) as numOfType
FROM myTypes t
JOIN myTable m ON t.type = m.type
GROUP BY t.type, m.owner;
Наконец, я соединил эти две таблицы вместе, используя внешнее соединение, поэтому я получил каждую строку из таблицы комбинаций типа владелец. Конечно, некоторые строки возвращали null для счетчика, поэтому мне пришлось использовать IFNULL заменить их на 0. Это соответствует результирующему набору в вашем вопросе.
SELECT w1.owner, w1.type, IFNULL(w2.numOfType, 0) AS numOfType
FROM (SELECT m.owner, t.type
FROM myTypes t, myTable m
GROUP BY m.owner, t.type) w1
LEFT JOIN (SELECT m.owner, t.type, COUNT(*) as numOfType
FROM myTypes t
JOIN myTable m ON t.type = m.type
GROUP BY t.type, m.owner) w2
ON w1.owner = w2.owner AND w1.type = w2.type;
Здесь SQL Fiddle.