菜鸟求一条SQL
如图所示,表A为一个公告表(还有其他字段,简化了),Title为标题,
VisibleType 为 "是否开放"
值为1,则为所有人全部都能看,Name则为Null
值为0,则需要指定人才能看,Name中存放的指定能看的人的帐号,帐号通过分号分割.
现在要用SQL语句获取指定帐号能看到的公告列表,比如 zhangsan 打开,能看到五篇. fengjie打开,只能看到3篇(即公开的),在线等,谢谢.
[解决办法]
如果要显示为一个结果集，需要分拆 Name 这一列
参照方法
http://bbs.csdn.net/topics/230087434
[解决办法]
USE tempdb;
/*
CREATE TABLE t1
(
id INT IDENTITY(1,1),
title NVARCHAR(10) NOT NULL,
visibletype INT NOT NULL,
name NVARCHAR(200) NULL
);
INSERT INTO t1(title,visibletype,name)
VALUES('第一篇',1,NULL),('第二篇',1,NULL),('第三篇',1,NULL),
('第四篇',0,'zhangsan;lisi;'),('第五篇',0,'zhangsan;lisi;wangwu');
*/
DECLARE @username AS NVARCHAR(50);
--SET @username = 'zhangsan';
SET @username = 'zhaoliu';
SELECT *
FROM t1
WHERE t1.visibletype = 1
OR t1.[name] LIKE '%'+ @username + ';%';
select Title from table1 where VisibleType=1
union
select Title from table1 where VisibleType=0 and charindex('zhangsan',Name)>0
if object_id('test') is not null
drop table test
go
create table test(id int identity,title varchar(20),visibletype bit,name varchar(40))
insert into test
select '第一课',1,null union all
select '第二课',1,null union all
select '第三课',1,null union all
select '第四课',0,'zhangsan;lishi' union all
select '第五课',0,'zhangsan;lishi' union all
select '第六课',1,null
select * from test t where exists(
select 1 from test where CHARINDEX('fengzi',isnull(name,''))>0 or visibletype=1 and id=t.id
)
/*
(6 row(s) affected)
id title visibletype name
----------- -------------------- ----------- ----------------------------------------
1 第一课 1 NULL
2 第二课 1 NULL
3 第三课 1 NULL
6 第六课 1 NULL
(4 row(s) affected)
*/
--首先创建这个表值函数，以后遇到这种问题直接调用这个表值函数
CREATE FUNCTION [dbo].[F_SPLIT]
(
@split_string varchar(max), --要进行分解的字符串
@tag_string varchar(10) --分解标志
)
RETURNS
@split_table TABLE
(
split_value varchar(200)
)
AS
BEGIN
declare @temp_string varchar(max)
declare @start_index int
declare @end_index int
while 1=1
begin
set @start_index = 0
select @end_index = CharIndex(@tag_string,@split_string,@start_index)
if @end_index <> 0
begin
set @temp_string = Substring(@split_string,@start_index,@end_index)
if @temp_string is not null and @temp_string <> ''
insert into @split_table(split_value) values(@temp_string)
set @start_index = @end_index + 1
set @split_string = Substring(@split_string,@start_index,len(@split_string))
end
else
begin
if @split_string is not null and @split_string <> ''
insert into @split_table(split_value) values(@split_string)
break
end
end
RETURN
END
--创建好以后
SELECT * FROM TABLE_ WHERE VisibleType=0 AND 'zhangsan' IN (SELECT * FROM F_SPLIT(Name,';')
