Подсчитать количество предметов принадлежащих юзеру

Question

Имею простую, но немного не до конца для меня понятную задачку по SQL.

Вкратце, у нас есть несколько таблиц, первая, accounts, где соответственно, хранится информация в виде "ключ-значение". Есть ячейки id, username, email.

Следующая таблица items, в которой хранится id предмета, его название и вес.

Третья таблица хранит информацию о связи между первыми двумя и содержит поля id предмета и id юзера.

Соответственно, конечная таблица должна содержать user_id, username, items, weight.

Выводить только если общая сумма веса больше 20.

Вопрос в том, как сделать подсчет предметов на основе их веса. То есть можно объявить переменную, которая будет равна:

SELECT COUNT(items.id) FROM items INNER JOIN accounts ON items.account_id

Но как подсчитать суммарный вес предметов?

Например таблица выглядит так:

account_id item_id
1             1
1             3
1             2
1             1

item_id weight
1          5
2          15
3          25

Как запрограммировать подсчёт с помощью языка SQL? Спасибо!

Answer 1

Например, так

select a.id user_id,max(username) username, sum(wt) weight
  ,group_concat(ItemName) Items
from accounts a
left join 
(   -- сколько весит Item и как называется
  select user_id,item_id,item_qty*Itemweight wt,ItemName
  from( -- сколько у user'а каких item
    select user_id,item_id,count(*) item_qty
    from UserItem
    group by user_id,item_id
  )uig
  left join items i on i.id=uig.item_id
) uigw on uigw.user_id=a.id
group by a.id
having sum(wt)>20

или так:

select a.id user_id,username,weight,items
from accounts a
inner join 
  (
  select user_id,sum(wt) weight,group_concat(ItemName) Items
  from(
    select user_id,item_id,item_qty*Itemweight wt,ItemName
    from(
      select user_id,item_id,count(*) item_qty
      from UserItem
      group by user_id,item_id
      )uig
    left join items i on i.id=uig.item_id
   ) uigw 
   group by user_id
   having sum(wt)>20
)uigws on uigws.user_id=a.id

Answer 2

Вот так можно сделать достаточно простым запросом, если вас устраивает, что список предметов будет строкой. По идее, для вашей задачи логичнее было бы сделать его массивом, но я не знаю, работает ли mysql с массивами. В постгрес например есть array_agg, который сделал бы массив из предметов.

Не совсем понятно, как дальше использовать это поле, но если надо, значит надо. Посмотрите мое решение на sqlfiddle.com Код вот такой:

select 
  ui.user_id
  , u.username
  , group_concat(i.id) as items
  , sum(i.weight) as weight
from accounts u
left join user_item ui
  on ui.user_id = u.id
left join items i
  on i.id = ui.item_id
group by ui.user_id, u.username
having sum(i.weight) > 20

p.s. перечитал вопрос и понял, что не до конца понятно, что должно лежать в поле items. В моем решении там лежит список предметов пользователя. Вам это и нужно? или нужно количество предметов?

Если нужно количество предметов, то просто замените group_concat на count

БЛОГ НА HUSL

Подсчитать количество предметов принадлежащих юзеру

Ответы (2 шт):