表结构 为
ID BID ISMust Result
1 0 true OK
2 1 true NO
3 1,2 true null
4 2,3 true null
以 ID 为 4 的记录为例，需要查询出 ID 为 2,3的Result
BID 的数据类型为nvarchar
使用 关键字In 无法进行查询!
在sql 中该如何查询！使用sql 语句，不是C#的代码?
------解决方案--------------------
- SQL code
create table tb(ID int,BID varchar(20),ISMust varchar(10),Result varchar(10))insert into tb select 1,'0','true','OK'insert into tb select 2,'1','true','NO'insert into tb select 3,'1,2','true',nullinsert into tb select 4,'2,3','true',nullgodeclare @sql varchar(2000)select @sql='select * from tb where id in('+bid+')' from tbexec(@sql)/*ID BID ISMust Result----------- -------------------- ---------- ----------2 1 true NO3 1,2 true NULL(2 行受影响)*/godrop table tb
------解决方案--------------------
- SQL code
------------------------------ Author :fredrickhu(小F，向高手学习)-- Date :2011-09-21 09:58:52-- Verstion:-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) -- Apr 22 2011 11:57:00 -- Copyright (c) Microsoft Corporation-- Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)--------------------------------> 测试数据：[tb]if object_id('[tb]') is not null drop table [tb]go create table [tb]([ID] int,[BID] varchar(3),[ISMust] varchar(4),[Result] varchar(2))insert [tb]select 1,'0','true','OK' union allselect 2,'1','true','NO' union allselect 3,'1,2','true',null union allselect 4,'2,3','true',null--------------开始查询--------------------------select distinct a.* from tb a,tb b where CHARINDEX(','+ltrim(b.ID)+',',','+a.BID+',')>0 and a.ID<>4----------------结果----------------------------/* ID BID ISMust Result----------- ---- ------ ------2 1 true NO3 1,2 true NULL(2 行受影响)*/