根据一个表(Ci_Class_Attribute )中字段(FieldName)作为查询字段在另外一个表(Ci_Re_Attribute)中查询。下面的存储过程可以实现,问题是,我还需要将这些查询的东西和另外一个表中的数据join一下,我不知道怎么实现。谢谢。CREATE PROCEDURE ci_viewre_all
@Classid int,
@reid varchar(20)
AS
declare @str nvarchar(1000)
select @str='select '
select @str=@str+FieldName+',' from Ci_Class_Attribute
select @str=substring(@str,1,len(@str)-1)+' from Ci_Re_Attribute'
exec(@str)
GO
@Classid int,
@reid varchar(20)
AS
declare @str nvarchar(1000)
select @str='select '
select @str=@str+FieldName+',' from Ci_Class_Attribute
select @str=substring(@str,1,len(@str)-1)+' from Ci_Re_Attribute'
exec(@str)
GO
@Classid int,
@reid varchar(20)
AS
declare @str nvarchar(1000)
select @str=''
select @str=@str+ ',' + FieldName from Ci_Class_Attribute
select @str= stuff(@str,1,1,'')+' from Ci_Re_Attribute as a LEFT JOIN tableB as b ON a.连接列 = b.连接列'
exec(@str)
GO
@Classid int,
@reid varchar(20)
AS
declare @str nvarchar(1000)
select @str='select '
select @str=@str+FieldName+',' from Ci_Class_Attribute
select @str=substring(@str,1,len(@str)-1)+' into #t from Ci_Re_Attribute'
exec(@str) --寫進一個領時表裡
select * from #t left join 表2 on 關聯條件
drop table #t
GO
@Classid int,
@reid varchar(20)
AS
declare @str nvarchar(1000)
select @str='select a.* from 表 a'
select @str=@str+' inner join (select '
select @str=@str+FieldName+',' from Ci_Class_Attribute
select @str=substring(@str,1,len(@str)-1)+' from Ci_Re_Attribute) as t'
select @str=' on ' --连接条件
--exec(@str)
print @str
GO
@Classid int,
@reid varchar(20)
AS
declare @str nvarchar(1000)
select @str=''
select @str=@str+ ',' + FieldName from Ci_Class_Attribute
select @str= stuff(@str,1,1,'')+',b.* from Ci_Re_Attribute as a LEFT JOIN news as b ON a.连接列 = b.newsID'
exec(@str)
GO
实际上从Ci_Class_Attribute 这个表中读取所需字段也是需要从刚才说的第三个表中的链接才可以实现的。
我的表结构如下:
CREATE TABLE [dbo].[Ci_Class_Attribute] (
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[Title] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Etitle] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[FieldName] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Intro] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[Classid] [int] NOT NULL ,
[Values] [ntext] COLLATE Chinese_PRC_CI_AS NULL ,
[ValueType] [int] NULL ,
[Orders] [int] NULL ,
[IfList] [tinyint] NULL ,
[IfSo] [tinyint] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GOCREATE TABLE [dbo].[Ci_Re_Attribute] (
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[ReId] [int] NOT NULL ,
[shezhi] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[asdf] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[qwe] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[chaox] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[1111] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GOCREATE TABLE [dbo].[Ci_Resource] (
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[Title] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[ClassId] [int] NOT NULL ,
[Content] [ntext] COLLATE Chinese_PRC_CI_AS NULL ,
[AddTime] [smalldatetime] NULL ,
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
@Classid int,
@reid varchar(20)
AS
declare @str nvarchar(1000)
select @str='select '
select @str=@str+FieldName+',' from Ci_Class_Attribute A Inner Join ci_resource B On A.Classid = B.Classid
select @str=substring(@str,1,len(@str)-1)+' from Ci_Re_Attribute'
exec(@str)
GO