SELECT
A.GeneralID,
A.TableName,
A.Title
B.Content,
FROM
dbo.PE_CommonModel A
INNER JOIN dbo.PE_U_Article B ON (A.GeneralID = B.ID)
WHERE
A.Title LIKE '%美女%'
上述代码 要求存储过程直接输入个如 美女 的字符串,能够查询出结果.重要一点 B表的名称是不确定的, 但一定是 A表中取出的TableName 希望老师们多多帮忙,我自己写了很久,不是这里错就是那里报错,要么就不出结果.因为菜鸟自己写的就不贴出来了
解决方案 »
- 我有一个固定ip放在互联网上做服务器,现在需要限制只有部分的电脑才能访问,请问有什么解决方案
- 查询:2006-1-5这一天随便什么时候都能查询到日期字段1到日期字段2之间2006-1-5这一天的所有数据
- ACCESS窗体文本框(字段)与插入对象EXCEL图表单元格绑定问题!
- 求助关于SQL语句的查询
- 这个SQL 怎么查
- 如何将SQL7.0中的日期01/01/1900 00:00:00 显示为空白?
- sql空值问题
- 怎麼判斷一個臨時表是否存在?
- 怎样知道一个字段是否是自增长字段?
- SQL SEVER身份无法连接到服务器
- 本机上同时开SQL SERVER2000和SQL SERVER2005,那么他们的端口应该是哪个啊?或者是应该怎么查看??
- 用powerDesingner 12.0设计数据库中的疑惑
A.GeneralID,
A.TableName,
A.Title
B.Content,
FROM
dbo.PE_CommonModel A
INNER JOIN dbo.PE_U_Article B ON (A.GeneralID = B.ID)
WHERE
A.Title LIKE '%'+@Like+'%'
-------------
没理解这句
set @Like = '美女'declare @TableName sysname
set @TableName = 'dbo.PE_U_Article'exec
('
SELECT
A.GeneralID,
A.TableName,
A.Title
B.Content,
FROM
dbo.PE_CommonModel A
INNER JOIN '+@TableName+' B ON (A.GeneralID = B.ID)
WHERE
A.Title LIKE ''%'+@Like+'%''
')
as
exec('
SELECT
A.GeneralID,
A.TableName,
A.Title,
B.Content
into tmp
FROM
dbo.PE_CommonModel A
INNER JOIN ' + @tbname + ' B ON (A.GeneralID = B.ID)
WHERE
charindex(''' + @title + ''' , A.title) > 0 select * from tmp ' )
goexec my_proc '美女' , 'dbo.PE_U_Article'
drop procedure my_proc
drop table tmp
@tbname varchar(50),
@like varchar(50)
as
begin
select '
SELECT
A.GeneralID,
A.TableName,
A.Title,
B.Content
FROM
dbo.PE_CommonModel A
INNER JOIN '+@tbname+' B ON (A.GeneralID = B.ID)
WHERE
A.Title LIKE ''%'+@like+'%'''
end
A.GeneralID,
A.TableName,
A.Title
FROM
dbo.PE_CommonModel A
WHERE
A.Title LIKE '%美女%'结果
ID TableName Title
10 PE_U_Article 美女哗啦啦
11 PE_U_Article 有一个美女哗啦啦
13 PE_U_Article 又有一个美女哗啦啦
14 PE_U_News 还有一个美女哗啦啦也就是说 从 PE_CommonModel中取出 TableName 正好是 要INNER JOIN 表名
如下面的,通过JOIN 取出B的ContentSELECT
A.GeneralID,
A.TableName,
A.Title,
B.Content
FROM
PE_CommonModel A
INNER JOIN PE_U_Article B ON (A.GeneralID = B.ID)
WHERE
A.Title LIKE '%美女%'
因为需要JOIN的表名不确定啊, 他是从 A 表中取出的TableName 结果如果对应的那条是 PE_U_News 那就JOIN PE_U_News 了.也就是说 A 表中 取出的TableName结果 需要跟B表 JOIN 以方便取出 B表的Content
但是A表中 可能取出 PE_U_News 也可能取出PE_U_Article 也可能取出 PE_U_Photo
--测试表
if object_id('tempdb.dbo.#t1') is not null drop table #t1
if object_id('tempdb.dbo.#t2') is not null drop table #t2
create table #t1
(
GeneralID int ,
TableName varchar(100),
Title varchar(100),
Content varchar(100)
)
insert #t1(GeneralID,TableName,Title,Content)
select 1,'tb1','美女','Content1' union all
select 2,'tb2','帅哥','Content2' union all
select 3,'tb2','帅哥2','Content2' union all
select 4,'tb2','美女2','Content2' create table #t2
(
GeneralID int ,
TableName varchar(100),
Title varchar(100),
Content varchar(100)
)
declare @tempid int,@temptablename varchar(100),@temptitle varchar(100),@tempcontent varchar(100)
declare c1 cursor for
select * FROM #t1 WHERE Title LIKE '%美女%'
open c1
fetch next from c1 into @tempid,@temptablename,@temptitle,@tempcontent
while @@fetch_status=0
begin
declare @returnnum int
declare @sqlstr nvarchar(200)
set @sqlstr=N'select @returnnum=count(a.GeneralID) from #t1 A INNER JOIN '+@temptablename+' B ON (A.GeneralID = B.ID)'
execute sp_executesql @sqlstr,N'@returnnum int out',@returnnum out
if @returnnum>0
insert into #t2(GeneralID,TableName,Title,Content) values(@tempid,@temptablename,@temptitle,@tempcontent)
fetch next from c1 into @tempid,@temptablename,@temptitle,@tempcontent
end
select * from #t2
close c1
deallocate c1
drop table #t1
drop table #t2
declare @sql nvarchar(8000)
declare @i int
declare @count int
declare @tablename nvarchar(200)
select identity(1,1) id , TableName into #temp from dbo.PE_CommonModel
select @count =max(id) from #temp
set @i =1
while(@i<=@count)
begin
select @tablename = TableName from #temp where id = @i
if @createviewsql ='' set @createviewsql = ' SELECT ID,Content FROM ' + @tablename
set @createviewsql = @createviewsql + ' UNION ALL SELECT ID,Content FROM ' + @tablename
set @i = @i + 1
end
set @createviewsql = 'create view test_vew as ' + @createviewsql
exec (@createviewsql)set @sql =' SELECT
A.GeneralID,
A.TableName,
A.Title
B.Content,
FROM
dbo.PE_CommonModel A
INNER JOIN test_vew B ON (A.GeneralID = B.ID)
WHERE
A.Title LIKE ''%美女%'' '
exec (@sql)
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (GeneralID int,TableName varchar(12),Title varchar(18))
insert into #T
select 10,'PE_U_Article','美女哗啦啦' union all
select 11,'PE_U_Article','有一个美女哗啦啦' union all
select 13,'PE_U_Article','又有一个美女哗啦啦' union all
select 14,'PE_U_News','还有一个美女哗啦啦'declare @Like varchar(100)
set @Like = '美女'declare @SQL varchar(max)
select @SQL = isnull(@SQL + ' union all'+char(10), '')+'SELECT A.GeneralID,A.TableName,A.Title,B.Content FROM PE_CommonModel A INNER JOIN '+TableName+' B ON (A.GeneralID = B.ID) WHERE A.Title LIKE ''%'+@Like+'%''' from #T where Title like '%'+@Like+'%' group by TableName
print (@SQL)
/*
SELECT A.GeneralID,A.TableName,A.Title,B.Content FROM PE_CommonModel A INNER JOIN PE_U_Article B ON (A.GeneralID = B.ID) WHERE A.Title LIKE '%美女%' union all
SELECT A.GeneralID,A.TableName,A.Title,B.Content FROM PE_CommonModel A INNER JOIN PE_U_News B ON (A.GeneralID = B.ID) WHERE A.Title LIKE '%美女%'
*/exec (@SQL)
修改如下:--测试表
if object_id('tempdb.dbo.#t1') is not null drop table #t1
if object_id('tempdb.dbo.#t2') is not null drop table #t2
create table #t1
(
GeneralID int ,
TableName varchar(100),
Title varchar(100)
)
insert #t1(GeneralID,TableName,Title)
select 1,'tb1','美女' union all
select 2,'tb2','帅哥' union all
select 3,'tb2','帅哥2' union all
select 4,'tb2','美女2'create table #t2
(
GeneralID int ,
TableName varchar(100),
Title varchar(100),
Content varchar(100)
)
declare @tempid int,@temptablename varchar(100),@temptitle varchar(100),@tempcontent varchar(100)
declare c1 cursor for
select * FROM #t1 WHERE Title LIKE '%美女%'
open c1
fetch next from c1 into @tempid,@temptablename,@temptitle
while @@fetch_status=0
begin
declare @returnnum int,@content varchar(100)
declare @sqlstr nvarchar(200)
set @sqlstr=N'select @returnnum=a.GeneralID,@content=B.thedate from #t1 A INNER JOIN '+@temptablename+' B ON (A.GeneralID = B.ID)'
execute sp_executesql @sqlstr,N'@returnnum int out ,@content varchar(100) out',@returnnum out, @content out
if @returnnum>0
insert into #t2(GeneralID,TableName,Title,Content) values(@tempid,@temptablename,@temptitle,@content)
fetch next from c1 into @tempid,@temptablename,@temptitle
end
select * from #t2
close c1
deallocate c1
drop table #t1
drop table #t2
--测试表
if object_id('tempdb.dbo.#t1') is not null drop table #t1
if object_id('tempdb.dbo.#t2') is not null drop table #t2
create table #t1
(
GeneralID int ,
TableName varchar(100),
Title varchar(100)
)
insert #t1(GeneralID,TableName,Title)
select 1,'tb1','美女' union all
select 2,'tb2','帅哥' union all
select 3,'tb2','帅哥2' union all
select 4,'tb2','美女2'create table #t2
(
GeneralID int ,
TableName varchar(100),
Title varchar(100),
Content varchar(100)
)
declare @tempid int,@temptablename varchar(100),@temptitle varchar(100),@tempcontent varchar(100)
declare c1 cursor for
select * FROM #t1 WHERE Title LIKE '%美女%'
open c1
fetch next from c1 into @tempid,@temptablename,@temptitle
while @@fetch_status=0
begin
declare @returnnum int,@content varchar(100)
declare @sqlstr nvarchar(200)
set @sqlstr=N'select @returnnum=a.GeneralID,@content=B.thedate from #t1 A INNER JOIN '+@temptablename+' B ON (A.GeneralID = B.ID) where a.GeneralID='+convert(varchar(10),@tempid)
execute sp_executesql @sqlstr,N'@returnnum int out ,@content varchar(100) out',@returnnum out, @content out if @returnnum>0
insert into #t2(GeneralID,TableName,Title,Content) values(@tempid,@temptablename,@temptitle,@content)
fetch next from c1 into @tempid,@temptablename,@temptitle
end
select * from #t2
close c1
deallocate c1
drop table #t1
drop table #t2