mysql - 按多列分组并求和

Question

我在计算以下 mysql 查询时遇到了一些问题。

这是一种时间跟踪，用户根据他们完成的任务有一定的费率。而且我需要有一个总结，这样我才能知道在周末向用户支付什么费用。

-----------tSessions---------------------------
|id      |userid  |typeid  |session_length_min
-----------------------------------------------
|1       |1       |1       |30
-----------------------------------------------
|2       |1       |1       |45
-----------------------------------------------
|3       |1       |2       |(null)
-----------------------------------------------
|4       |2       |2       |(null)
-----------------------------------------------


-----------tUsers-----------------------------------------------------------------------------------
|id      |name  |rate_cleaning_30_min   |rate_cleaning_45_min   |rate_kitchenwork  |rate_dogwalking
----------------------------------------------------------------------------------------------------
|1       |Tom   |30                     |50                     |40                |20
----------------------------------------------------------------------------------------------------
|2       |Joe   |35                     |60                     |45                |20
----------------------------------------------------------------------------------------------------
|3       |Dave  |40                     |60                     |30                |10
----------------------------------------------------------------------------------------------------


-----------tTypes----------------
|id      |name
---------------------------------
|1       |Cleaning
---------------------------------
|2       |Kitchenwork
---------------------------------
|3       |Dogwalking
---------------------------------


============== Required Result ====================
|username   |sessioncount   |amount_to_pay
---------------------------------------------------
|Tom        |2              |120
---------------------------------------------------
|Joe        |1              |45
---------------------------------------------------

下面的查询是我不太成功的方法之一:

SELECT
tSessions.id,
tSessions.userid,
tSessions.typeid,
tSessions.session_length_min,
SUM(tUsers.rate_cleaning_30_min) AS rate_cleaning_30_min_sum,
SUM(tUsers.rate_cleaning_45_min) AS rate_cleaning_45_min_sum,
SUM(tUsers.rate_kitchenwork) AS rate_kitchenwork,
SUM(tUsers.rate_dogwalking) AS rate_dogwalking,
Count(*) AS sessioncount,
FROM
tSessions
INNER JOIN tUsers ON tSessions.userid = tUsers.id
WHERE WEEKOFYEAR(FROM_UNIXTIME(datetime))=WEEKOFYEAR(NOW())-1
GROUP BY
tSessions.userid,
tSessions.typeid

Answer 1

这是一个SQLFiddle example :

select tUsers.Name,
count(*) as sessioncount,
sum(
if(typeid=1,
          if(session_length_min<=30,rate_cleaning_30_min,
            rate_cleaning_45_min) 
          ,0)
+
  if(typeid=2,rate_kitchenwork,0)
+
 if(typeid=3,rate_dogwalking,0)
) as amount_to_pay

from
tSessions
left join tUsers on tSessions.userid=tUsers.id
group by tUsers.Name

但是你的基础方案不好。如果您需要添加或删除任务类型怎么办。您应该将 tUsers 和任务的价格分开。只需添加新表 tRates 并修改 tTypes:

    -----------tTypes----------------
    |id      |name
    ---------------------------------
    |1       |Cleaning 30 min
    ---------------------------------
    |2       |Kitchenwork
    ---------------------------------
    |3       |Dogwalking
    ---------------------------------
    |4       |Cleaning 45 min
    ---------------------------------

-----------tSessions--------
|id      |userid  |typeid  |
----------------------------
|1       |1       |1       |
----------------------------
|2       |1       |4       |
----------------------------
|3       |1       |2       |
----------------------------
|4       |2       |2       |
----------------------------


-----------tUsers-----------------
|id      |name  |
-----------------
|1       |Tom   |
-----------------
|2       |Joe   |
-----------------
|3       |Dave  |
-----------------


and add tRates table with USER<->TASKS rates:


-----------tRates--------
|id      |userid  |typeid  | rate   |
---------------------------------------
|1       |1       |1       |30      |
--------------------------------------
|2       |1       |2       |40      |
--------------------------------------
|3       |1       |3       |20      |
--------------------------------------
|4       |1       |4       |50      |
--------------------------------------
|5       |2       |1       |35      |
--------------------------------------
|6       |2       |2       |45      |
--------------------------------------
|7       |2       |3       |20      |
--------------------------------------
|8       |2       |4       |60      |
--------------------------------------
|9       |3       |1       |40      |
--------------------------------------
|10      |3       |2       |30      |
--------------------------------------
|11      |3       |3       |10      |
--------------------------------------
|12      |3       |4       |60      |
--------------------------------------