查询

Question

BH   F_name         uploadtype        uploadobject   ispublic     creater
1    教学计划        1                 linli,zhou       1           system
2    学习计划        0                                  0           system
3    学习            1                 system,zhou      0           linli
4    娱乐            0                 system,linli     0           zhou如果ispublic=1当uploadtype=1 表示指定用户,uploadtype=0表示所有用户
如果ispublic=0当uploadtype=1表示指定用户,uploadtype=0表示仅限自己即creater 现在假设用户是linli想搜索出
ispublic=1当uploadtype=1时 uploadobject有linli 的记录
和ispublic=0, uploadobject有linli的记录,还有creater='linli'的记录
即
BH   F_name         uploadtype        uploadobject   ispublic     creater
1    教学计划        1                 linli,zhou       1           system3    学习            1                 system,zhou      0           linli
4    娱乐            0                 system,linli     0           zhou

Answer 1

select * from tb where ispublic=1 and uploadtype=1 and charindex(',linli,',','+creater+',')>0
union all
select * from tb where ispublic=0 and charindex(',linli,',','+creater+',')>0

Answer 2

select * from T
where (ispublic=1 and uploadtype=1 and charindex(',linli,' , ','+rtrim(uploadobject)+',')>0 )
Or (ispublic=0 and (charindex(',linli,' , ','+rtrim(uploadobject)+',')>0  or creater='linli'))

Answer 3

select * from [Table] where ispublic=1 and uploadtype=1 and charindex(',linli,',','+uploadobject+',')>0 or ispublic=0 and charindex(',linli,',','+uploadobject+',')>0 and creater='linli'