很久没来,问一问题,顺便放点血

[zhangzs8896]

有两个表:
Sys_WFStatistics和Sys_Emplyee
其中Sys_WFStatistics中有字段StatisticsID,int类型
Sys_Emplyee中有字段StatisticsReports 类型varchar,存放: "'1','2','3'"
现在需要找出Sys_WFStatistics中StatisticsID在StatisticsReports中的记录
我这样写的:
select SortID,StatisticsID,StatisticsName,StatisticsUrl,Remark from Sys_WFStatistics where ''+Convert(varchar,StatisticsID)+'' in ( select StatisticsReports from Sys_Emplyee where EmpAccount='NEWSIGHT\zhangzhongsheng')

查询不到数据.
但是这样
select SortID,StatisticsID,StatisticsName,StatisticsUrl,Remark from Sys_WFStatistics where ''+Convert(varchar,StatisticsID)+'' in ( '1','2','3' )
是可以的.

寻求解决办法,效率越高越好!

[wgsasd311]

select SortID,StatisticsID,StatisticsName,StatisticsUrl,Remark from Sys_WFStatistics where StatisticsID in ( select StatisticsReports from Sys_Emplyee where EmpAccount='NEWSIGHT\zhangzhongsheng')

[vivianfdlpw]

select SortID
,StatisticsID
,StatisticsName
,StatisticsUrl
,Remark
from Sys_WFStatistics
where exists (select 1
from Sys_Emplyee
where EmpAccount='NEWSIGHT\zhangzhongsheng'
and charindex(','+Convert(varchar,StatisticsID)+','
,
','+StatisticsReports+',')>0
)

[wgsasd311]

或者
select SortID,StatisticsID,StatisticsName,StatisticsUrl,Remark from Sys_WFStatistics where StatisticsID in ( select cast(StatisticsReports as varchar) from Sys_Emplyee where EmpAccount='NEWSIGHT\zhangzhongsheng')

[zhangzs8896]

不行,类型转换不过去的.
2楼哥们结果跟我一样，无记录

[vivianfdlpw]

try:

select SortID
,StatisticsID
,StatisticsName
,StatisticsUrl
,Remark
from Sys_WFStatistics
where exists (select 1
from Sys_Emplyee
where EmpAccount='NEWSIGHT\zhangzhongsheng'
and charindex(','''+Convert(varchar,StatisticsID)+''','
,
','''+StatisticsReports+''',')>0
)

[vivianfdlpw]

create table Sys_WFStatistics
(
StatisticsID ID,
SortID int default 0,
StatisticsName varchar(20) default '',
StatisticsUrl varchar(20) default '',
Remark varchar(100) default ''
)

create table Sys_Emplyee
(
StatisticsReports varchar(20),
EmpAccount varchar(100)
)
insert Sys_WFStatistics(StatisticsID)
select 1 union
select 2 union
select 3
insert Sys_Emplyee
select '''1'',''2'',''3''','NEWSIGHT\zhangzhongsheng'
go


--查询
select SortID
,StatisticsID
,StatisticsName
,StatisticsUrl
,Remark
from Sys_WFStatistics
where exists (select 1
from Sys_Emplyee
where EmpAccount='NEWSIGHT\zhangzhongsheng'
and charindex(','''+Convert(varchar,StatisticsID)+''','
,
','''+StatisticsReports+''',')>0
)

drop table Sys_WFStatistics,Sys_Emplyee


/*--结果
SortID StatisticsID StatisticsName StatisticsUrl Remark
----------- ------------ -------------------- -------------------- -------
0 2

(1 row(s) affected)

--*/

[vivianfdlpw]

create table Sys_WFStatistics
(
StatisticsID ID,
SortID int default 0,
StatisticsName varchar(20) default '',
StatisticsUrl varchar(20) default '',
Remark varchar(100) default ''
)

create table Sys_Emplyee
(
StatisticsReports varchar(20),
EmpAccount varchar(100)
)
insert Sys_WFStatistics(StatisticsID)
select 1 union
select 2 union
select 3
insert Sys_Emplyee
select '''1'',''2'',''3''','NEWSIGHT\zhangzhongsheng'
go


--查询
select SortID
,StatisticsID
,StatisticsName
,StatisticsUrl
,Remark
from Sys_WFStatistics
where exists (select 1
from Sys_Emplyee
where EmpAccount='NEWSIGHT\zhangzhongsheng'
and charindex(','''+Convert(varchar,StatisticsID)+''','
,
','+StatisticsReports+',')>0
)

drop table Sys_WFStatistics,Sys_Emplyee


/*--结果
SortID StatisticsID StatisticsName StatisticsUrl Remark
----------- ------------ -------------------- -------------------- --------
0 1
0 2
0 3

(3 row(s) affected)

--*/

[zhangzs8896]

呵呵,已经搞定了,方法跟 vivianfdlpw()差不多的.