|
楼主 |
发表于 2023-11-21 18:40:09
|
显示全部楼层
黑龙江省伊春市
自行解决了WITH split_notes AS (
SELECT
id,
SUBSTR(note_2, 2, INSTR(note_2, '/') - 2) AS card_key,
SUBSTR(note_2, INSTR(note_2, '/') + 1) AS operator,
chat_name,
note_3 AS amount,
note_4 AS rate,
note_5 AS total,
time
FROM data
),
ranked AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY card_key ORDER BY time) as rn
FROM split_notes
)
SELECT
c1.id AS id,
c1.card_key AS card_key,
c1.operator AS creator,
c1.chat_name AS creator_chat_name,
c1.amount AS creator_amount,
c1.rate AS creator_rate,
c1.total AS creator_total,
c1.time AS creator_time,
c2.operator AS user,
c2.chat_name AS user_chat_name,
c2.amount AS user_amount,
c2.rate AS user_rate,
c2.total AS user_total,
c2.time AS user_time,
(c2.amount - c1.amount) AS amount_difference,
(c2.rate - c1.rate) AS rate_difference,
(c2.total - c1.total) AS total_difference
FROM ranked c1
LEFT JOIN ranked c2 ON c1.card_key = c2.card_key AND c2.rn = 2
WHERE c1.rn = 1; |
|