|
23.07.2018, 16:56
#39677803
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
Ссылка на профиль пользователя:
|
|
|
Участник
Откуда: Запорожье
Сообщения: 56 486
Рейтинг:
0
/ 0
|
|
|
|
вот тестовая БД на коленке
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
use test;
drop table acc_operations;
drop table acc_operation_code;
drop table operation;
drop table main;
go
create table dbo.main
(
main_id int,
name varchar(500),
department_id int not null,
constraint pk_main primary key(main_id)
);
create table dbo.operation
(
operation_id int identity(1,1),
main_id int not null,
acc_operation_code int not null,
operation_date datetime not null,
name varchar(500),
constraint pk_operation primary key(operation_id),
constraint fk_operation foreign key(main_id) references main
);
create table dbo.acc_operation_code
(
acc_operation_code int not null,
acc_operation_name varchar(500) not null,
constraint pk_acc_operation_code primary key(acc_operation_code)
);
create table dbo.acc_operations
(
acc_operation_id int,
acc_operation_code int not null,
acc_operation_feature int not null,
acc_operation_actuality_from datetime not null,
acc_operation_actuality_to datetime not null,
name varchar(500),
constraint pk_acc_operation primary key(acc_operation_id),
constraint fk_acc_operation foreign key(acc_operation_code) references acc_operation_code
);
insert into dbo.acc_operation_code
(
acc_operation_code,
acc_operation_name
)
select 1, 'operation code 1' union all
select 2, 'operation code 2' union all
select 3, 'operation code 3'
insert into dbo.acc_operations
(
acc_operation_id ,
acc_operation_code,
acc_operation_feature ,
acc_operation_actuality_from ,
acc_operation_actuality_to
)
select 1, 1, 1, {ts '2017-01-01 00:00:00'}, {ts '2017-12-31 23:59:59'} union all
select 2, 1, 0, {ts '2018-01-01 00:00:00'}, {ts '2018-12-31 23:59:59'} union all
select 3, 2, 1, {ts '2017-01-01 00:00:00'}, {ts '2017-12-31 23:59:59'} union all
select 4, 2, 0, {ts '2018-01-01 00:00:00'}, {ts '2018-12-31 23:59:59'} union all
select 5, 3, 0, {ts '2017-01-01 00:00:00'}, {ts '2018-12-31 23:59:59'}
;with main1(id) as
(
select 1 id
union all
select id+1 from main1 where id < 32767
)
insert into main(main_id, name, department_id)
select id, cast(id as varchar(100))+'-'+cast(id as varchar(100))+'-'+cast(id as varchar(100))+'-'+cast(id as varchar(100))+'-'+cast(id as varchar(100))+'-'+cast(id as varchar(100)),
id / 1000
from main1
option (maxrecursion 32767)
insert into main(main_id, name, department_id)
select main_id+32768, name+'-32768', department_id from main;
insert into main(main_id, name, department_id)
select main_id+65536, name+'-65536', department_id from main;
---select * from main;
insert into dbo.operation
(
main_id,
acc_operation_code,
operation_date,
name)
select --top 100
main.main_id,
acc_operation_code,
dateadd(hour, main.main_id/6, {ts '2017-01-01 00:00:00'}),
cast(main.main_id as varchar(100))+ '='+cast(acc_operation_code as varchar(100))
from main join (select 1 main_id, 1 acc_operation_code union select 2 id, 2 union select 3 id, 3) o on 1=1
go
запрос через подзапрос
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
declare @department_id int = 11;
declare @acc_operation_feature int = 1;
select *
from(
select dbo.main.*,
row_number() over(order by name) rn
from dbo.main
where main_id in(
select main_id
from dbo.operation op --on op.main_id = main.main_id
left join acc_operation_code code on code.acc_operation_code = op.acc_operation_code
left join acc_operations ops on ops.acc_operation_code = op.acc_operation_code
and op.operation_date between acc_operation_actuality_from and acc_operation_actuality_to
where (@department_id is null or main.department_id = @department_id)
and (@acc_operation_feature is null or ops.acc_operation_feature = @acc_operation_feature)
)
) subq
where rn >= 1000
and rn < 1500
запрос с джойнами
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
declare @department_id int = 11;
declare @acc_operation_feature int = 1;
select subq.*
from(
select subq.*, row_number() over(order by name) rn
from(
select distinct
dbo.main.*
from dbo.main
left join dbo.operation op on op.main_id = main.main_id
left join acc_operation_code code on code.acc_operation_code = op.acc_operation_code
left join acc_operations ops on ops.acc_operation_code = op.acc_operation_code
and op.operation_date between acc_operation_actuality_from and acc_operation_actuality_to
where (@department_id is null or main.department_id = @department_id)
and (@acc_operation_feature is null or ops.acc_operation_feature = @acc_operation_feature)
) subq
) subq
where rn >= 1000
and rn < 1500
|
|
|
