27,579
社区成员
发帖
与我相关
我的任务
分享
select
A.* as A_*
from QAQuestion Q inner join QAAnswer A ON A.QuestionID = Q.ID
--参考上面的,然后变了下
--查询时,动态给各列取别名
if object_id('alias') is not null
drop table alias
go
create table alias
(
id int primary key,
name nvarchar(20)
)
go
insert into alias
select 1,'张三' union all
select 2,'李四' union all
select 3,'王五' union all
select 4,'赵六'
go
select * from alias
select name from syscolumns where id = object_id('alias')
go
--动态生成语句为:
declare @sql varchar(max)
set @sql = ''
--第一种方法
--select @sql = @sql + ',' + c.name + ' as A_' + c.name
--from sys.tables t
--inner join sys.columns c
--on t.object_id = c.object_id where t.name = 'alias'
--order by c.column_id
--第二种方法
select @sql = @sql + ',' + name + ' as A_' + name from syscolumns where id = object_id('alias')
set @sql = 'select ' + STUFF(@sql,1,1,'') + ' from alias A'
print @sql
exec( @sql )
--先建个表
select * into wc_table
from sys.objects
/*
要实现
select a.* as a_*
from wc_table
的效果
*/
--动态生成语句为:
declare @sql varchar(max);
set @sql = '';
select @sql = @sql + ',' + c.name + ' as A_' + c.name
from sys.tables t
inner join sys.columns c
on t.object_id = c.object_id
where t.name = 'wc_table'
order by c.column_id
set @sql = 'select ' +
STUFF(@sql,1,1,'') +
' from wc_table A'
select @sql
/*
我把结果格式化了一下就是这样:
SELECT name AS A_name,
object_id AS A_object_id,
principal_id AS A_principal_id,
schema_id AS A_schema_id,
parent_object_id AS A_parent_object_id,
type AS A_type,
type_desc AS A_type_desc,
create_date AS A_create_date,
modify_date AS A_modify_date,
is_ms_shipped AS A_is_ms_shipped,
is_published AS A_is_published,
is_schema_published AS A_is_schema_published
FROM wc_table A
*/
exec(@sql)
这样只是中看不中用的实现方式,比原来的更加麻烦,本质上要想实现你的那种方式,必须在SQL Server端的sql要支持这种简单的写法,但到现在位置我没有看到数据库,包括sql server,oracle,mysql,db2中任何一种数据库,能实现这种写法的。
select
'A_'+A.*
from QAQuestion Q inner join QAAnswer A ON A.QuestionID = Q.ID