--测试数据
create table MEMBER(USER_ID varchar(100))
insert MEMBER
select 'ken' union all
select 'jet' union all
select 'xin'create table MAIL_BOX_k(USER_ID varchar(100), MAIL_SIZE int)
insert MAIL_BOX_k
select 'ken', 1000 union all
select 'ken', 1200create table MAIL_BOX_j(USER_ID varchar(100), MAIL_SIZE int)
insert MAIL_BOX_j
select 'jet', 2000 union all
select 'jet', 2200create table MAIL_BOX_x(USER_ID varchar(100), MAIL_SIZE int)
insert MAIL_BOX_x
select 'xin', 3000 union all
select 'xin', 3200create table MAIL_BOX_z(USER_ID varchar(100), MAIL_SIZE int)--查询
declare @X varchar(100)
declare @sql varchar(100)
select @X = left(USER_ID, 1) from MEMBER -- 取整列第一个英文字母
select @sql='select USER_ID, sum(MAIL_SIZE) as USED_SPACE from MAIL_BOX_'+@X+' group by USER_ID'
print(@sql)
exec(@sql)结果:
USER_ID USED_SPACE
xin 6200但我想的结果是:
USER_ID USED_SPACE
ken 2200
jet 4200
xin 6200大乌龟快来帮帮忙啊,郁闷一个晚上
create table MEMBER(USER_ID varchar(100))
insert MEMBER
select 'ken' union all
select 'jet' union all
select 'xin'create table MAIL_BOX_k(USER_ID varchar(100), MAIL_SIZE int)
insert MAIL_BOX_k
select 'ken', 1000 union all
select 'ken', 1200create table MAIL_BOX_j(USER_ID varchar(100), MAIL_SIZE int)
insert MAIL_BOX_j
select 'jet', 2000 union all
select 'jet', 2200create table MAIL_BOX_x(USER_ID varchar(100), MAIL_SIZE int)
insert MAIL_BOX_x
select 'xin', 3000 union all
select 'xin', 3200create table MAIL_BOX_z(USER_ID varchar(100), MAIL_SIZE int)--查询
declare @X varchar(100)
declare @sql varchar(100)
select @X = left(USER_ID, 1) from MEMBER -- 取整列第一个英文字母
select @sql='select USER_ID, sum(MAIL_SIZE) as USED_SPACE from MAIL_BOX_'+@X+' group by USER_ID'
print(@sql)
exec(@sql)结果:
USER_ID USED_SPACE
xin 6200但我想的结果是:
USER_ID USED_SPACE
ken 2200
jet 4200
xin 6200大乌龟快来帮帮忙啊,郁闷一个晚上
insert MEMBER
select 'ken' union all
select 'jet' union all
select 'xin'create table MAIL_BOX_k(USER_ID varchar(100), MAIL_SIZE int)
insert MAIL_BOX_k
select 'ken', 1000 union all
select 'ken', 1200create table MAIL_BOX_j(USER_ID varchar(100), MAIL_SIZE int)
insert MAIL_BOX_j
select 'jet', 2000 union all
select 'jet', 2200create table MAIL_BOX_x(USER_ID varchar(100), MAIL_SIZE int)
insert MAIL_BOX_x
select 'xin', 3000 union all
select 'xin', 3200create table MAIL_BOX_z(USER_ID varchar(100), MAIL_SIZE int)--查询
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+' select USER_ID, sum(MAIL_SIZE) as USED_SPACE from MAIL_BOX_'+left(USER_ID, 1)+' group by USER_ID union all ' from MEMBER
select @sql=left(@sql,len(@sql)-10)
print(@sql)
exec(@sql)
USER_ID USED_SPACE
ken 2200
jet 4200
xin 6200
好象错了,他每次都select后付值,所以最后@X = x
select @sql=@sql+' select USER_ID, sum(MAIL_SIZE) as USED_SPACE from MAIL_BOX_'+left(USER_ID, 1)+' group by USER_ID union all ' from MEMBER 这个方法我好象也试过,不过我想知道,from MAIL_BOX_'+left(USER_ID, 1)+'
这里怎么才可以用变量去做,即from MAIL_BOX_'+@X+'???
insert MEMBER
select 'ken' union all
select 'jet' union all
select 'xin'create table MAIL_BOX_k(USER_ID varchar(100), MAIL_SIZE int)
insert MAIL_BOX_k
select 'ken', 1000 union all
select 'ken', 1200create table MAIL_BOX_j(USER_ID varchar(100), MAIL_SIZE int)
insert MAIL_BOX_j
select 'jet', 2000 union all
select 'jet', 2200create table MAIL_BOX_x(USER_ID varchar(100), MAIL_SIZE int)
insert MAIL_BOX_x
select 'xin', 3000 union all
select 'xin', 3200create table MAIL_BOX_z(USER_ID varchar(100), MAIL_SIZE int)--查询
declare @sql varchar(8000),@x varchar(1000)
select @sql='',@x=''
select @x=@x + ',' + left(user_id,1) from member
select @sql=replace(@x,',',' group by USER_ID union all select USER_ID, sum(MAIL_SIZE) as USED_SPACE from MAIL_BOX_')
select @sql=stuff(@sql+' group by user_id',1,28,'')
exec(@sql)
drop table member,mail_box_k,mail_box_j,mail_box_x,mail_box_z
--marco08(天道酬勤)你好象今天升级了耶...恭喜
--结贴