MySQL 用条件求和多列值 [英] MySQL Sum multiple column values with conditions

问题描述

我有以下架构(两个表):

I have the following schema (two tables):

**APPS**

   | ID (bigint)  | USERID (Bigint) | USAGE_START_TIME (datetime)    | 
    ------------------------------------------------------------------
   |  1           |        12       |         2013-05-03 04:42:55    |
   |  2           |        12       |         2013-05-12 06:22:45    |
   |  3           |        12       |         2013-06-12 08:44:24    |
   |  4           |        12       |         2013-06-24 04:20:56    |
   |  5           |        13       |         2013-06-26 08:20:26    |
   |  6           |        13       |         2013-09-12 05:48:27    |


**USAGE** 

   | ID (bigint)  | APPID (bigint) |   DEVICEID (bigint)  | HIGH_COUNT (bigint) |  MEDIUM_COUNT (bigint)  |
    --------------------------------------------------------------------------------------------------------
   |  1           |        1       |                  2    |       400           |                   200   |
   |  2           |        1       |                  3    |       200           |                   100   |
   |  3           |        2       |                  3    |       350           |                    40   |
   |  4           |        3       |                  4    |         2           |                   400   |
   |  5           |        4       |                  2    |         4           |                    30   |
   |  6           |        5       |                  3    |        50           |                   300   |

说明:

所以，有两张桌子.现在我想找到以下内容:

So, there are two tables. Now I want to find the following:

给定一个 USERID，获​​取 HIGH_COUNT & 的总和MEDIUM_COUNT 个.计数时SUM 应注意: 如果在 USAGE 中，使用相同的设备不止一次，然后是具有最新信息的记录(基于APPS.USAGE_START_TIME)，在计算总和.

Given a USERID, Get sum of HIGH_COUNT & MEDIUM_COUNT. While counting the SUM it should be taken care that: If in USAGE, same device is used more than once, then the record which has the latest info (based on APPS.USAGE_START_TIME), should be considered while calculating the sum.

例如:

对于上述模式，结果应该是(对于 userid=12):

For above schema, result should be (for userid=12) :

   | HIGH_COUNT (bigint)  | MEDIUM_COUNT (Bigint) |
    -----------------------------------------------
   |                356   |                   470 |

SQL 小提琴:http://sqlfiddle.com/#!2/74ae0f>

推荐答案

如果用户在一台设备上使用多个 APPS，此查询将使用 APPS 行最高usage_start_time:

If a user uses multiple APPS on one device, this query will use the APPS row with the highest usage_start_time:

select  a.userid
,       sum(u.high_count)
,       sum(u.medium_count)
from    apps a
join    `usage` u
on      u.appid = a.id
join    (
        select  u.device_id
        ,       a.userid
        ,       max(a.usage_start_time) as max_start_time
        from    apps a
        join    `usage` u
        on      u.appid = a.id
        group by
                u.device_id
        ,       a.userid
        ) filter
on      filter.device_id = u.device_id
        and filter.userid = a.userid
        and filter.max_start_time = a.usage_start_time
group by
        a.userid

在您的数据集中，它将为用户 12 选择用法行 5, 3, 4.

In your dataset, it will select usage rows 5, 3, 4 for user 12.

查看它在 SQL Fiddle 中的运行情况.

这篇关于MySQL 用条件求和多列值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！