最近在接触学习MySQL、SQL Server相关的，碰到一些SQL的写法，记录一下，积累起来。
1. SQL Server中可以使用WITH语法，而且其中可以使用自定义的变量，可以是个常数，还可以是SQL，如下所示，这个SQL在SQL Server Management Studio和DBeaver都可以执行，
代码语言：javascript复制DECLARE @START_DATE VARCHAR(15)
SET @START_DATE='20210323' --=(SELECT SDATE FROM t WHERE id = 30);
;
WITH TABLE1 AS
(
SELECT ID FROM TABLE WHERE STARTDATE BETWEEN @START_DATE AND @START_DATE
)
SELECT * FROM TABLE1;但是在HeidiSQL客户端，会提示如下错误，
这个问题应该不是数据库了，很可能是客户端在处理这种SQL的差异，很可能将";"作为断句了，并未结合上下文。
2. MySQL的一张表，根据相邻两行的某个字段做关联，如下所示，from_string和to_string，同样用了自定义的变量，(以下的SQL删除业务字段，仅做展示用途)，
代码语言：javascript复制SET @i = 0;
SET @j = 0;
SELECT
a.id,
b.id
FROM
(
SELECT
( @i := @i 1 ) AS id,
from_string,
to_string
FROM
table
WHERE
log = 'status'
ORDER BY
issue_id,updated
) AS a
INNER JOIN (
SELECT
( @j := @j 1 ) AS id,
from_string,
to_string
FROM
table
WHERE
log = 'status'
ORDER BY
issue_id, updated
) AS b
ON a.issue_id = b.issue_id
AND a.to_string = b.from_string
AND a.id 1 = b.id;可以用这种形式，不用提前定义变量了，语句当中，随定义随用，
代码语言：javascript复制select a.row_number,
b.row_number,
(select from_string, to_string, updated, @num := @num 1 as row_number from (SELECT @num:=0) r,table WHERE log = 'status' ORDER BY issue_id, updated) a,
(select from_string, to_string, updated, @num2 := @num2 1 as row_number from (SELECT @num2:=0) r2, table WHERE log = 'status' ORDER BY issue_id, updated) b
where a.row_number 1 = b.row_number ;
