我正试图计算购买的数量和所有符合给定搜索条件的项目的“向上”和“向下”投票。不幸的是,正如我现在设置的查询一样,购买和投票数乘以一个神秘的系数22,我不知道它是从哪里来的。
举一个例子:购买、上涨和下跌的票数应该分别为7、2和1,而不是154、44和22。
下面是我的SQL代码:
SELECT *
sum(purchaseyesno) as tots,
sum(rating=1) as yes,
sum(rating=0) as no
from items
join items_purchased
on items_purchased.item_id=items.item_id
join accounts
on items.account_id=accounts.account_id
like subject='%album by joe%' or description='%album by joe%'
group by item_id
order by tots desc, yes desc
以下是一些示例数据:
subject tots yes no full_name
album by joe 154 44 22 joe smith
album by fred 88 44 0 fred jones
以下是我希望数据的外观:
subject tots yes no full_name
album by joe 7 2 1 joe smith
album by fred 4 2 0 fred jones
有人能帮我弄清楚这是怎么回事吗?尽管改变了
group by
和其他东西,我还是无法找出22个问题的这个因素(这意味着,这个22个数字独立于返回行的#)。 最佳答案
不显示架构,但使用子查询可能会有帮助:
SELECT subject, tots, yes, no, fullnane
FROM (
SELECT item_id, SUM(purchaseyesno) AS tots, SUM(rating=1) AS yes, SUM(rating=0) AS no
FROM items_purchased
GROUP BY item_id
) i
JOIN items ON i.item_id = items.item_id
JOIN accounts ON items.account_id=accounts.account_id
WHERE subject LIKE '%album by joe%' OR description LIKE '%album by joe%'
ORDER BY tots DESC, yes DESC
关于mysql - MYSQL的商品购买总和及其“上”/“下”票是否乘以22?,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/9967807/