我要把邮箱用户和他邮箱的已使用容量显示出来，如图:
¦---------------------- ¦
¦ USER_ID ¦ USED_SPACE ¦
¦ ken 1024 ¦
¦ jet 56314 ¦
¦ xin 8651 ¦
.
.
.
请问我怎样能用一条SQL语句得到上图所显示的效果呢？
其中数据里：
表MEMBER中存放了邮箱用户的信息（有字段USER_ID),一个用户一条记录；
表MAIL_BOX_X 用来存放用户邮件的信息(有字段USER_ID和MAIL_SIZE)，一封邮件一条记录。其中表名中的x代表一个A到Z的英文字母，所以这里共26个表。例如用户ken的邮件信息就存放在MAIL_BOX_K里，用户jet的邮件信息就存放在MAIL_BOX_J里
我想做法应该是从MEMBER表里得到USER_ID,判断USER_ID的第一个字母到出相应的MAIL_BOX_X,再用类似select count(*) from MAIL_BOX_X where USER_ID =...
由于SQL Server用得不熟，写不出这样的语句，望高手赐教~~
---------------------------------------------------------------
查詢所有：
select userid ,count() from mail_box_a group by userid
union
select userid ,count() from mail_box_b group by userid
union
select userid ,count() from mail_box_c group by userid
union
select userid ,count() from mail_box_d group by userid
union
select userid ,count(*) from mail_box_e group by userid
......
union
select userid ,count(*) from mail_box_z group by userid
---------------------------------------------------------------
select userid ,count() from mail_box_a group by userid
union
select userid ,count() from mail_box_b group by userid
union
select userid ,sum(mail_size) from mail_box_c group by userid
union
select userid ,sum(mail_size) from mail_box_d group by userid
union
select userid ,sum(mail_size) from mail_box_e group by userid
......
union
select userid ,sum(mail_size) from mail_box_z group by userid
---------------------------------------------------------------
1。使用联合：
select USER_ID, sum(MAIL_SIZE) as USED_SPACE from
(select USER_ID, MAIL_SIZE from MAIL_BOX_A
union select USER_ID, MAIL_SIZE from MAIL_BOX_B
......
union select USER_ID, MAIL_SIZE from MAIL_BOX_Z
)
group by USER_ID
2。使用动态SQL执行：
declare @X char(1)
declare @userid char(10)
select USER_ID into userid from MEMBER where ....
set @X = left(userid, 1)
exec("select USER_ID, sum(MAIL_SIZE) as USED_SPACE from MAIL_BOX_"+@X+" where USER_ID = '"+@userid+"'")
---------------------------------------------------------------
declare @exeStr varchar(4000)
declare @s char(1)
set @s='A'
set @exeStr='select a.user_id,b.user_space from mail a, ( '
while @s<='Z'
begin
set @exeStr=@exeStr+'select userid,count(*) from mail_box_'+@s+' group by userid '
set @a=char(ascii(@a)+1)
end
set @exeStr=@exeStr+' ) b'
exec (@exeStr)
set
end
---------------------------------------------------------------
create table #table
(user_id varchar(100),user_space float(18) )
insert into #table
select user_id,count() from MAIL_BOX_a group by user_id
.
.
.
insert into #table
select user_id,count() from MAIL_BOX_X group by user_id
select * from #table
drop table #table
---------------------------------------------------------------
不好意思前面是统计记录数。
create table #table
(user_id varchar(100),user_space float(18) )
insert into #table
select user_id,sum(MAIL_SIZE) from MAIL_BOX_a group by user_id
.
.
.
insert into #table
select user_id,sum(MAIL_SIZE) from MAIL_BOX_X group by user_id
select * from #table
drop table #table
---------------------------------------------------------------
看一下你的mailbox_body_k 表的US字段的排序规则是不是和member 表的USER_ID 字段的的排序规则不同。
导出建表语句看看！！
我觉得这样会好做些，建一个视图或建一个UDF。比如视图：
select top 100 percent user_id,mail_size from
(
select user_id,mail_size from mail_box_a
union all
select user_id,mail_size from mail_box_b
union all
select user_id,mail_size from mail_box_c
....
) t0
查询时就简单多了。