sql 散分

[lx8900133]

表名 t_info
id info gid
1 aaaaa 1
2 bbbbb 1,2
3 cccc 3,4,11
4 dddd 1,2,3,4


一用户 他的组id 是1 , 现在从表中取 属于1的信息 ，取到的结果应该是 第1，2，4条
sql？？？？

[知识与运气]

创建一个存储过程：


CREATE PROCEDURE sp_t_info_sel

@aa varchar(100)=null --你要查找的字符：比如你要查找的id为1，这个aa参数就是1

as



select * from t_info where d like '%'+ @aa +'%' or info like '%'+ @aa +'%' or gid like '%'+ @aa +'%'


GO

[yushuiyouyue]

对AJAX有兴趣的朋友，欢迎加入群78514534
对ASP.NET和C#有兴趣的朋友，欢迎加入67226009

[xfreyes]

表结构很不合理！！

[byte377]

UP

[wanghao3616]

能改变gid的存储方式吗？

如果改为这样是不是更便于操作

表名 t_info
id info gid
1 aaaaa ,1,
2 bbbbb ,1,2,
3 cccc ,3,4,11,
4 dddd ,1,2,3,4,

这样用

select * from  t_info  where gid like '%,1,%'

[zzxap]

select * from t_info where charindex('1',gid)>0

[vbman2003]

select * from t_info where charindex(',1,',','+gid+',')>0

[zzxap]

[code=SQL]
select * from t_info where charindex('1',gid)>0
union all
select * from t_info where charindex('1,',gid)>0
union all
select * from t_info where charindex(',1,',gid)>0

[/CODE]

[花郎]

SQL: select * from t_info where gid like '%,1,%'

[wanghao3616]

表也可以做成两个表

表名 t_info
id info gid
1 aaaaa ,1,
2 bbbbb ,1,2,
3 cccc ,3,4,11,
4 dddd ,1,2,3,4,


表名 t_infoIngroup
id tid gid
1 1 1
2 2 1
3 2 2
4 3 3
....
这样

select t_info.* from  t_infoIngroup 
left join t_info on t_infoIngroup.tid=t_info.id 
where t_infoIngroup.gid=1

[itliyi]

ding

[weizijia]

为什么不弄个住外关系的表啊
你这表省事了，查询麻烦了，以后还有变竖表的事

[zhujin1123]

SELECT * FROM  t_info WHERE GID LIKE '%1%'

[zzxap]

[code=SQL]

select * from t_info where gid='1'
union all
select * from t_info where charindex('1,',gid)>0
union all
select * from t_info where charindex(',1,',gid)>0


[/CODE]

[zzxap]

[code=SQL]
SELECT DISTINCT * FROM
(
select * from t_info where gid='1'
union all
select * from t_info where charindex('1,',gid)>0
union all
select * from t_info where charindex(',1,',gid)>0
)A

[/CODE]