现在有个这样的问题:
在XML中SQL条件是:
where projectid in (@projectid)
我的想法是叫它变成下面这个样子:
where projectid in ('1001','1002','1003')
我这时候在C#程序中对@projectid进行赋值为
string ProjectID = "1001,1002,1003"; //原始数据
string @projectid = ProjectID.Replace(",", "\",\"");
这个时候得到的@projectid好象为@projectid = "1001","1002","1003"
但是这个样子好象不行,不知道为什么?
在XML中SQL条件是:
where projectid in (@projectid)
我的想法是叫它变成下面这个样子:
where projectid in ('1001','1002','1003')
我这时候在C#程序中对@projectid进行赋值为
string ProjectID = "1001,1002,1003"; //原始数据
string @projectid = ProjectID.Replace(",", "\",\"");
这个时候得到的@projectid好象为@projectid = "1001","1002","1003"
但是这个样子好象不行,不知道为什么?
select * from 表
where charindex(','+ltrim(projectid )+',',','+@projectid+',')>0
------------------------
老兄你不是开玩笑吧-_-!
@projectid='1001,1002,1003' 是projectid的值为1001,1002,1003
和我要的projectid='1001' or projectid = '1002' or projectid = '1003'
能一样吗?晕
------------------------
老兄你不是开玩笑吧-_-!
@projectid='1001,1002,1003' 是projectid的值为1001,1002,1003
和我要的projectid='1001' or projectid = '1002' or projectid = '1003'
能一样吗?晕
--------------
这两个SQL语句,执行有什么不一样吗???
declare @t table(id int,projectid int)
insert into @t select 1,1001
union all select 2,1002
union all select 3,1003
union all select 4,1004select * from @t where charindex(','+ltrim(projectid)+',',',1001,1002,1003,')>0select * from @t where projectid='1001' or projectid = '1002' or projectid = '1003'
/*(所影响的行数为 4 行)id projectid
----------- -----------
1 1001
2 1002
3 1003(所影响的行数为 3 行)id projectid
----------- -----------
1 1001
2 1002
3 1003(所影响的行数为 3 行)*/
-------------------------------------------
不行的,兄弟
改為:
string @projectid = "'" + ProjectID.Replace(",", "\',\'") + "'";
改為:
string @projectid = "'" + ProjectID.Replace(",", "\',\'") + "'";
--------------------------------------------------------------
不行的兄弟。
还有zlp321002我不知道你想说什么。
set @ProjectID='1001,1002,1003'
select * from @t where charindex(','+ltrim(projectid)+',',','+@ProjectID+',')>0/*
id projectid
----------- -----------
1 1001
2 1002
3 1003(所影响的行数为 3 行)
*/
不知道为什么不行???
declare @ProjectID varchar(100);
select @ProjectID = "1001,1002,1003";
/*
table_Pqs: 查询表名
projectId: 字段名 就是要in的字段名
*/
select * from table_Pqs where charindex(','+cast(projectId as varchar)+',',','+@ProjectID+',')>0;
string ProjectID = "1001,1002,1003"; //原始数据执行下面SQL就可以啊
declare @ProjectID varchar(100);
select @ProjectID = '1001,1002,1003';
/*
table_Pqs: 查询表名
projectId: 字段名 就是要in的字段名
*/
select * from table_Pqs where charindex(','+cast(projectId as varchar)+',',','+@ProjectID+',')>0;
这个在表数据量大的时候会更快一些
(select projectname,value as Xi,projectid from TBLPAS_PRJ_TARGET where type = '进度控制'
and projectid in ('1001','1002')) as A,
(select
avg (CAST(SUBSTRING(value,1,PATINDEX('%[^0.-9.]%',value)-1) as float)) as CLx,
avg(CAST(SUBSTRING(value,1,PATINDEX('%[^0.-9.]%',value)-1) as float)) + stdev(CAST(SUBSTRING(value,1,PATINDEX('%[^0.-9.]%',value)-1) as float)) as UCLx,
avg(CAST(SUBSTRING(value,1,PATINDEX('%[^0.-9.]%',value)-1) as float)) - stdev(CAST(SUBSTRING(value,1,PATINDEX('%[^0.-9.]%',value)-1) as float)) as LCLx
from TBLPAS_PRJ_TARGET
where type = '进度控制' and projectid in ('1001','1002')) as B
declare @ProjectID nvarchar(4000)
set @ProjectID = '1001,1002,1003'
set @ProjectID = Replace(@ProjectID, ',', ''',''')
set @ProjectID = '''' + @ProjectID + ''''print @ProjectID
1001 PAS 成本控制 25.0% 3.0% 15.0% 35.0% 异常 2007-4-23
1001 PAS 末轮测试缺陷密度(个/KLOC) 32.3% 5.0% 3.0% 3.0% 异常 2007-4-23
1001 PAS 验收发现缺陷密度(个/KLOC) 6.0% 0.0% 1.0% 0.57% 正常 2007-4-23
1001 PAS 质量成本 40.0% 20.0% 130.0% 140.2% 异常 2007-4-23
1001 PAS 编码生产率(LOC/人日) 240.0% 125.0% 200.0% 181.8% 异常 2007-4-23
1002 QAS 进度控制 115.0% 10.0% 15.0% 15.6% 异常 2007-4-23
1002 QAS 成本控制 215.0% 13.0% 115.0% 135.0% 异常 2007-4-23
1002 QAS 末轮测试缺陷密度(个/KLOC) 312.3% 15.0% 31.0% 13.0% 异常 2007-4-23
1002 QAS 验收发现缺陷密度(个/KLOC) 61.0% 10.0% 11.0% 10.57% 正常 2007-4-23
1002 QAS 质量成本 401.0% 120.0% 130.0% 140.2% 异常 2007-4-23
1002 QAS 编码生产率(LOC/人日) 240.0% 125.0% 200.0% 181.8% 异常 2007-4-23
@projectid='1001,1002,1003' 即可.
好像和复选框有异曲同工之妙