您可以使用会话变量模拟类似行号的功能。我们在两个派生表中分别得到所有的输入和输出,并做一个 LEFT JOIN 在他们身上,得到想要的输出。 这甚至适用于 IN 以及 OUT 不是连续的。它还将处理有争议的案件 IN 没有 OUT . 如果有一个 OUT 没有 IN . 尝试以下查询:
SET @row_no_1 = 0;
SET @row_no_2 = 0;
SELECT
t1.row_no AS ID, t1.Number AS `IN`, t2.Number AS `OUT`
FROM
(SELECT
@row_no_1:=@row_no_1 + 1 AS row_no, Number
FROM
`your_table`
WHERE
Type = 'IN'
ORDER BY id ASC) AS t1
LEFT JOIN
(SELECT
@row_no_2:=@row_no_2 + 1 AS row_no, Number
FROM
`your_table`
WHERE
Type = 'OUT'
ORDER BY id ASC) AS t2 ON t2.row_no = t1.row_no
SET @row_number = 0;
SET @row_number2 = 0;
SELECT
out_table.OUTs AS outs, in_table.Ins as INs FROM
(SELECT
(@row_number2:=@row_number2 + 1) AS num2, Number as OUTs FROM your_table WHERE your_table.Type = 'OUT') as out_table ,
(SELECT
(@row_number:=@row_number + 1) AS num1, Number as Ins FROM your_table WHERE your_table.Type = 'IN') as in_table
WHERE num2 = num1
SELECT a.ID
MAX(CASE WHEN a.type = "IN" THEN a.Number ELSE "" END) AS IN_Type,
MAX(CASE WHEN b.type = "IN" THEN b.Number ELSE "" END) AS Out_Type
FROM table1 a Left join table1 b on a.ID = b.ID
Group by a.ID
3条答案
按热度按时间vhmi4jdf1#
您可以使用会话变量模拟类似行号的功能。我们在两个派生表中分别得到所有的输入和输出,并做一个
LEFT JOIN
在他们身上,得到想要的输出。这甚至适用于
IN
以及OUT
不是连续的。它还将处理有争议的案件IN
没有OUT
.如果有一个
OUT
没有IN
.尝试以下查询:
u3r8eeie2#
这将使用隐式连接。
它将使用mysql会话变量。作为参考,你可以阅读http://www.mysqltutorial.org/mysql-variables/ 用于会话变量。
pinkon5k3#
回答我自己。。。