where 子句中的条件子查询 [英] Conditional subquery in where clause

查看:140
本文介绍了where 子句中的条件子查询的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我正在尝试使用条件逻辑创建一个查询,其中我仅使用日期时间列 (start_date) 计算每个月最近记录的收入,但前提是该月有多个来自同一 account_id 的记录.

I'm trying to create a query with conditional logic where I only calculate revenue for the most recent records by each month using a datetime column (start_date), but only if there are multiple records in that month from the same account_id.

这是我加入两个表后的基本架构示例(sqlfiddle 链接中的完整架构).

Here's a basic example of the schema after I join two tables (full schema in sqlfiddle link).

| account_id | plan_id | start_date           | plan_interval | price |
|------------|---------|----------------------|---------------|-------|
| 1          | 1       | 2018-01-03T14:52:13Z | month         | 39    |
| 1          | 3       | 2018-02-07T11:10:17Z | year          | 999   |
| 1          | 2       | 2018-02-07T11:11:17Z | month         | 99    |

在上面的示例中,我只想在输出中包含第 1 行和第 3 行,因为它是 1 月份 account_id 1 中的一条记录,以及 2 月份 account_id 1 两条记录中的最新记录.

In the above example, I would only like to include rows 1 and 3 in my output, as it's the one record from account_id 1 in January and the most recent of two records for account_id 1 in February.

SELECT
MONTH(start_date) AS month,
SUM(CASE WHEN plan_interval = 'month' 
     THEN price * .01 
     ELSE (price * .01)/12 END) AS mrr
FROM subscriptions
  JOIN plans
  ON plans.id = subscriptions.plan_id
WHERE Year(start_date) = 2018 AND 
  CASE WHEN (account_id = account_id
             AND MONTH(start_date) = MONTH(start_date))
             THEN (SELECT MAX(start_date) FROM subscriptions)
             ELSE (SELECT start_date FROM subscriptions)
             END
GROUP BY month
ORDER BY month ASC;

上面子查询中的 case 语句似乎无法做到这一点.当满足第一个条件时,它返回数据而不过滤掉记录.

The case statement in the subquery above does not seem to work in doing this. It returns the data without filtering out records when the first condition is met.

这是一个例子:sqlfiddle

推荐答案

此查询返回您在问题中要求的行:

This query returns the rows that you are asking for in the question:

SELECT s.*, p.plan_interval, p.price,
       (CASE WHEN p.plan_interval = 'month' 
             THEN p.price * 0.01 
             ELSE (p.price * 0.01)/12
        END) AS mrr
FROM subscriptions s JOIN
     plans p
     ON p.id = s.plan_id
WHERE YEAR(s.start_date) = 2018 AND 
      s.start_date = (SELECT MAX(s2.start_date)
                      FROM subscriptions s2
                      WHERE s2.account_id = s.account_id AND
                            EXTRACT(YEAR_MONTH FROM s2.start_date) = EXTRACT(YEAR_MONTH FROM s.start_date)
                     )
ORDER BY s.start_date ASC;

这使用子查询来获取每个月订阅的最新记录.

This uses a subquery to get the most recent record for a subscription for each month.

然后,您可以随意聚合.

You can then aggregate this however you wish.

关于查询的说明:

  • 表别名使查询更易于编写和阅读.
  • 子查询使用了 EXTRACT() 的方便的 YEAR_MONTH 选项,因此它可以处理年份和月份.
  • 对于 -1 和 1 之间的数字常量,我总是在前面加上 0,所以 0.12 而不是 .12.如果发现这样会使小数点更明显.
  • Table aliases make the query easier to write and to read.
  • The subquery uses the handy YEAR_MONTH option of EXTRACT(), so it handles both years and months.
  • For numeric constants between -1 and 1, I always prepend with a 0, so 0.12 rather than .12. If find that this makes the decimal point more obvious.

这篇关于where 子句中的条件子查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