У меня эти 3 таблицы: пользователи, заказы и order_item.
Когда пользователь может иметь заказ, а позиция заказа может быть только событием, только членством или обоими, и поэтому 2 строки будут записаны в order_items
таблицы пользователей
id | user_id | name | phone
---|-----------|-------|------
1 | 123456789 | Jon | 555-55555
2 | 123456780 | Alice | 555-6666
таблицы заказов
id | user_id | user_uid | user_info
---|---------|-----------|----------
1 | 1 | 123456789 | bla
2 | 2 | 123456780 | foo
3 | 2 | 123456780 | foo
таблица order_items
id | order_id | order_type | price
--- | -------- | ---------- | ------
1 | 1 | membership | 70
2 | 1 | event | 200
3 | 2 | event | 300
4 | 3 | membership | 70
Связь такова,
order_items.order_id -> orders.id
orders.user_id -> users.id
orders.user_uid -> users.user_id
Я ищу запрос, который будет производить этот тип вывода,
user_id | name | count_membership | count_events | total_orders
-------- | ------ | ------------------ | -------------- | --------------
123456789 | Jon | 1 | 1 | 1
123456780 | Alice | 1 | 1 | 2
Мне нравится подсчитывать общие заказы, которые есть у пользователя, и подсчитывать, сколько из них у него есть. в конце мне нравится отфильтровывать всех пользователей, где count_membership = 0
Заранее спасибо,
Вы можете получить ожидаемый результат, используя некоторую условную агрегацию
select u.user_id,
u.name,
sum(oi.membership_count) membership_count,
sum(oi.event_count) event_count,
count(o.id) total_orders
from users u
join orders o on u.id = o.user_id
join (
select order_id,
sum(order_type = 'membership') membership_count,
sum(order_type = 'event') event_count
from order_items
group by order_id
) oi on o.id = oi.order_id
group by u.user_id,u.name
Ваш запрос должен объединить три таблицы в их общих полях, которые будут user_id и order_id.
Поскольку вы сказали, что не хотите никаких результатов, когда членство не является типом заказа, ваш пример результатов не имеет смысла, и группировка в отдельные результаты таким образом, используя один запрос mysql, усложняет задачу order_type, который является единственным полем в две отдельные колонки. Поэтому я немного поправился.
select users.user_id, users.name, order_type, count(order_id) as total_orders from orders, order_items, users where users.user_id = orders.user_id and orders.id = order_items.order_id and order_type = "membership" group by users.user_id, users.name
рабочий вывод из терминала mysql:
mysql> select users.user_id, users.name, order_type, count(order_id) as total_orders from orders, order_items, users where users.user_id = orders.user_id and orders.id = order_items.order_id and order_type = "membership" group by users.user_id, users.name; +---------+------+------------+--------------+ | user_id | name | order_type | total_orders | +---------+------+------------+--------------+ | 123 | Jon | membership | 1 | | 1234 | Pam | membership | 2 | +---------+------+------------+--------------+