MySQL根据用户的工作位置获得平衡总和 [英] MySQL getting SUM of balance grouped by user's job location

问题描述

我试图在特定月份获得所有用户余额的总和，并根据用户的地区进行分组，这取决于他们工作的销售点。

余额

• id_balance


• 日期


• id_user


• 值（$$$）

用户 姓名（不相关）

pos p>

• id_pos


• id_region


• 相关）

location_region

• id_region


• 名称（佛罗里达州，德克萨斯州等）
  

<基本上，我需要它来呈现这些数据（按月过滤）：

  location_region.name | SUM（balance.value）
 --------------------- | ------------------ -  
佛罗里达| 45730 
德克萨斯州| 43995 
  

我试过几种方法，但都没有运气。这是我最接近的尝试。

  SELECT location_region.name，SUM（balance.value）AS money 
 FROM balance 
 LEFT JOIN用户ON user.id_user 
 LEFT JOIN pos ON pos.id_pos = user.id_pos 
 LEFT JOIN location_region ON location_region.id_region = pos.id_region 
 WHERE balance.date BETWEEN' 2014-02-01'AND DATE_ADD（'2014-02-01'，INTERVAL 1 MONTH）
 GROUP BY location_region.id_region 
 ORDER BY money DESC 
  

有什么想法？您当前的查询有逻辑错误，余额和用户表之间的JOIN条件不完整（缺少 balance.id_user ）。因此，而不是余额LEFT JOIN用户ON user.id_user 您应该有余额LEFT JOIN user ON user.id_user = balance.id_user 。这会导致JOINed表具有更多行（用户表中的平衡时间行数）。所以最后的SUM带来了一个更高的价值。

我对你的示例数据（我改变了一些值）进行了以下查询，它似乎工作正常：

  SELECT location_region.name，SUM（balance.value）AS money 
 FROM balance 
 LEFT JOIN user USING（id_user）
 LEFT JOIN pos USING（id_pos）
 LEFT JOIN location_region USING（id_region）
 WHERE balance.date BETWEEN'2014-02-01'AND DATE_ADD（'2014-02- 01'，INTERVAL 1个月）
 GROUP BY location_region.id_region 
 ORDER BY money DESC 
  

工作演示： http://sqlfiddle.com/#!2/dda28/3

Im trying to get the SUM of all user balances in a specific month, and grouped by the user's region, which depends on the Point of Sell they work at.

balance

• id_balance
• date
• id_user
• value ($$$)

user

• id_user
• id_pos
• name (not relevant)

pos (Point of Sell)

• id_pos
• id_region
• name (not relevant)

location_region

• id_region
• name (Florida, Texas, etc)

Basically, I would need it to present this data (filtered by month):

location_region.name | SUM(balance.value)
---------------------|-------------------
Florida              | 45730
Texas                | 43995

I've tried a few approaches with no luck. This was my closest attempt.

SELECT location_region.name, SUM(balance.value) AS money
FROM balance
LEFT JOIN user ON user.id_user
LEFT JOIN pos ON pos.id_pos = user.id_pos
LEFT JOIN location_region ON location_region.id_region = pos.id_region
WHERE balance.date BETWEEN '2014-02-01' AND DATE_ADD('2014-02-01', INTERVAL 1 MONTH)
GROUP BY location_region.id_region
ORDER BY money DESC

Any ideas? Thanks!

解决方案

Your current query has a logical error, JOIN condition between balance and user tables is incomplete (missing balance.id_user). So instead of balance LEFT JOIN user ON user.id_user you should have balance LEFT JOIN user ON user.id_user=balance.id_user. This is causing the JOINed table to have more rows (number of rows in balance times number of rows in user table). So the final SUM is bringing a way too higher value.

I tried the following query on your sample data (I changed some values) and it seems to be working fine:

SELECT location_region.name, SUM(balance.value) AS money
FROM balance
LEFT JOIN user USING(id_user)
LEFT JOIN pos USING(id_pos)
LEFT JOIN location_region USING(id_region)
WHERE balance.date BETWEEN '2014-02-01' AND DATE_ADD('2014-02-01', INTERVAL 1 MONTH)
GROUP BY location_region.id_region
ORDER BY money DESC

Working demo: http://sqlfiddle.com/#!2/dda28/3

这篇关于MySQL根据用户的工作位置获得平衡总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！