|----------------------|
| 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用得不熟,写不出这样的语句,望高手赐教~~
25 个解决方案
#1
select a.user_id,b.user_space
from MEMBER a,
(select user_id,user_space from mail_box_A
union
select user_id,user_space from mail_box_B
union
......
select user_id,user_space from mail_box_Z) as b
where a.userid = b.userid
似乎没有简单方法了 :-(
from MEMBER a,
(select user_id,user_space from mail_box_A
union
select user_id,user_space from mail_box_B
union
......
select user_id,user_space from mail_box_Z) as b
where a.userid = b.userid
似乎没有简单方法了 :-(
#2
查詢所有:
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 ,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
#3
不明确你的意思,不知道这样行不行
select user_id, sum(mail_size) from mail_box_x group by user_id
select user_id, sum(mail_size) from mail_box_x group by user_id
#4
to ny64(ny64):
MAIL_BOX_X是26个表来的,从MAIL_BOX_A到MAIL_BOX_Z,邮件的存放到哪一个表是根据USER_ID的头一个字母来决定的(例如MAIL_BOX_A里放的邮件信息都是以A为第一个字母的用户)
MAIL_BOX_X是26个表来的,从MAIL_BOX_A到MAIL_BOX_Z,邮件的存放到哪一个表是根据USER_ID的头一个字母来决定的(例如MAIL_BOX_A里放的邮件信息都是以A为第一个字母的用户)
#5
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
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
#6
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+"'")
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+"'")
#7
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
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
#8
俺们同意
hjhing(winding) ( )
hjhing(winding) ( )
#9
xue
#10
你们的方法都试过了,不行:(
我试过最简单的(仿照书本上的):
select "TOTALSIZE"=(
SELECT SUM(MAILSIZE) from mailbox_body_k
WHERE USER_ID=a.USER_ID
)
from member a
都报错:
服务器: 消息 446,级别 16,状态 9,行 21
无法解决 equal to 操作的排序规则冲突。
呜呜~~~~~~
我试过最简单的(仿照书本上的):
select "TOTALSIZE"=(
SELECT SUM(MAILSIZE) from mailbox_body_k
WHERE USER_ID=a.USER_ID
)
from member a
都报错:
服务器: 消息 446,级别 16,状态 9,行 21
无法解决 equal to 操作的排序规则冲突。
呜呜~~~~~~
#11
你用的是sql2000吗?
#12
to: tj_dns(愉快的登山者)
老兄又爬到五座山了
老兄又爬到五座山了
#13
to Chiff(~o~):
是的,用的是SQLServer 2000,有关系的吗?
是的,用的是SQLServer 2000,有关系的吗?
#14
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
(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
#15
不好意思前面是统计记录数。
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
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
#16
做了一个实验:
select (
select count(*) from MEMBER b where b.USER_ID = a.USER_ID
) as aaa
from member a
成功返回记录信息
select (
select count(*) from mailbox_body_k b where b.USER_ID = a.US
) as aaa
from member a
却出错了,百思不得其解。
服务器: 消息 446,级别 16,状态 9,行 20
无法解决 equal to 操作的排序规则冲突。
一个表都不行,更不用说是26个表了:(
select (
select count(*) from MEMBER b where b.USER_ID = a.USER_ID
) as aaa
from member a
成功返回记录信息
select (
select count(*) from mailbox_body_k b where b.USER_ID = a.US
) as aaa
from member a
却出错了,百思不得其解。
服务器: 消息 446,级别 16,状态 9,行 20
无法解决 equal to 操作的排序规则冲突。
一个表都不行,更不用说是26个表了:(
#17
看一下你的mailbox_body_k 表的US字段的排序规则是不是和member 表的USER_ID 字段的的排序规则不同。
导出建表语句看看!!
导出建表语句看看!!
#18
脚本如下,请过目:
CREATE TABLE [dbo].[MEMBER] (
[USER_ID] [varchar] (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[USER_PA] [varchar] (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[USER_EMAIL] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[NICK_NAME] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[GROUP_ID] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[RIGHT] [varchar] (1) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[MAILBOX_BODY_K] (
[USER_ID] [varchar] (50) COLLATE Chinese_PRC_CS_AS NOT NULL ,
[SEND_NAME] [varchar] (50) COLLATE Chinese_PRC_CS_AS NULL ,
[SEND_EMAIL] [varchar] (50) COLLATE Chinese_PRC_CS_AS NULL ,
[SEND_DATA] [varchar] (50) COLLATE Chinese_PRC_CS_AS NULL ,
[SUBJECT] [varchar] (200) COLLATE Chinese_PRC_CS_AS NULL ,
[CONTENT] [text] COLLATE Chinese_PRC_CS_AS NULL ,
[SIZE] [int] NULL ,
[MAILBOX_PATH] [varchar] (50) COLLATE Chinese_PRC_CS_AS NULL ,
[ADDRROOM] [char] (2) COLLATE Chinese_PRC_CS_AS NULL ,
[MOTELY] [varchar] (50) COLLATE Chinese_PRC_CS_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[MEMBER] (
[USER_ID] [varchar] (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[USER_PA] [varchar] (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[USER_EMAIL] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[NICK_NAME] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[GROUP_ID] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[RIGHT] [varchar] (1) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[MAILBOX_BODY_K] (
[USER_ID] [varchar] (50) COLLATE Chinese_PRC_CS_AS NOT NULL ,
[SEND_NAME] [varchar] (50) COLLATE Chinese_PRC_CS_AS NULL ,
[SEND_EMAIL] [varchar] (50) COLLATE Chinese_PRC_CS_AS NULL ,
[SEND_DATA] [varchar] (50) COLLATE Chinese_PRC_CS_AS NULL ,
[SUBJECT] [varchar] (200) COLLATE Chinese_PRC_CS_AS NULL ,
[CONTENT] [text] COLLATE Chinese_PRC_CS_AS NULL ,
[SIZE] [int] NULL ,
[MAILBOX_PATH] [varchar] (50) COLLATE Chinese_PRC_CS_AS NULL ,
[ADDRROOM] [char] (2) COLLATE Chinese_PRC_CS_AS NULL ,
[MOTELY] [varchar] (50) COLLATE Chinese_PRC_CS_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
#19
:)
#20
to:zzhuz(大件)
try below:
select (
select count(*) from mailbox_body_k b where b.[USER_ID] = a.[USER_ID] COLLATE Chinese_PRC_CI_AS
) as aaa
from member a
try below:
select (
select count(*) from mailbox_body_k b where b.[USER_ID] = a.[USER_ID] COLLATE Chinese_PRC_CI_AS
) as aaa
from member a
#21
to:zzhuz(大件)
给你提个建议
1.给 member,mailbox_body_k添加主键
2.不要使用SQL SERVER默认的字符串,如:USER_ID,SIZE...
3.MEMBER和mailbox_body_X的关系不明确
给你提个建议
1.给 member,mailbox_body_k添加主键
2.不要使用SQL SERVER默认的字符串,如:USER_ID,SIZE...
3.MEMBER和mailbox_body_X的关系不明确
#22
我觉得这样会好做些,建一个视图或建一个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
查询时就简单多了。
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
查询时就简单多了。
#23
我写SP,是根据USER_ID选择表,不知是否符合你的要求?
create proc usp_getmailsize
@user_id varchar(30)
as
BEGIN
declare @rc int,
@firstChar char(2),
@Mailbox_name varchar(30),
@str varchar(2000),
@TEMP varchar(2000)
select @rc=0
if @user_id is null
begin
select @rc=-1
return @rc
end
select @firstChar =left(@user_ID,1) where left(@user_ID,1) like '[A-Za-z]'
if @firstChar is null
begin
select @rc=-2
return @rc
end
select @Mailbox_name='mailbox_body'
select @str=@Mailbox_name+'_'+@firstChar
--print @str
SET @TEMP= 'select a.[user_id],sum(b.[size]) from member a left outer join '+ @str+' b on a.[user_id]=b.[user_id] COLLATE Chinese_PRC_CI_AS
group by a.[User_id]'
EXEC (@TEMP)
SELECT @RC=@@ERROR
IF @RC<>0
begin
select @rc=-3
return @rc
end
END
go
exec usp_getmailsize 'kest'
create proc usp_getmailsize
@user_id varchar(30)
as
BEGIN
declare @rc int,
@firstChar char(2),
@Mailbox_name varchar(30),
@str varchar(2000),
@TEMP varchar(2000)
select @rc=0
if @user_id is null
begin
select @rc=-1
return @rc
end
select @firstChar =left(@user_ID,1) where left(@user_ID,1) like '[A-Za-z]'
if @firstChar is null
begin
select @rc=-2
return @rc
end
select @Mailbox_name='mailbox_body'
select @str=@Mailbox_name+'_'+@firstChar
--print @str
SET @TEMP= 'select a.[user_id],sum(b.[size]) from member a left outer join '+ @str+' b on a.[user_id]=b.[user_id] COLLATE Chinese_PRC_CI_AS
group by a.[User_id]'
EXEC (@TEMP)
SELECT @RC=@@ERROR
IF @RC<>0
begin
select @rc=-3
return @rc
end
END
go
exec usp_getmailsize 'kest'
#24
哈哈。。。解决了where后面加上COLLATE Chinese_PRC_CI_AS 就ok了:)
这问题困了我整天。。。一会儿给分
这版的人太热情了,特别感谢Chiff(~o~)和leimin(黄山光明顶)。100分不够分,另开了张贴
http://expert.csdn.net/Expert/topic/1171/1171838.xml?temp=.6509516
Chiff(~o~)、leimin(黄山光明顶)进来拿分吧~~
这问题困了我整天。。。一会儿给分
这版的人太热情了,特别感谢Chiff(~o~)和leimin(黄山光明顶)。100分不够分,另开了张贴
http://expert.csdn.net/Expert/topic/1171/1171838.xml?temp=.6509516
Chiff(~o~)、leimin(黄山光明顶)进来拿分吧~~
#25
应该考虑一下icevi(按钮工厂) 的方法!
#1
select a.user_id,b.user_space
from MEMBER a,
(select user_id,user_space from mail_box_A
union
select user_id,user_space from mail_box_B
union
......
select user_id,user_space from mail_box_Z) as b
where a.userid = b.userid
似乎没有简单方法了 :-(
from MEMBER a,
(select user_id,user_space from mail_box_A
union
select user_id,user_space from mail_box_B
union
......
select user_id,user_space from mail_box_Z) as b
where a.userid = b.userid
似乎没有简单方法了 :-(
#2
查詢所有:
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 ,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
#3
不明确你的意思,不知道这样行不行
select user_id, sum(mail_size) from mail_box_x group by user_id
select user_id, sum(mail_size) from mail_box_x group by user_id
#4
to ny64(ny64):
MAIL_BOX_X是26个表来的,从MAIL_BOX_A到MAIL_BOX_Z,邮件的存放到哪一个表是根据USER_ID的头一个字母来决定的(例如MAIL_BOX_A里放的邮件信息都是以A为第一个字母的用户)
MAIL_BOX_X是26个表来的,从MAIL_BOX_A到MAIL_BOX_Z,邮件的存放到哪一个表是根据USER_ID的头一个字母来决定的(例如MAIL_BOX_A里放的邮件信息都是以A为第一个字母的用户)
#5
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
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
#6
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+"'")
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+"'")
#7
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
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
#8
俺们同意
hjhing(winding) ( )
hjhing(winding) ( )
#9
xue
#10
你们的方法都试过了,不行:(
我试过最简单的(仿照书本上的):
select "TOTALSIZE"=(
SELECT SUM(MAILSIZE) from mailbox_body_k
WHERE USER_ID=a.USER_ID
)
from member a
都报错:
服务器: 消息 446,级别 16,状态 9,行 21
无法解决 equal to 操作的排序规则冲突。
呜呜~~~~~~
我试过最简单的(仿照书本上的):
select "TOTALSIZE"=(
SELECT SUM(MAILSIZE) from mailbox_body_k
WHERE USER_ID=a.USER_ID
)
from member a
都报错:
服务器: 消息 446,级别 16,状态 9,行 21
无法解决 equal to 操作的排序规则冲突。
呜呜~~~~~~
#11
你用的是sql2000吗?
#12
to: tj_dns(愉快的登山者)
老兄又爬到五座山了
老兄又爬到五座山了
#13
to Chiff(~o~):
是的,用的是SQLServer 2000,有关系的吗?
是的,用的是SQLServer 2000,有关系的吗?
#14
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
(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
#15
不好意思前面是统计记录数。
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
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
#16
做了一个实验:
select (
select count(*) from MEMBER b where b.USER_ID = a.USER_ID
) as aaa
from member a
成功返回记录信息
select (
select count(*) from mailbox_body_k b where b.USER_ID = a.US
) as aaa
from member a
却出错了,百思不得其解。
服务器: 消息 446,级别 16,状态 9,行 20
无法解决 equal to 操作的排序规则冲突。
一个表都不行,更不用说是26个表了:(
select (
select count(*) from MEMBER b where b.USER_ID = a.USER_ID
) as aaa
from member a
成功返回记录信息
select (
select count(*) from mailbox_body_k b where b.USER_ID = a.US
) as aaa
from member a
却出错了,百思不得其解。
服务器: 消息 446,级别 16,状态 9,行 20
无法解决 equal to 操作的排序规则冲突。
一个表都不行,更不用说是26个表了:(
#17
看一下你的mailbox_body_k 表的US字段的排序规则是不是和member 表的USER_ID 字段的的排序规则不同。
导出建表语句看看!!
导出建表语句看看!!
#18
脚本如下,请过目:
CREATE TABLE [dbo].[MEMBER] (
[USER_ID] [varchar] (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[USER_PA] [varchar] (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[USER_EMAIL] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[NICK_NAME] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[GROUP_ID] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[RIGHT] [varchar] (1) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[MAILBOX_BODY_K] (
[USER_ID] [varchar] (50) COLLATE Chinese_PRC_CS_AS NOT NULL ,
[SEND_NAME] [varchar] (50) COLLATE Chinese_PRC_CS_AS NULL ,
[SEND_EMAIL] [varchar] (50) COLLATE Chinese_PRC_CS_AS NULL ,
[SEND_DATA] [varchar] (50) COLLATE Chinese_PRC_CS_AS NULL ,
[SUBJECT] [varchar] (200) COLLATE Chinese_PRC_CS_AS NULL ,
[CONTENT] [text] COLLATE Chinese_PRC_CS_AS NULL ,
[SIZE] [int] NULL ,
[MAILBOX_PATH] [varchar] (50) COLLATE Chinese_PRC_CS_AS NULL ,
[ADDRROOM] [char] (2) COLLATE Chinese_PRC_CS_AS NULL ,
[MOTELY] [varchar] (50) COLLATE Chinese_PRC_CS_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[MEMBER] (
[USER_ID] [varchar] (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[USER_PA] [varchar] (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[USER_EMAIL] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[NICK_NAME] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[GROUP_ID] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[RIGHT] [varchar] (1) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[MAILBOX_BODY_K] (
[USER_ID] [varchar] (50) COLLATE Chinese_PRC_CS_AS NOT NULL ,
[SEND_NAME] [varchar] (50) COLLATE Chinese_PRC_CS_AS NULL ,
[SEND_EMAIL] [varchar] (50) COLLATE Chinese_PRC_CS_AS NULL ,
[SEND_DATA] [varchar] (50) COLLATE Chinese_PRC_CS_AS NULL ,
[SUBJECT] [varchar] (200) COLLATE Chinese_PRC_CS_AS NULL ,
[CONTENT] [text] COLLATE Chinese_PRC_CS_AS NULL ,
[SIZE] [int] NULL ,
[MAILBOX_PATH] [varchar] (50) COLLATE Chinese_PRC_CS_AS NULL ,
[ADDRROOM] [char] (2) COLLATE Chinese_PRC_CS_AS NULL ,
[MOTELY] [varchar] (50) COLLATE Chinese_PRC_CS_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
#19
:)
#20
to:zzhuz(大件)
try below:
select (
select count(*) from mailbox_body_k b where b.[USER_ID] = a.[USER_ID] COLLATE Chinese_PRC_CI_AS
) as aaa
from member a
try below:
select (
select count(*) from mailbox_body_k b where b.[USER_ID] = a.[USER_ID] COLLATE Chinese_PRC_CI_AS
) as aaa
from member a
#21
to:zzhuz(大件)
给你提个建议
1.给 member,mailbox_body_k添加主键
2.不要使用SQL SERVER默认的字符串,如:USER_ID,SIZE...
3.MEMBER和mailbox_body_X的关系不明确
给你提个建议
1.给 member,mailbox_body_k添加主键
2.不要使用SQL SERVER默认的字符串,如:USER_ID,SIZE...
3.MEMBER和mailbox_body_X的关系不明确
#22
我觉得这样会好做些,建一个视图或建一个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
查询时就简单多了。
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
查询时就简单多了。
#23
我写SP,是根据USER_ID选择表,不知是否符合你的要求?
create proc usp_getmailsize
@user_id varchar(30)
as
BEGIN
declare @rc int,
@firstChar char(2),
@Mailbox_name varchar(30),
@str varchar(2000),
@TEMP varchar(2000)
select @rc=0
if @user_id is null
begin
select @rc=-1
return @rc
end
select @firstChar =left(@user_ID,1) where left(@user_ID,1) like '[A-Za-z]'
if @firstChar is null
begin
select @rc=-2
return @rc
end
select @Mailbox_name='mailbox_body'
select @str=@Mailbox_name+'_'+@firstChar
--print @str
SET @TEMP= 'select a.[user_id],sum(b.[size]) from member a left outer join '+ @str+' b on a.[user_id]=b.[user_id] COLLATE Chinese_PRC_CI_AS
group by a.[User_id]'
EXEC (@TEMP)
SELECT @RC=@@ERROR
IF @RC<>0
begin
select @rc=-3
return @rc
end
END
go
exec usp_getmailsize 'kest'
create proc usp_getmailsize
@user_id varchar(30)
as
BEGIN
declare @rc int,
@firstChar char(2),
@Mailbox_name varchar(30),
@str varchar(2000),
@TEMP varchar(2000)
select @rc=0
if @user_id is null
begin
select @rc=-1
return @rc
end
select @firstChar =left(@user_ID,1) where left(@user_ID,1) like '[A-Za-z]'
if @firstChar is null
begin
select @rc=-2
return @rc
end
select @Mailbox_name='mailbox_body'
select @str=@Mailbox_name+'_'+@firstChar
--print @str
SET @TEMP= 'select a.[user_id],sum(b.[size]) from member a left outer join '+ @str+' b on a.[user_id]=b.[user_id] COLLATE Chinese_PRC_CI_AS
group by a.[User_id]'
EXEC (@TEMP)
SELECT @RC=@@ERROR
IF @RC<>0
begin
select @rc=-3
return @rc
end
END
go
exec usp_getmailsize 'kest'
#24
哈哈。。。解决了where后面加上COLLATE Chinese_PRC_CI_AS 就ok了:)
这问题困了我整天。。。一会儿给分
这版的人太热情了,特别感谢Chiff(~o~)和leimin(黄山光明顶)。100分不够分,另开了张贴
http://expert.csdn.net/Expert/topic/1171/1171838.xml?temp=.6509516
Chiff(~o~)、leimin(黄山光明顶)进来拿分吧~~
这问题困了我整天。。。一会儿给分
这版的人太热情了,特别感谢Chiff(~o~)和leimin(黄山光明顶)。100分不够分,另开了张贴
http://expert.csdn.net/Expert/topic/1171/1171838.xml?temp=.6509516
Chiff(~o~)、leimin(黄山光明顶)进来拿分吧~~
#25
应该考虑一下icevi(按钮工厂) 的方法!