我本周为工作编写的奇葩 SQL 查询

我喜欢SQL。在我看来，它是最强大、最优美的语言之一。它已经存在很长时间了，但我仍然在不断学习新知识。讽刺的是，我在大学里上的 SQL 课是我上过的唯一一门有用的计算机科学课程，而且我在那里学到的知识至今仍然有用。

简而言之，到底出了什么问题？呃……我不能透露具体细节，所以我编一个比较接近事实的故事吧。

前提

假设你正在编写一个任务管理器，并且刚刚往你的某个项目中添加了 1000 个任务。1000 个任务数量庞大且难以管理，所以你决定将它们分成 100 个一组。编写一个连接数据库的 Node 脚本似乎很简单……但是等等，这意味着你需要以某种方式连接到数据库。你不想创建一个新项目，将代码提交到现有代码库感觉不太好，而且你的 UI 也无法同时处理 1000 个任务。

但是……但是 PgAdmin 就在那里，已经连接到数据库了。我可以用 SQL 做这件事吗？你问自己，因为现在是下午 4 点，你想回家，而且 SQL 是你最喜欢的语言，所以你决定用 SQL 来做。

注意：在现实世界中，有 100,000 个“任务”，所以，是的，我无法想象任何非分页 UI 能够像轻而易举一样处理 100,000 个任务。

还有！这是我和另一位工程师合作完成的，所以当我说“我不得不写”的时候，实际上是我和我的同事一起完成的 :) 谢谢兄弟！

最终查询

DO $FN$
DECLARE
  project_id integer
BEGIN
  FOR counter IN 1..10 LOOP
    RAISE NOTICE 'Counter: %', counter;

    EXECUTE $$ INSERT INTO projects(name) VALUES ('Group ' || $1) RETURNING id $$ 
      INTO project_id 
      USING counter;

    EXECUTE $$ UPDATE tasks 
      SET project_id = $1
      WHERE tasks.id IN (
          SELECT tasks.id 
          FROM tasks
          INNER JOIN projects ON projects.id = tasks.project_id
          WHERE projects.name = $2
          ORDER BY tasks.id ASC
          LIMIT 100) $$ 
     USING project_id, 'Client project';

     RAISE NOTICE 'Finished Counter: %', counter;
  END LOOP;
END;$FN$

解释

我不想逐行讲解查询语句，但我很想解释代码中的各个“块”，以便更好地理解，并让您了解 SQL 的工作原理。

请注意，其中很多内容是postgres 特有的，这意味着 MySQL、MSSQL 或其他 SQL 数据库可能无法访问此语法的某些部分。

街区DO $FN$​

DO $FN$
 -- code goes here
$FN$

本质上，这和其他“do”代码块一样。“do”代码块的文档将其比作一个返回void且不接受任何参数的自执行匿名函数。

这$FN$就是所谓的美元符号字符串常量。在 Postgres 中，有几种类型的引号：

1. 单引号 ( 'string goes here') 表示内容为字符串
2. 双引号 ( "column_name") 允许我们引用有问题的列名或表名，从而安全地使用它。
3. 美元符号字符串（$$ SELECT * FROM projects WHERE name = 'test'; $$）与单引号字符串类似，区别在于你可以在其中使用其他类型的引号。

如果想在多个引用中嵌套多个引用呢？你可以使用转义符\，但 Postgres 非常强大，它允许你在引用符内指定自定义标签，并根据需要嵌套任意层级：

$outer_quote$
  $inner_quote$
    $$ SELECT * FROM projects where name = 'test' $$
  $inner_quote$
$outer_quote$

你无需在任何地方声明这些自定义标签，它们就能正常工作。只需在美元符号之间插入一个单词即可。

声明变量

下一个代码块（位于我们以美元报价的自定义标签字符串中）是声明代码块：

-- dollar quote start
DECLARE
  project_id integer;
-- rest of the code

这样我们就可以声明要在函数其余部分使用的变量。是的，你必须预先声明它们，并且要声明类型。我找不到关于这条语句的具体文档（DECLARE在代码块之外DO使用语句与游标有关，但这与我们这里讨论的内容无关）。

功能体

DECLARE
  -- variables
BEGIN
  -- more code
END;

本质上，到目前为止的代码都是在为运行一些代码做准备。我们通过代码块告诉 Postgres 我们想要执行某些操作DO，然后通过声明变量来声明我们想要使用的变量DECLARE。

所有中间环节BEGIN以及END运行常规 SQL 语句的操作。

for 循环

FOR counter IN 1..10 LOOP
  -- more code
END LOOP;

是的，SQL 中也可以使用 for 循环！:)相关的循环和控制文档中有详细的说明，其中就列出了这个例子！

这counter是一个会被更新的变量，我们可以在循环内部访问它。它1..10创建了一个范围（循环 1 到 10）。

