仅返回一行！但对所有用户的预期输出 [英] Only one row returns! but expected output for all user

问题描述

我有三顿饭，费用，用户！ 我想从这些表中计算出每个用户的总费用，总费用！在一个查询中！ 我在计算总费用时遇到问题！

i have three table meals,expense,users! i want to calculate every user total expense ,total cost from these tables !! in a single query! i faced problem in calculation total cost !!

totalcost = meal rate *每位用户的总餐食

totalcost=meal rate* total meal per user

(用餐费=所有用户费用之和/所有用户膳食之和)！

(meal rate= sum of all user expense / sum of all users meal)!!

当我朗读下面的查询时，它仅返回一行！我已经尝试了group by，但是它给了我错误的输出结果:(有人可以帮我吗?

when i rum my below query it returns only one row !! i have tried group by but it gives me wrong output :( Can anyone help me??

 {
    SELECT u.name , 
           ((SUM(tot_expenseamount)/ SUM(tot_noofmeal))*tot_noofmeal) as total_Cost,
           IFNULL(t.tot_expenseamount,0) AS tot_expense_amount ,
           IFNULL(n.tot_noofmeal,0) AS tot_no_of_meal 
    FROM users u
    LEFT JOIN (
        SELECT e.user_id , SUM(e.expenseamount) AS tot_expenseamount 
        FROM expenses e 
        WHERE e.expensedate >= '2019-04-01' + INTERVAL 0 MONTH AND e.expensedate < '2019-04-01' + INTERVAL 1 MONTH
        GROUP BY e.user_id ) t ON t.user_id = u.id
    LEFT JOIN (
        SELECT m.user_id , SUM(m.noofmeal) AS tot_noofmeal 
        FROM meals m 
        WHERE m.mealdate >= '2019-04-01' + INTERVAL 0 MONTH AND m.mealdate < '2019-04-01' + INTERVAL 1 MONTH
        GROUP BY m.user_id ) n ON n.user_id = u.id 
    ORDER BY u.name

膳食表

费用表

用户表

预期输出

推荐答案

我为您重写了它.

如果您使用的是MySQL 8.0.2 +:

If you're using MySQL 8.0.2+:

SELECT u.name 
     , (expense.TotalExpenseAmount / SUM(MealCount) OVER ()) * MealsPerUser.MealCount
  FROM users u
 CROSS 
  JOIN (SELECT SUM(e.expenseamount) AS TotalExpenseAmount 
          FROM expenses e 
         WHERE e.expensedate >= '2019-04-01' + INTERVAL 0 MONTH AND e.expensedate < '2019-04-01' + INTERVAL 1 MONTH
     ) expense
  LEFT 
  JOIN (SELECT m.user_id, SUM(m.noofmeal) AS MealCount 
          FROM meals m 
         WHERE m.mealdate >= '2019-04-01' + INTERVAL 0 MONTH AND m.mealdate < '2019-04-01' + INTERVAL 1 MONTH
         GROUP BY m.user_id
     ) MealsPerUser
    ON MealsPerUser.user_id = u.id 
 ORDER BY u.name

如果没有，它将变得更加复杂:

If not, it becomes even more convoluted:

SELECT u.name 
     , (expense.TotalExpenseAmount / meals.TotalMealCount) * MealsPerUser.MealCount
  FROM users u
 CROSS 
  JOIN (SELECT SUM(e.expenseamount) AS TotalExpenseAmount 
          FROM expenses e 
         WHERE e.expensedate >= '2019-04-01' + INTERVAL 0 MONTH AND e.expensedate < '2019-04-01' + INTERVAL 1 MONTH
     ) expense
 CROSS
  JOIN (SELECT SUM(m.noofmeal) AS TotalMealCount
          FROM meals m 
         WHERE m.mealdate >= '2019-04-01' + INTERVAL 0 MONTH AND m.mealdate < '2019-04-01' + INTERVAL 1 MONTH
     ) meals
  LEFT 
  JOIN (SELECT m.user_id, SUM(m.noofmeal) AS MealCount 
          FROM meals m 
         WHERE m.mealdate >= '2019-04-01' + INTERVAL 0 MONTH AND m.mealdate < '2019-04-01' + INTERVAL 1 MONTH
         GROUP BY m.user_id
     ) MealsPerUser
    ON MealsPerUser.user_id = u.id 
 ORDER BY u.name

这篇关于仅返回一行！但对所有用户的预期输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！