Tylko pamiętajcie o średniku przed with! ;)
Odliczanie od 1 do 10.
1
2
3
4
5
6
7
| ;with cte as
(
select 1 as x
union all
select x+1 from cte where x < 10
)
select * from cte
|
Wynik
1
2
3
4
5
6
7
8
9
10
11
12
| x
-----------
1
2
3
4
5
6
7
8
9
10
|
Równie dobrze możemy przechodzić po datach:
1
2
3
4
5
6
7
8
9
10
11
| declare @start_date datetime = '2014-05-06 00:00:00'
declare @dest_date datetime = '2014-05-12 00:00:00'
;with date_cte as
(
select @start_date as mydate
union all
select DATEADD(dd, 1, mydate)
from date_cte c
where DATEADD(dd, 1, mydate) <= @dest_date
)
select mydate from date_cte
|
Wynik:
1
2
3
4
5
6
7
8
9
| mydate
-----------------------
2014-05-06 00:00:00.000
2014-05-07 00:00:00.000
2014-05-08 00:00:00.000
2014-05-09 00:00:00.000
2014-05-10 00:00:00.000
2014-05-11 00:00:00.000
2014-05-12 00:00:00.000
|
Standardowe przechodzenie po drzewie
Drzewo
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
| declare @t table
(
id int not null,
parent_id int null
)
insert into @t values (0, null),(1, 0),(2, 1),(3, 1),(4, 3),(5, 0),(6, 0),(7, 6)
-- 0
-- +-1
-- | +-2
-- | +-3
-- | +-4
-- +-5
-- +-6
-- +7
|
1
2
3
4
5
6
7
8
9
10
| id parent_id
----------- -----------
0 NULL
1 0
2 1
3 1
4 3
5 0
6 0
7 6
|
Od liścia do korzenia
1
2
3
4
5
6
7
8
9
| ;with cte as
(
select id, parent_id from @t where id = 4
union all
select t.id, t.parent_id
from cte c
join @t t on c.parent_id = t.id
)
select * from cte
|
wynik
1
2
3
4
5
6
7
8
| id parent_id
----------- -----------
4 3
3 1
1 0
0 NULL
(4 row(s) affected)
|
Od gałęzi do liści
1
2
3
4
5
6
7
8
9
| ;with cte as
(
select id, parent_id from @t where parent_id = 1
union all
select t.id, t.parent_id
from cte c
join @t t on t.parent_id = c.id
)
select * from cte
|
wynik
1
2
3
4
5
6
7
| id parent_id
----------- -----------
2 1
3 1
4 3
(3 row(s) affected)
|