mysql - 如何从多个查询中进行乘法和除法？

Question

现在我有 3 个疑问:

我们将此查询称为 X

SELECT count(*) as totalDrink from 
Drink D join History H where H.drinkName = D.name AND type = 'vodka' and userID = 'sai' 

查询Y

SELECT count(*) as totalDrink from 
Drink D join Favorite F where F.drinkName = D.name AND type = 'vodka' and userID = 'sai' 

查询Z

SELECT SUM(totalDrinks) as total FROM (
SELECT COUNT(*) totalDrinks FROM History
WHERE userID = 'sai'
UNION ALL
SELECT COUNT(*) FROM Favorite
WHERE userID = 'sai'
) as totalDrink

基本上我想在 mySQL 中执行并输出:

(X + Y(0.666))/Z

Answer 1

最直接:

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' 
  )
) / (
  (
    SELECT COUNT(*) FROM History  WHERE userID = 'sai'
  ) + (
    SELECT COUNT(*) FROM Favorite WHERE userID = 'sai'
  )
)