dimanche 1 février 2015

How to solve cursor-based SQL task on SQLite?

I've recently had a job interview with SQL task, which requires using cursors, but code must have been written for SQLite that doesn't have cursors. So, how could I solve it? Task is following:



Exists a table which has two columns: "b" - the beginning and "e" - the end.
Each row of this table represents 1-dimentional line - column "b"
contains coordinate of the beginning, column "e" - coordinate of
the end. e.g.:
b | e
-------
1 | 5
2 | 4
3 | 7
-------
Column "e" always greater than "b" in each row. It is required to calculate
length of covered surface on coordinate axis. For this example answer
is 6 = ((5 - 1) + (7 - 5)).


This is my solution with cursors:



DECLARE @temp_table TABLE(b int, e int);
DECLARE [cursor] CURSOR FOR SELECT b, e FROM [table]
DECLARE @b INT
DECLARE @e INT

OPEN [cursor]
FETCH NEXT FROM [cursor] INTO @b, @e
WHILE @@FETCH_STATUS = 0
BEGIN
IF (EXISTS(SELECT * FROM @temp_table))
FETCH NEXT FROM [cursor] INTO @b, @e
IF (EXISTS(SELECT * FROM @temp_table WHERE b <= @b AND e >= @e))
CONTINUE;
IF (EXISTS(SELECT * FROM @temp_table WHERE e < @b OR b > @e)
OR NOT EXISTS(SELECT * FROM @temp_table))
BEGIN
INSERT INTO @temp_table VALUES(@b, @e)
CONTINUE
END
IF (EXISTS(SELECT * FROM @temp_table WHERE b <= @b AND e <= @e))
BEGIN
UPDATE @temp_table SET e = @e WHERE b <= @b AND e <= @e
CONTINUE;
END
IF (EXISTS(SELECT * FROM @temp_table WHERE b >= @b AND e >= @e))
BEGIN
UPDATE @temp_table SET b = @b WHERE b >= @b AND e >= @e
CONTINUE;
END
END

CLOSE [cursor]
DEALLOCATE [cursor]

SELECT SUM(e - b) FROM @temp_table


How can I write same code for SQLite? I asked my friends and nobody knows. I tried to solve this task many times, but I didn't have success. Thought about this unresolved problem makes me upset. Help me, please! Any help is appreciated.


Aucun commentaire:

Enregistrer un commentaire