CREATE TABLE #tbl (ad_id INT, ac_id VARCHAR(255), dt INT, rel INT)
INSERT INTO #tbl VALUES (1,'10',111,1)
INSERT INTO #tbl VALUES (2,'11',112,0)
INSERT INTO #tbl VALUES (3,'12',113,0)
INSERT INTO #tbl VALUES (3,'13',114,1)
INSERT INTO #tbl VALUES (3,'14',115,1)
INSERT INTO #tbl VALUES (4,'15',116,1)
INSERT INTO #tbl VALUES (4,'16',117,1)
INSERT INTO #tbl VALUES (4,'17',118,1)
SELECT * , dt1 = MIN(dt), flg = 0
INTO #tbl2
FROM #tbl
WHERE rel = 1
GROUP BY ad_id
HAVING rel = 1
UPDATE #tbl2
SET flg = 1
FROM #tbl2
WHERE dt = dt1
WHILE EXISTS (SELECT 1 FROM #tbl2
WHERE flg = 0)
BEGIN
UPDATE #tbl2
SET ac_id = t1.ac_id +',' + t2.ac_id
,flg = 2
FROM #tbl2 t1, #tbl2 t2
WHERE t1.ad_id = t2.ad_id
AND t1.dt = t1.dt1
AND t2.dt = (SELECT MIN(dt) FROM #tbl2
WHERE ad_id = t1.ad_id
AND dt > t1.dt)
DELETE #tbl2
FROM #tbl2 t1
WHERE t1.dt = (SELECT MIN(dt) FROM #tbl2
WHERE ad_id = t1.ad_id
AND dt > t1.dt1)
END
No comments:
Post a Comment