Проблема SQL-SQL с SQL Server 2005: нужна помощь в исключении записей
Добрый день!
Мне нужна помощь в написании запроса. У меня есть записи в таблице ниже. Условие не будет, записи не должны отображаться, если повторение новых записей new_state из предыдущих записей (new_state) и если оно изменено в том же Дата..
здесь record_id 1 прошел через ff состояния: 0-> 1-> 2-> 1-> 3-> 4-> 3 в тот же день .. состояние 1 было изменено на состояние 2, а затем снова на состояние 1 (id 2 и 3 не будут отображаться). То же самое с состоянием 3 (идентификаторы 5 и 6 не отображаются).
id | record_id| date_changed | old_state | new_state | 1 | 1 | 2009-01-01 | 0 | 1 | 2 | 1 | 2009-01-01 | 1 | 2 | not displayed 3 | 1 | 2009-01-01 | 2 | 1 | not displayed 4 | 1 | 2009-01-01 | 1 | 3 | 5 | 1 | 2009-01-01 | 3 | 4 | not displayed 6 | 1 | 2009-01-01 | 4 | 3 | not displayed поэтому в результате будет отображаться только 2 записи для record_id = 1 ..
id | record_id| date_changed | old_state | new_state | 1 | 1 | 2009-01-01 | 0 | 1 | 4 | 1 | 2009-01-01 | 1 | 3 |
Вот код для создания таблицы и данных:
IF OBJECT_ID('TempDB..#table','U') IS NOT NULL DROP TABLE #table CREATE TABLE #table ( id INT identity primary key, record_id INT, date_changed DATETIME, old_state INT, new_state INT ) INSERT INTO #table(record_id,date_changed,old_state,new_state) SELECT 1,'2009-01-01',0,1 UNION ALL --displayed SELECT 1,'2009-01-01',1,2 UNION ALL --not displayed SELECT 1,'2009-01-01',2,1 UNION ALL --not displayed SELECT 1,'2009-01-01',1,3 UNION ALL --displayed SELECT 1,'2009-01-01',3,4 UNION ALL --not displayed SELECT 1,'2009-01-01',4,3 --not displayed INSERT INTO #table(record_id,date_changed,old_state,new_state) SELECT 3,'2009-01-01',0,1 UNION ALL --displayed SELECT 3,'2009-01-01',1,2 UNION ALL --not displayed SELECT 3,'2009-01-01',2,3 UNION ALL --not displayed SELECT 3,'2009-01-01',3,4 UNION ALL --not displayed SELECT 3,'2009-01-01',4,1 --not displayed SELECT * FROM #table
Буду признателен за любую помощь..
благодаря
Для ясности относительно record_id = 3 .. Учитывая эту таблицу:
id | record_id| date_changed | old_state | new_state | 7 | 3 | 2009-01-01 | 0 | 1 | 8 | 3 | 2009-01-01 | 1 | 2 | not displayed 9 | 3 | 2009-01-01 | 2 | 3 | not displayed 10 | 3 | 2009-01-01 | 3 | 4 | not displayed 11 | 3 | 2009-01-01 | 4 | 1 | not displayed
при выполнении запроса для record_id = 3 результатом таблицы будет:
id | record_id| date_changed | old_state | new_state | 7 | 3 | 2009-01-01 | 0 | 1 |
Благодаря!
ОБНОВЛЕНИЕ (12/2/2009):
Специальный сценарий
id | record_id| date_changed | old_state | new_state | 1 | 4 | 2009-01-01 | 0 | 1 | displayed 2 | 4 | 2009-01-01 | 1 | 2 | displayed 3 | 4 | 2009-01-01 | 2 | 3 | not displayed 4 | 4 | 2009-01-01 | 3 | 2 | not displayed 5 | 4 | 2009-01-01 | 2 | 3 | displayed 6 | 4 | 2009-01-01 | 3 | 4 | not displayed 7 | 4 | 2009-01-01 | 4 | 3 | not displayed
где new_state 3 появляется на id 3,5 и 7 .. id 3 не будет отображаться, так как он находится между id 2 и id 4, у которых есть одно и то же new_state (3). Затем должен отображаться идентификатор 5, так как нет существующего new_state 3 еще ..
фрагмент кода:
IF OBJECT_ID('TempDB..#tablex','U') IS NOT NULL DROP TABLE #tablex CREATE TABLE #tablex ( id INT identity primary key, record_id INT, date_changed DATETIME, old_state INT, new_state INT ) INSERT INTO #tablex(record_id,date_changed,old_state,new_state) SELECT 4,'2009-01-01',0,1 UNION ALL --displayed SELECT 4,'2009-01-01',1,2 UNION ALL --displayed SELECT 4,'2009-01-01',2,3 UNION ALL --not displayed SELECT 4,'2009-01-01',3,2 UNION ALL --not displayed SELECT 4,'2009-01-01',2,3 UNION ALL --displayed SELECT 4,'2009-01-01',3,4 UNION ALL --not displayed SELECT 4,'2009-01-01',4,3 --not displayed
Я думаю, что последовательность в построении результата важна.
Благодаря!
SELECT A.* /* A.ID, A.old_state, a.new_state, B.ID as [Next], b.old_state, b.new_state, C.ID as [Prev], c.old_state, c.new_state */ FROM #table A LEFT JOIN #table B ON A.ID = (B.ID - 1) LEFT JOIN #table C ON (A.ID - 1) = C.ID -- WHERE A.old_State <> B.new_State AND A.new_State <> C.old_State WHERE A.record_id = 1 AND A.old_State <> COALESCE(B.new_State, -1) AND A.new_State <> COALESCE(C.old_State, -1)
EDIT: Я полагаю, что OP нужен, чтобы была выбрана оставшаяся запись, кроме тех, где старое состояние текущей записи не совпадает с новым состоянием новой записи (вид операции отмены в записях), и новое состояние текущей записи не должно быть таким же как прежнее состояние предыдущей записи.
Последующие шаги для достижения результата
- выберите все элементы, которые не должны появляться в результате.
- левые присоединяют их к исходной таблице и выбирают только те записи, которые не совпадают, а не должны отображаться .
,
;WITH cte_table (master_id, master_state, id, record_id, old_state, new_state, level) AS ( SELECT id, old_state, id, record_id, old_state, new_state, 1 FROM #table UNION ALL SELECT master_id, master_state, #table.id, #table.record_id, #table.old_state, #table.new_state, level + 1 FROM cte_table INNER JOIN #table ON cte_table.new_state = #table.old_state AND cte_table.record_id = #table.record_id AND cte_table.id < #table.id AND cte_table.master_state < #table.old_state ) SELECT master_id, t1.*, level INTO #result FROM #table t1 INNER JOIN ( SELECT master_id, min_child_id = MIN(id), level FROM cte_table GROUP BY master_id, level ) t2 ON t2.min_child_id = t1.id SELECT t1.* FROM #table t1 LEFT OUTER JOIN ( SELECT r1.id FROM #result r1 INNER JOIN ( SELECT r1.master_id FROM #result r1 INNER JOIN #result r2 ON r2.new_state = r1.old_state AND r2.master_id = r1.master_id WHERE r1.level = 1 ) r2 ON r2.master_id = r1.master_id ) r1 ON r1.id = t1.id WHERE r1.id IS NULL AND t1.old_state < t1.new_state ORDER BY 1, 2, 3