select * users where cid in (@cids) --》改成 select * users where charindex(','+ltrim(cid)+',',','+@cids+',')>0 --or exec('select * users where cid in ('+@cids+')')
DECLARE @pwd TABLE (pwd nvarchar(100)) INSERT @pwd select pwd from rele group by pwd having(count(pwd)>1) --多条重复pwd DECLARE @cids TABLE (cids nvarchar(100)) INSERT @cids SELECT cid from rele where pwd IN (SELECT * FROM @pwd) --多条重复pwd的cid
declare @pwd nvarchar(100) declare @cids nvarchar(100) select @pwd =ISNULL(@pwd+',','')+ltrim(pwd),@cids=ISNULL(@pwd+',','')+ltrim(cid) from rele group by pwd having(count(pwd)>1)/*此处获得pwd ,该pwd在表里至少存在两条同样数据*/ select @pwd,@cids 要实现这样的结果?
我用数据打个比方吧,也许大家更明白 select cid from rele where pwd=@pwd/*得出数据:1,2,3,4 */ select @cids/* 得出数据:4 */而我想要的结果是 select * users where cid in (1,2,3,4)而不是现在的 select * users where cid in (1)
select cid from rele where pwd=@pwd/*得出数据:1,2,3,4 */ select @cids/* 得出数据:4 */--》 select @cids=isnull(@cids+',','')+ltrim(cid) from rele where pwd=@pwd
嗨,您这样是行得通不过会有个错误 -------------------------------------------------------- 在将 nvarchar 值 '1,2,3,4' 转换成数据类型 int 时失败。
@cids是字符型 应该不会吧 ltrim(cid) 这里已经做了转换了
--一句SQL搞定 select * users where cid in (select cid from rele where exists(select 1 from rele a where a.pwd=rele.pwd group by a.pwd having(count(a.pwd)>1)))
--》改成
select * users where charindex(','+ltrim(cid)+',',','+@cids+',')>0
--or
exec('select * users where cid in ('+@cids+')')
DECLARE @pwd TABLE (pwd nvarchar(100))
INSERT @pwd
select pwd from rele group by pwd having(count(pwd)>1) --多条重复pwd
DECLARE @cids TABLE (cids nvarchar(100))
INSERT @cids
SELECT cid from rele where pwd IN (SELECT * FROM @pwd) --多条重复pwd的cid
declare @cids nvarchar(100)
select @pwd =ISNULL(@pwd+',','')+ltrim(pwd),@cids=ISNULL(@pwd+',','')+ltrim(cid) from rele group by pwd having(count(pwd)>1)/*此处获得pwd ,该pwd在表里至少存在两条同样数据*/
select @pwd,@cids
要实现这样的结果?
select cid from rele where pwd=@pwd/*得出数据:1,2,3,4 */
select @cids/* 得出数据:4 */而我想要的结果是 select * users where cid in (1,2,3,4)而不是现在的 select * users where cid in (1)
select @cids/* 得出数据:4 */--》
select @cids=isnull(@cids+',','')+ltrim(cid) from rele where pwd=@pwd
--------------------------------------------------------
在将 nvarchar 值 '1,2,3,4' 转换成数据类型 int 时失败。
应该不会吧
ltrim(cid) 这里已经做了转换了
select * users where cid in (select cid from rele where exists(select 1 from rele a where a.pwd=rele.pwd group by a.pwd having(count(a.pwd)>1)))