Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- DROP TABLE IF EXISTS transaction CASCADE;
- CREATE TABLE transaction (
- times INTEGER PRIMARY KEY,
- ta Integer,
- object VARCHAR(1),
- access VARCHAR(5),
- CHECK(access = 'read' OR access = 'write')
- );
- INSERT INTO transaction
- (times, ta, object, access)
- VALUES
- (1, 1, 'A', 'read'),
- (2, 2, 'B', 'read'),
- (3, 2, 'B', 'write'),
- (4, 3, 'A', 'read'),
- (5, 3, 'C', 'write'),
- (6, 2, 'C', 'read'),
- (7, 1, 'B', 'read'),
- (8, 3, 'D', 'write');
- SELECT a.object, a.times AS A_Time, a.ta AS A_Trans, b.times AS B_Time, b.ta AS B_Trans
- FROM transaction a
- JOIN transaction b ON a.object = b.object
- JOIN transaction c ON a.ta = c.ta
- WHERE a.ta <> b.ta
- AND b.times > a.times
- AND c.times > b.times;
- WITH RECURSIVE r AS (
- SELECT 0 AS times, 0 AS ta
- UNION ALL
- SELECT b.times, b.ta
- FROM r
- JOIN transaction b on b.times > r.times
- JOIN transaction c ON c.ta = b.ta
- AND b.ta <> r.ta
- AND b.times <> c.times
- )
- SELECT times, ta
- FROM r
- ;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement