表A (fID,fItemClassID,fNO) 1,2,'单据A' 表B (fID,fItemClassID,fNO) 1,3,'单据B' 表C (fID,fTableName,fName) (2,'A','A单')(3,'B','B单') 表D (fName,fRelationID,fRelationClassID)('aa',1,2)('bb',1,3)我想得到的查询结果是根据C表的fTableName关联A表或者B表或者其他表 Select d.fName,c.fName,(c.fTableName).fName From D Left Join C On C .fID = D.fRelationClassIDID Left Join (c.fTableName) On (c.fTableName).fID = d.fRelationID Left Join A On D.fRelationID = (c.fTableName).fID
返回的结果是 aa,A单,单据A bb,B单,单据B
---------------------------------------------------------------- -- Author :DBA_HuangZJ(发粪涂墙) -- Date :2014-06-25 10:58:37 -- Version: -- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) -- Jun 17 2011 00:54:03 -- Copyright (c) Microsoft Corporation -- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor) -- ---------------------------------------------------------------- --> 测试数据[a] if object_id('[a]') is not null drop table [a] go create table [a]([fID] int,[fItemClassID] int,[fNO] nvarchar(6)) insert [a] select 1,2,N'单据A' --> 测试数据[b] if object_id('[b]') is not null drop table [b] go create table [b]([fID] int,[fItemClassID] int,[fNO] nvarchar(6)) insert [b] select 1,3,N'单据B' if object_id('[c]') is not null drop table c go CREATE TABLE c (fID INT ,fTableName nvarchar(6),fName nvarchar(6)) INSERT INTO c values(2,'A',N'A单'),(3,'B',N'B单') if object_id('[d]') is not null drop table d go CREATE TABLE D (fName nvarchar(6),fRelationID INT ,fRelationClassID INT )INSERT INTO d values('aa',1,2),('bb',1,3)--------------生成数据--------------------------Select d.fName,c.fName,ISNULL(B.[fNO],A.[fNO])[fNO] From c Left Join d On C .fID = D.fRelationClassID Left Join B On c.fID = B.[fItemClassID] Left Join A On c.fID = A.[fItemClassID] ----------------结果---------------------------- /* fName fName fNO ------ ------ ------ aa A单 单据A bb B单 单据B */
create table A(fID int,fItemClassID int,fNO varchar(10))insert into A select 1,2,'单据A'create table B(fID int,fItemClassID int,fNO varchar(10))insert into B select 1,3,'单据B'
create table C(fID int,fTableName varchar(10),fName varchar(10))insert into C values(2,'A','A单'),(3,'B','B单')
create table D(fName varchar(10),fRelationID int,fRelationClassID int)insert into D values('aa',1,2),('bb',1,3)declare @tsql varchar(8000)select @tsql=isnull(@tsql+' union all ','') +'select '''+dfName+''' ''dfName'','''+cfName+''' ''cfName'','+s+' ''fNO'' ' from (select d.fName 'dfName',c.fName 'cfName', '(select top 1 x.fNO from '+c.fTableName+' x where x.fID='+rtrim(d.fRelationID)+') ' 's' from D d left join C c on d.fRelationClassID=c.fID) texec(@tsql)/* dfName cfName fNO ------ ------ ---------- aa A单 单据A bb B单 单据B(2 row(s) affected) */
表B (fID,fItemClassID,fNO) 1,3,'单据B'
表C (fID,fTableName,fName) (2,'A','A单')(3,'B','B单')
表D (fName,fRelationID,fRelationClassID)('aa',1,2)('bb',1,3)我想得到的查询结果是根据C表的fTableName关联A表或者B表或者其他表
Select d.fName,c.fName,(c.fTableName).fName From D
Left Join C On C .fID = D.fRelationClassIDID
Left Join (c.fTableName) On (c.fTableName).fID = d.fRelationID
Left Join A On D.fRelationID = (c.fTableName).fID
返回的结果是
aa,A单,单据A
bb,B单,单据B
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-06-25 10:58:37
-- Version:
-- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
-- Jun 17 2011 00:54:03
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[a]
if object_id('[a]') is not null drop table [a]
go
create table [a]([fID] int,[fItemClassID] int,[fNO] nvarchar(6))
insert [a]
select 1,2,N'单据A'
--> 测试数据[b]
if object_id('[b]') is not null drop table [b]
go
create table [b]([fID] int,[fItemClassID] int,[fNO] nvarchar(6))
insert [b]
select 1,3,N'单据B'
if object_id('[c]') is not null drop table c
go
CREATE TABLE c (fID INT ,fTableName nvarchar(6),fName nvarchar(6))
INSERT INTO c
values(2,'A',N'A单'),(3,'B',N'B单')
if object_id('[d]') is not null drop table d
go
CREATE TABLE D (fName nvarchar(6),fRelationID INT ,fRelationClassID INT )INSERT INTO d
values('aa',1,2),('bb',1,3)--------------生成数据--------------------------Select d.fName,c.fName,ISNULL(B.[fNO],A.[fNO])[fNO]
From c
Left Join d On C .fID = D.fRelationClassID
Left Join B On c.fID = B.[fItemClassID]
Left Join A On c.fID = A.[fItemClassID]
----------------结果----------------------------
/*
fName fName fNO
------ ------ ------
aa A单 单据A
bb B单 单据B
*/
这个里面的一个表名是我虚拟的(c.fTableName) 这个表是动态的,实际执行是报错
这个里面的表A和表B是动态的表,根据C表的fTableName字段来取的表名,不能固定的
具体是怎样跟表A,表B做关联? 在表C.fTableName的值前加个"表"字组成表名吗?
create table A(fID int,fItemClassID int,fNO varchar(10))insert into A
select 1,2,'单据A'create table B(fID int,fItemClassID int,fNO varchar(10))insert into B
select 1,3,'单据B'
create table C(fID int,fTableName varchar(10),fName varchar(10))insert into C
values(2,'A','A单'),(3,'B','B单')
create table D(fName varchar(10),fRelationID int,fRelationClassID int)insert into D
values('aa',1,2),('bb',1,3)declare @tsql varchar(8000)select @tsql=isnull(@tsql+' union all ','')
+'select '''+dfName+''' ''dfName'','''+cfName+''' ''cfName'','+s+' ''fNO'' '
from
(select d.fName 'dfName',c.fName 'cfName',
'(select top 1 x.fNO from '+c.fTableName+' x where x.fID='+rtrim(d.fRelationID)+') ' 's'
from D d
left join C c on d.fRelationClassID=c.fID) texec(@tsql)/*
dfName cfName fNO
------ ------ ----------
aa A单 单据A
bb B单 单据B(2 row(s) affected)
*/