Граждане, чиркните плз пару строк, а то мозг заклинило:
Задача: Есть таблички A и B, связь один-ко-многим. У B есть некий атрибут K.
Хочется одним запросом (без процедур), если такое можно, выбрать все атрибуты А, количество относящихся к этому А записей из B и количество относящихся к этому А записей из B с определенным значением K={чего хочу = 1}.
Скрипты создания:
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.
/*==============================================================*/
/* DBMS name: Microsoft SQL Server 2000 */
/*==============================================================*/
alter table EB
drop constraint FK_EB_CONTAINS_EA
go
if exists (select 1
from sysindexes
where id = object_id('EB')
and name = 'Contains_FK'
and indid > 0
and indid < 255 )
drop index EB.Contains_FK
go
if exists (select 1
from sysobjects
where id = object_id('EA')
and type = 'U')
drop table EA
go
if exists (select 1
from sysobjects
where id = object_id('EB')
and type = 'U')
drop table EB
go
/*==============================================================*/
/* Table: EA */
/*==============================================================*/
create table EA (
ID int not null,
Name varchar( 12 ) not null,
CreationDate datetime not null,
constraint PK_EA primary key nonclustered (ID)
)
go
/*==============================================================*/
/* Table: EB */
/*==============================================================*/
create table EB (
EA_ID int not null,
SheduledDate datetime not null,
Result int not null
constraint CKC_RESULT_EB check (Result in ( 0 , 1 , 2 ))
)
go
/*==============================================================*/
/* Index: Contains_FK */
/*==============================================================*/
create index Contains_FK on EB (
EA_ID ASC
)
go
alter table EB
add constraint FK_EB_CONTAINS_EA foreign key (EA_ID)
references EA (ID)
go
Тестовые данные:
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.
insert into EA (ID, Name, CreationDate) values ( 1 , 'Name1', '1-1-1 0:0:0')
go
insert into EA (ID, Name, CreationDate) values ( 2 , ' Name2', '1-1-1 0:0:2')
go
insert into EA (ID, Name, CreationDate) values ( 3 , 'Name3', '1-1-1 0:0:0')
go
insert into EB (EA_ID, SheduledDate, Result) values ( 1 , '1-1-1 0:0:5', 1 )
go
insert into EB (EA_ID, SheduledDate, Result) values ( 2 , '1-1-1 0:0:16', 2 )
go
insert into EB (EA_ID, SheduledDate, Result) values ( 1 , '1-1-1 0:0:9', 1 )
go
insert into EB (EA_ID, SheduledDate, Result) values ( 2 , '1-1-1 0:0:17', 2 )
go
insert into EB (EA_ID, SheduledDate, Result) values ( 1 , '1-1-1 0:0:19', 1 )
go
insert into EB (EA_ID, SheduledDate, Result) values ( 2 , '1-1-1 0:0:6', 2 )
go
insert into EB (EA_ID, SheduledDate, Result) values ( 1 , '1-1-1 0:0:3', 1 )
go
insert into EB (EA_ID, SheduledDate, Result) values ( 2 , '1-1-1 0:0:13', 2 )
go
insert into EB (EA_ID, SheduledDate, Result) values ( 2 , '1-1-1 0:0:2', 1 )
go
insert into EB (EA_ID, SheduledDate, Result) values ( 1 , '1-1-1 0:0:11', 2 )
go
insert into EB (EA_ID, SheduledDate, Result) values ( 1 , '1-1-1 0:0:0', 1 )
go
insert into EB (EA_ID, SheduledDate, Result) values ( 1 , '1-1-1 0:0:9', 2 )
go
insert into EB (EA_ID, SheduledDate, Result) values ( 1 , '1-1-1 0:0:7', 1 )
go
insert into EB (EA_ID, SheduledDate, Result) values ( 2 , '1-1-1 0:0:1', 2 )
go
insert into EB (EA_ID, SheduledDate, Result) values ( 2 , '1-1-1 0:0:15', 1 )
go
insert into EB (EA_ID, SheduledDate, Result) values ( 2 , '1-1-1 0:0:4', 2 )
go
insert into EB (EA_ID, SheduledDate, Result) values ( 2 , '1-1-1 0:0:17', 1 )
go
insert into EB (EA_ID, SheduledDate, Result) values ( 1 , '1-1-1 0:0:12', 2 )
go
insert into EB (EA_ID, SheduledDate, Result) values ( 1 , '1-1-1 0:0:10', 1 )
go
insert into EB (EA_ID, SheduledDate, Result) values ( 2 , '1-1-1 0:0:2', 2 )
go
Как это сделать?
Получив примерно такую выборку:
1.
2.
3.
1 , 'Name1', '1-1-1 0:0:0', 10 , 7
2 , ' Name2', '1-1-1 0:0:2', 10 , 3
3 , 'Name3', '1-1-1 0:0:0', 0 , 0
|
