在子查询中使用LIMIT简化查询，在子查询和外部查询中重复WHERE子句 [英] Simplifying a query with a LIMIT in a subquery and WHERE clauses duplicated in the subquery and outer query

问题描述

SQL小提琴

假设以下表格：

客户

+----+------------------------+------------+------------+
| id | company                | first_name | last_name  |
+----+------------------------+------------+------------+
|  1 | MDD                    | T          | H          |
|  2 | Aliance Magnet A LLP   | A          | Wilkinson  |
|  3 | MAF                    | C          | G          |
|  4 | QL                     | F          | B          |
|  5 | ARL                    | S          | P          |
|  6 | Q Corp.                | H          | H          |
|  7 | VQDA                   | L          | W          |
|  8 | AESC                   | E          | W          |
|  9 | Placement Incorporated | C          | Mendez     |
| 10 | Numpties United        | Y          | Cunningham |
+----+------------------------+------------+------------+

交易

+----+-----------+-------------+------+
| id | form_type | customer_id | due  |
+----+-----------+-------------+------+
|  1 | invoice   |           9 |    1 |
|  2 | payment   |           1 |    6 |
|  3 | invoice   |           7 |    9 |
|  4 | payment   |           9 |    4 |
|  5 | invoice   |           7 |    5 |
|  6 | payment   |           3 |    5 |
|  7 | invoice   |           9 |    5 |
|  8 | invoice   |           9 |   10 |
|  9 | invoice   |          10 |    1 |
| 10 | invoice   |           2 |    4 |
+----+-----------+-------------+------+

以下查询查找发票交易> 0的客户，但仅返回与前三个客户相关的记录。

The following query finds the customers with invoice transactions > 0, but only returns the records related to the first three customers.

SELECT
  t.id AS trans_id,
  c.id AS customer_id,
  c.company,
  c.first_name,
  c.last_name,
  t.due
FROM (
  SELECT DISTINCT c.*
  FROM customers AS c
  INNER JOIN transactions AS t ON t.customer_id = c.id
  WHERE t.due > 0
  AND t.form_type = 'invoice'
  ORDER BY c.company, c.first_name, c.last_name
  LIMIT 3
) AS c
INNER JOIN transactions AS t ON t.customer_id = c.id
WHERE t.due > 0
AND t.form_type = 'invoice'
ORDER BY c.company, c.first_name, c.last_name;

结果

+----------+-------------+------------------------+------------+------------+------+
| trans_id | customer_id | company                | first_name | last_name  | due  |
+----------+-------------+------------------------+------------+------------+------+
|       10 |           2 | Aliance Magnet A LLP   | A          | Wilkinson  |    4 |
|        9 |          10 | Numpties United        | Y          | Cunningham |    1 |
|        1 |           9 | Placement Incorporated | C          | Mendez     |    1 |
|        7 |           9 | Placement Incorporated | C          | Mendez     |    5 |
|        8 |           9 | Placement Incorporated | C          | Mendez     |   10 |
+----------+-------------+------------------------+------------+------------+------+

有没有办法，例如使用窗口函数或通用表表达式，以避免在 WHERE t.due>中重复0 AND t.form_type =发票 以及内部查询和外部查询中的 ORDER BY 子句？还是通过单个SQL查询获得相同结果的其他方法？

Is there a way, e.g. using window functions or common table expressions that will avoid duplicating WHERE t.due > 0 AND t.form_type = 'invoice' and the ORDER BY clause in the inner and outer queries? Or some other way to get the same results with a single SQL query?

推荐答案

您可以使用 DENSE_RANK ：

WITH cte AS (
  SELECT t.id AS trans_id,
         c.id AS customer_id,
         c.company,
         c.first_name,
         c.last_name,
         t.due,
         DENSE_RANK() OVER(ORDER BY c.company, c.first_name, c.last_name) rn
  FROM customers AS c
  JOIN transactions AS t ON t.customer_id = c.id
  WHERE t.due > 0 AND t.form_type = 'invoice'
)
SELECT * FROM cte WHERE rn <= 3;

DBFiddle演示

DBFiddle Demo

这篇关于在子查询中使用LIMIT简化查询，在子查询和外部查询中重复WHERE子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！