如何从sql查询中获得每种类型的“分数”列表？

Question

我试图获得一个返回“加权分数”的表，该表带有我为以下模式的每种类型设计的公式：

收藏夹：userID,drinkName

SELECT (
( SELECT COUNT(*)
  FROM   Drink D JOIN History  H ON H.drinkName = D.name
    WHERE  type = 'vodka' AND userID = 'sai') 
+ 
(((SELECT COUNT(*)
    FROM   Drink D JOIN Favorite F ON F.drinkName = D.name
    WHERE  type = 'vodka' AND userID = 'sai'))*0.666))
 /  
((SELECT COUNT(*) FROM History  WHERE userID = 'sai') 
+ 
(SELECT COUNT(*) FROM Favorite WHERE userID = 'sai'))

然而，这只返回特定类型的饮料，而不是每种类型的饮料的表。

Answer 1

使用group by以及聚合函数来获取每个类型的合计。

SELECT type, SUM(numerator)/SUM(denominator) score
FROM (
    SELECT type, COUNT(*) numerator, COUNT(*) denominator
    FROM History
    WHERE userID = "sai"
    GROUP BY type
    UNION ALL
    SELECT type, (COUNT(*) * 0.666) numerator, COUNT(*) denominator
    FROM Favorites
    WHERE userID = "sai"
    GROUP BY type
) u
GROUP BY type