bookid bookname booktype author publisher isbn--
create view v_test
as
select bookid,isbn,bookname from ta
union all
select bookid,isbn,booktype from ta
union all
select bookid,isbn,author from ta
union all
select bookid,isbn,publisher from tagoselect ...................
create view v_test
as
select bookid,isbn,bookname from ta
union all
select bookid,isbn,booktype from ta
union all
select bookid,isbn,author from ta
union all
select bookid,isbn,publisher from tagoselect ...................
解决方案 »
- 怎么实现某张表内某项的值是对另外一张表的全部统计?
- 急救!为何我的 ADOcientdataset 在 Edit时用applyupdate 不能促存数据到SQLSERVER2000?
- 如何生成这样的组?
- 有一次我在打开企业管理器时弹出:MMC无法打开选择文件。 企业管理器打开失败,不如何办。谢谢!!
- 任何一个二目关系都属于三范式吗???
- 大哥,帮小妹找点触发器和存储过程的例子,好吗
- 如何插入数据库数据并且不产生日志?
- 一行数据变成多行数据的SQL,该如何写?
- SQL不能安装
- 请问大力:你写的《MS SQL Server2000全文索引image列全攻略》中用到sp_textcopy库里没有啊
- sql如何查询一个字段同时满足几个值的记录?
- 无法打开SQL Server Management Studio
where bookid=Isnull(@bookid,bookid)
and (@bookname is null or bookname like '%'+@bookname+'%')
and (@booktype is null or booktype like '%'+@booktype+'%')
and (@author is null or author like '%'+@author+'%')
and (@publisher is null or publisher like '%'+@publisher+'%')
and isbn=Isnull(@isbn,isbn)
方法二:declare @sql nvarchar(max)
set @sql = ' select * from tb where 1=1 '
if(@bookid is not null)
@sql = @sql + ' and bookid='+@bookid
if(@bookname is not null)
@sql = @sql + ' and bookname like ''%'+@bookid+'%'' '
if...
...exec(@sql)另外,like非常影响查询效率,甚用。
1.你必须明细这些条件的关联性,确定是不是只要录入了内容同时满足才返回?还是只要任意一个满足也返回.
2.写个储存过程给你,这样客户端只要传入参数即可.Create Procedure QueryBook
@bookid nvarchar(20),
@bookname nvarchar(20),
@booktype nvarchar(20),
@author nvarchar(20),
@publisher nvarchar(20),
@isbn nvarchar(20)
as
Declare @QueryString nvarchar(1000)
Set @QueryString=''if IsNull(@bookid,'')<>''
Set @QueryString=@QueryString+' bookid=''' +@bookid+''''if IsNull(@bookname,'')<>''
Set @QueryString=@QueryString+ when case @QueryString='' then '' else ' And ' end +' bookname Like ''%' +@bookname+'%'''if IsNull(@booktype,'')<>''
Set @QueryString=@QueryString+ when case @QueryString='' then '' else ' And ' end +' booktype Like ''%' +@booktype+'%'''if IsNull(@author,'')<>''
Set @QueryString=@QueryString+ when case @QueryString='' then '' else ' And ' end +' author Like ''%' +@author+'%'''if IsNull(@publisher,'')<>''
Set @QueryString=@QueryString+ when case @QueryString='' then '' else ' And ' end +' publisher Like ''%' +@publisher+'%'''if IsNull(@isbn,'')<>''
Set @QueryString=@QueryString+' isbn=''' +@isbn+''''
Set @QueryString='select * from book where ' + @QueryString
-- 返回查询结果
exec (@QueryString)
go
CREATE PROC Book
(
@bookid nvarchar(20),
@bookname nvarchar(20),
@booktype nvarchar(20),
@author nvarchar(20),
@publisher nvarchar(20),
@isbn nvarchar(20))
AS
SET NOCOUNT ON
DECLARE @sql nvarchar(4000)
SET @sql='SELECT * FROM tbname WHERE 1=1'
+CASE WHEN @bookid IS NULL THEN '' ELSE ' AND bookid=@bookid' END
+CASE WHEN @bookname IS NULL THEN '' ELSE ' AND bookname=@bookname' END
+CASE WHEN @booktype IS NULL THEN '' ELSE ' AND booktype=@booktype' END
+CASE WHEN @author IS NULL THEN '' ELSE ' AND author=@author' END
+CASE WHEN @publisher IS NULL THEN '' ELSE ' AND publisher=@publisher' END
+CASE WHEN @isbn IS NULL THEN '' ELSE ' AND isbn=@isbn' END
EXEC sp_executesql @sql,N'
@bookid nvarchar(20),
@bookname nvarchar(20),
@booktype nvarchar(20),
@author nvarchar(20),
@publisher nvarchar(20),
@isbn nvarchar(20)
',@bookid,@bookname,@booktype,@author,@publisher,@isbn
GO
(
@bookid nvarchar(20),
@bookname nvarchar(20),
@booktype nvarchar(20),
@author nvarchar(20),
@publisher nvarchar(20),
@isbn nvarchar(20))
AS
SET NOCOUNT ON
DECLARE @sql nvarchar(4000)
SET @sql='SELECT * FROM tbname WHERE 1=1'
+CASE WHEN @bookid IS NULL THEN '' ELSE ' AND bookid=@bookid' END
+CASE WHEN @bookname IS NULL THEN '' ELSE ' AND bookname like ''%'+@bookname+'%''' END
+CASE WHEN @booktype IS NULL THEN '' ELSE ' AND booktype like ''%'+@booktype+'%''' END
+CASE WHEN @author IS NULL THEN '' ELSE ' AND author like ''%'+@author+'%''' END
+CASE WHEN @publisher IS NULL THEN '' ELSE ' AND publisher like '%''+@publisher+'%''' END
+CASE WHEN @isbn IS NULL THEN '' ELSE ' AND isbn like '%''+@isbn+'%''' END
EXEC sp_executesql @sql,N'
@bookid nvarchar(20),
@bookname nvarchar(20),
@booktype nvarchar(20),
@author nvarchar(20),
@publisher nvarchar(20),
@isbn nvarchar(20)
',@bookid,@bookname,@booktype,@author,@publisher,@isbn
GO