我有以下SQL Server数据库结构,我必须使用它来查询数据。模型可能是错误的;如果是这样的话,我很欣赏争论,所以我可以要求改变。如果没有,我需要一个查询来获取选项卡式数据,格式将在下面详细介绍。
结构是这样的:
CLIENTS
:
ClientID ClientName
-----------------------
1 James
2 Leonard
3 Montgomery
ATTRIBUTES
:
AttributeID AttributeName
-----------------------------
1 Rank
2 Date
3 Salary
4 FileRecordsAmount
ATTRIBUTES_STRING
:
ClientID AttributeID AttributeStringValue
1 1 Captain
2 1 Chief Surgeon
3 1 Chief Engineer
ATTRIBUTES_NUMERIC
:
ClientID AttributeID AttributeNumericValue
1 4 187
2 4 2
3 4 10
我需要的结果如下:
RESULTS:
----------------------------------------------------------
ClientID ClientName Rank FileRecordsAmount
1 James Captain 187
2 Leonard Chief Surgeon 2
3 Montgomery Chief Engineer 10
我怎样才能做到这一点?
谢谢!
编辑:这里具有挑战性的问题是(对我来说)属性是动态的......我有 5 个属性表(ATTRIBUTES_STRING、ATTRIBUTES_NUMERIC、ATTRIBUTES_DATE、ATTRIBUTES_BIT、ATTRIBUTES_INT),用户应该能够设置自己的属性。
你需要一个SQL联接。它将看起来像这样:
select
CLIENTS.ClientID,
CLIENTS.ClientName,
ATTRIBUTES_STRING1.AttributeStringValue as Rank,
ATTRIBUTES_NUMERIC2.AttributeNumericValue as FileRecordsAmount
from
CLIENTS,
ATTRIBUTES ATTRIBUTES1,
ATTRIBUTES ATTRIBUTES2,
ATTRIBUTES_STRING ATTRIBUTES_STRING1,
ATTRIBUTES_NUMERIC ATTRIBUTES_NUMERIC2
where CLIENTS.ClientID = ATTRIBUTES_STRING1.ClientID
and CLIENTS.ClientID = ATTRIBUTES_NUMERIC2.ClientID
and ATTRIBUTES_STRING1.AttributeID = ATTRIBUTES1.AttributeID
and ATTRIBUTES_NUMERIC2.AttributeID = ATTRIBUTES2.AttributeID
and ATTRIBUTES1.AttributeName = 'Rank'
and ATTRIBUTES2.AttributeName = 'FileRecordsAmount'
;
这是SQL小提琴供参考。这是我的第一个EAV模式,所以我不会太信任它:)
编辑:下面提供的架构供参考:
create table CLIENTS (
ClientID integer primary key,
ClientName varchar(50) not null
);
insert into CLIENTS values (1,'James');
insert into CLIENTS values (2,'Leonard');
insert into CLIENTS values (3,'Montgomery');
create table ATTRIBUTES (
AttributeID integer primary key,
AttributeName varchar(50) not null
);
create index ATTRIBUTE_NAME_IDX on ATTRIBUTES (AttributeName);
insert into ATTRIBUTES values (1,'Rank');
insert into ATTRIBUTES values (2,'Date');
insert into ATTRIBUTES values (3,'Salary');
insert into ATTRIBUTES values (4,'FileRecordsAmount');
create table ATTRIBUTES_STRING (
ClientID integer,
AttributeID integer not null,
AttributeStringValue varchar(255) not null,
primary key (ClientID, AttributeID)
);
insert into ATTRIBUTES_STRING values (1,1,'Captain');
insert into ATTRIBUTES_STRING values (2,1,'Chief Surgeon');
insert into ATTRIBUTES_STRING values (3,1,'Chief Engineer');
create table ATTRIBUTES_NUMERIC (
ClientID integer,
AttributeID integer not null,
AttributeNumericValue numeric(10, 5) not null,
primary key (ClientID, AttributeID)
);
insert into ATTRIBUTES_NUMERIC values (1,4,187);
insert into ATTRIBUTES_NUMERIC values (2,4,2);
insert into ATTRIBUTES_NUMERIC values (3,4,10);
编辑:修改了选择,使其更容易使用额外的属性进行扩展