注意：经验丰富的 Postgres 和 SQL 老手会告诉我，进行 1 到 10 的计算是不必要的，因为……而且你猜怎么着……你可以1..10用 SQL 查询来代替，创建一个“for each”类型的循环。例如，FOR task IN SELECT * FROM tasks LOOP它会遍历数据库中的每个任务。

发出通知！

RAISE NOTICE 'Counter: %', counter;

关于这一点，简单说明一下，这是一个日志。就是这样。这样做RAISE NOTICE不会抛出任何错误。它的工作方式类似于格式化字符串，%字符串后面的内容会被替换成变量。就像其他语言一样，你可以传入多个%符号，然后在字符串末尾传入多个变量进行替换。

没必要，我只是喜欢看日志而已 :)

执行...到...使用（创建一个新项目）

EXECUTE $$ INSERT INTO projects(name) VALUES ('Group ' || $1) RETURNING id $$ 
      INTO project_id 
      USING counter;

该语句将使用该counter变量创建一个全新的项目，项目名称Group后跟计数器编号，并将生成的项目 ID 存储在该project_id变量中。

为了避免让人感到不知所措，我会把这句话解释得更详细一些。起初我也觉得这句话难以理解，事实上，最初的查询语句写得非常粗糙，就是为了避免EXECUTE完全理解这句话的意思。

该EXECUTE语句会执行给定的任何字符串。我们已经知道它$$可以代替引号使用。因此，INSERT INTO .... RETURNING id它是一个我们想要运行的 SQL 字符串。

这里有两点需要说明：

1. 这$1是用于替换的（稍后我会详细介绍USING）。在我们的示例中，我们使用了Postgres 中的字符串连接运算符||。
2. 这RETURNING id是 Postgres 的一个特性。你可以插入一条记录（或批量插入多条记录）并id立即获取结果。你甚至可以使用 `get_request_id()`RETURNING *来获取整个新记录。

这条INTO语句会将查询结果存储在我们之前声明的变量中。我之前不知道这一点，一直尝试project_id := EXECUTE $$ statement $$这样做，但都失败了 :(。在我们的例子中，我们将返回的 id 存储到project_id.

该USING语句用于变量替换。您可以在此处传入一个变量列表。第$1一个变量将被替换为您在该USING语句中提供的第一个变量/值。第二个变量可以通过 `{{1}}` 访问，$2依此类推。

呼……好。

EXECUTE...USING 的文档称此操作执行的是动态语句。令人困惑的是……这与EXECUTE执行预处理语句的命令不同。

将最多 100 项任务转移到新项目

EXECUTE $$ UPDATE tasks 
      SET project_id = $1
      WHERE tasks.id IN (
          SELECT tasks.id 
          FROM documents 
          INNER JOIN projects ON projects.id = tasks.project_id
          WHERE projects.name = $2
          ORDER BY tasks.id ASC
          LIMIT 100) $$ 
     USING project_id, 'Client project';

EXECUTE...INTO...USING我很庆幸之前的问题已经解释清楚了，因为这个问题要复杂得多。

首先，我们来看一下简化后的查询：

UPDATE tasks SET project_id = $1 WHERE tasks.id IN 
  (-- subquery)

我们将新项目 ID 设置为project_id从子查询结果中获取的先前 EXECUTE 语句中获取的任务 ID。

接下来是子查询：

SELECT tasks.id 
  FROM documents 
  INNER JOIN projects ON projects.id = tasks.project_id
  WHERE projects.name = $2
    ORDER BY tasks.id ASC
    LIMIT 100

在子查询中，我们查找属于需要拆分的那个大型项目的 100 个任务。我们获取该项目中的下 100 个任务。我们使用 `<project_name>`$2替换为原始大型项目的项目名称。我们按 `<sort_name>` 排序，tasks.id ASC以确保任务在拆分过程中不会顺序错乱。

最后，是包含所有步骤的 execute 语句：

EXECUTE $$ 
 -- query
$$
USING project_id, 'Client project';

与之前一样，我们执行一条语句，但这次我们不存储查询结果。我们替换为两个不同的变量，这两个变量可以通过查询$1访问。$2

另一份提高通知

RAISE NOTICE 'Finished Counter: %', counter;

和之前一样，这会让我们知道循环已经完成。我只是喜欢记录日志。

我需要知道这个吗？

我的天啊，当然不是。除非你每天都泡在数据库里，否则没人需要对这些功能了如指掌，但了解一下总是好的。我在学习或使用任何技术时都会秉持这种理念。我知道我可以用 SQL 做所有这些事情，因为我之前有过使用 SQL 的经验，我只需要花些时间阅读 Postgres 的文档，就能弄清楚如何完成这项任务。我认为这种知识非常宝贵。