求SQL Server存储过程：关键字匹配多个字段

shenzongyi123 2010-09-17 09:51:10
是这样的，我想通过输入一个关键字，在一个表里这个关键字可以模糊匹配多个字段，并将受影响的行数返回！
这是我写的测试代码：
CREATE PROCEDURE sp_getTotalRecordsByKeyWords

	@keyWords varchar(50)

AS

SELECT count(*) AS total,

	  cast(b.bookID as varchar(30))

	  + cast(b.typename as varchar(30))

	  + cast(b.bookname as varchar(50)) 

	  + cast(b.publish as varchar(20)) 

      + cast(b.author as varchar(30)) AS keyWords  

FROM bookinfo b

WHERE b.keyWords like '%' +@keyWords + '%';

GO

错误消息：
消息 207，级别 16，状态 1，过程 sp_getTotalRecordsByKeyWords，第 11 行
列名 'keyWords' 无效
...全文
246 18 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
18 条回复
切换为时间正序
请发表友善的回复…
发表回复
athonyho 2011-08-16
  • 打赏
  • 举报
 回复
不错 标记学习下
hao1hao2hao3 2010-09-17
  • 打赏
  • 举报
 回复
[Quote=引用 10 楼 samchoy 的回复:]
SQL code
CREATE PROCEDURE sp_getTotalRecordsByKeyWords
@keyWords varchar(50)
AS
SELECT count(*) AS total,
cast(b.bookID as varchar(30))
+ cast(b.typename as varchar(30))
+……
[/Quote]


呵呵！还是这哥们细心！
samchoy 2010-09-17
  • 打赏
  • 举报
 回复 
CREATE PROCEDURE sp_getTotalRecordsByKeyWords

    @keyWords varchar(50)

AS

SELECT count(*) AS total,

      cast(b.bookID as varchar(30))

      + cast(b.typename as varchar(30))

      + cast(b.bookname as varchar(50)) 

      + cast(b.publish as varchar(20)) 

      + cast(b.author as varchar(30)) AS keyWords  

FROM bookinfo b

WHERE cast(b.bookID as varchar(30))

      + cast(b.typename as varchar(30))

      + cast(b.bookname as varchar(50)) 

      + cast(b.publish as varchar(20)) 

      + cast(b.author as varchar(30)) like '%' +@keyWords + '%'

group by b.bookID,b.typename,b.bookname,b.publish,b.author;

GO
hao1hao2hao3 2010-09-17
  • 打赏
  • 举报
 回复
[Quote=引用 8 楼 shenzongyi123 的回复:]
引用 7 楼 samchoy 的回复:
试试

SQL code
CREATE PROCEDURE sp_getTotalRecordsByKeyWords
@keyWords varchar(50)
AS
SELECT count(*) AS total,
cast(b.bookID as varchar(30))
+ cast(b.typename as varchar(30……
[/Quote]

提示什么？都说了少了group by语句了。

在Where子句后面加

CREATE PROCEDURE sp_getTotalRecordsByKeyWords
@keyWords varchar(50)
AS
SELECT count(*) AS total,
cast(b.bookID as varchar(30))
+ cast(b.typename as varchar(30))
+ cast(b.bookname as varchar(50))
+ cast(b.publish as varchar(20))
+ cast(b.author as varchar(30)) AS keyWords
FROM bookinfo b
WHERE cast(b.bookID as varchar(30))
+ cast(b.typename as varchar(30))
+ cast(b.bookname as varchar(50))
+ cast(b.publish as varchar(20))
+ cast(b.author as varchar(30)) like '%' +@keyWords + '%'
group by cast(b.bookID as varchar(30))
+ cast(b.typename as varchar(30))
+ cast(b.bookname as varchar(50))
+ cast(b.publish as varchar(20))
+ cast(b.author as varchar(30))



好别扭啊！
shenzongyi123 2010-09-17
  • 打赏
  • 举报
 回复
[Quote=引用 7 楼 samchoy 的回复:]
试试

SQL code
CREATE PROCEDURE sp_getTotalRecordsByKeyWords
@keyWords varchar(50)
AS
SELECT count(*) AS total,
cast(b.bookID as varchar(30))
+ cast(b.typename as varchar(30))
……
[/Quote]
不行！
samchoy 2010-09-17
  • 打赏
  • 举报
 回复
试试
CREATE PROCEDURE sp_getTotalRecordsByKeyWords

    @keyWords varchar(50)

AS

SELECT count(*) AS total,

      cast(b.bookID as varchar(30))

      + cast(b.typename as varchar(30))

      + cast(b.bookname as varchar(50)) 

      + cast(b.publish as varchar(20)) 

      + cast(b.author as varchar(30)) AS keyWords  

FROM bookinfo b

WHERE cast(b.bookID as varchar(30))

      + cast(b.typename as varchar(30))

      + cast(b.bookname as varchar(50)) 

      + cast(b.publish as varchar(20)) 

      + cast(b.author as varchar(30)) like '%' +@keyWords + '%';

GO
shenzongyi123 2010-09-17
  • 打赏
  • 举报
 回复
keyWords是那几个字段连接后的别名，不知道我的写法有问题，这方面了还不是很清楚！麻烦哪位告诉我下，该如何实现该功能
budong0000 2010-09-17
  • 打赏
  • 举报
 回复
在一个表里这个关键字可以模糊匹配多个字段

可能你想要模糊查询，如果用到动态字段就用动态语句，估计你字段都定好了。所以
col1 like '%' +@keyWords + '%'
or col2 like '%' +@keyWords + '%'
or col3 like '%' +@keyWords + '%'
Rotel-刘志东 2010-09-17
  • 打赏
  • 举报
 回复
没有此列
hao1hao2hao3 2010-09-17
  • 打赏
  • 举报
 回复
你的写法有问题吧！

没有分组语句，怎么用count这个聚合函数？

group by
hao1hao2hao3 2010-09-17
  • 打赏
  • 举报
 回复
表bookinfo 中有这个列吗？
luoyoumou 2010-09-17
  • 打赏
  • 举报
 回复
-- 错误已经很明显啦：列名 'keyWords' 无效！
-- 意思是你的列名写错了嘛！表中不存在　名为 keyWords 的字段名！
shenzongyi123 2010-09-17
  • 打赏
  • 举报
 回复
谢谢各位的帮助，散分！
shenzongyi123 2010-09-17
  • 打赏
  • 举报
 回复
额……问题终于解决了，好像是total和keyWords别名的原因。
SQL别名问题参考：http://weichongkun.blog.163.com/blog/static/71220498200881973344263/
这是我修改后的代码：
CREATE PROCEDURE sp_getTotalRecordsByKeyWords

    @keyWords varchar(50)

AS

SELECT count(*)  

FROM bookinfo b

WHERE cast(b.bookID as varchar(30))

      + cast(b.typename as varchar(30))

      + cast(b.bookname as varchar(50)) 

      + cast(b.publish as varchar(20)) 

      + cast(b.author as varchar(30)) like '%' +@keyWords + '%';

GO



exec sp_getTotalRecordsByKeyWords 'j'

返回无名列：结果集为2

samchoy 2010-09-17
  • 打赏
  • 举报
 回复
似乎也不对，要不这样
CREATE PROCEDURE sp_getTotalRecordsByKeyWords

    @keyWords varchar(50), @count int output

AS

SELECT  cast(b.bookID as varchar(30))

      + cast(b.typename as varchar(30))

      + cast(b.bookname as varchar(50)) 

      + cast(b.publish as varchar(20)) 

      + cast(b.author as varchar(30)) AS keyWords  

FROM bookinfo b

WHERE cast(b.bookID as varchar(30))

      + cast(b.typename as varchar(30))

      + cast(b.bookname as varchar(50)) 

      + cast(b.publish as varchar(20)) 

      + cast(b.author as varchar(30)) like '%' +@keyWords + '%'

SET @count = @@ROWCOUNT

GO
samchoy 2010-09-17
  • 打赏
  • 举报
 回复
[Quote=引用 13 楼 shenzongyi123 的回复:]
虽然10楼的可以解决问题，但是好像total不是返回的总数，而是分组之后的总数，该如何改进呢？[/Quote]
终于明白你要的是什么了
CREATE PROCEDURE sp_getTotalRecordsByKeyWords

    @keyWords varchar(50)

AS

SELECT count(*) AS total, keyWords

FROM (SELECT cast(bookID as varchar(30))

           + cast(typename as varchar(30))

           + cast(bookname as varchar(50)) 

           + cast(publish as varchar(20)) 

           + cast(author as varchar(30)) AS keyWords  

      FROM bookinfo) b

WHERE keyWords like '%'+@keyWords+'%'

GROUP BY keyWords

GO
shenzongyi123 2010-09-17
  • 打赏
  • 举报
 回复
[Quote=引用 10 楼 samchoy 的回复:]
SQL code
CREATE PROCEDURE sp_getTotalRecordsByKeyWords
@keyWords varchar(50)
AS
SELECT count(*) AS total,
cast(b.bookID as varchar(30))
+ cast(b.typename as varchar(30))
+……
[/Quote]
虽然10楼的可以解决问题，但是好像total不是返回的总数，而是分组之后的总数，该如何改进呢？
心中的彩虹 2010-09-17
  • 打赏
  • 举报
 回复
[Quote=引用楼主 shenzongyi123 的回复:]
是这样的，我想通过输入一个关键字，在一个表里这个关键字可以模糊匹配多个字段，并将受影响的行数返回！
这是我写的测试代码：


SQL code
CREATE PROCEDURE sp_getTotalRecordsByKeyWords
@keyWords varchar(50)
AS
SELECT count(*) AS total,
cast(b.b……
[/Quote]


--好久没来mssql了



CREATE PROCEDURE sp_getTotalRecordsByKeyWords

    @keyWords varchar(50)

AS

SELECT count(*) AS total,

      cast(b.bookID as varchar(30))

      + cast(b.typename as varchar(30))

      + cast(b.bookname as varchar(50)) 

      + cast(b.publish as varchar(20)) 

      + cast(b.author as varchar(30)) AS keyWords  

FROM bookinfo b

WHERE (cast(b.bookID as varchar(30))

      + cast(b.typename as varchar(30))

      + cast(b.bookname as varchar(50)) 

      + cast(b.publish as varchar(20)) 

      + cast(b.author as varchar(30))) like '%' +@keyWords + '%';



group by (cast(b.bookID as varchar(30))

      + cast(b.typename as varchar(30))

      + cast(b.bookname as varchar(50)) 

      + cast(b.publish as varchar(20)) 

      + cast(b.author as varchar(30))) 

GO

















--or







CREATE PROCEDURE sp_getTotalRecordsByKeyWords

    @keyWords varchar(50)

AS

select total,keyWords

from (SELECT count(*) AS total,

      cast(b.bookID as varchar(30))

      + cast(b.typename as varchar(30))

      + cast(b.bookname as varchar(50)) 

      + cast(b.publish as varchar(20)) 

      + cast(b.author as varchar(30)) AS keyWords  

FROM bookinfo b) t

WHERE t.keyWords like '%' +@keyWords + '%';

GO
SQL查询字段被包含语句
说到SQL的模糊查询，最先想到的，应该就是like关键字。当我们需要查询包含某个特定字段的数据时，往往会使用 ‘%关键字%’ 查询的方式。具体代码示例大家参考下本
Sqlserver2008--学习笔记(自己总结)
Sqlserver2008--学习笔记(自己总结)
SQL 存储过程
SQL SERVER 存储过程，通过简单的实例，了解怎么实现关系数据库的存储过程
全文搜索存储过程
全文搜索存储过程，根据关键字以及权重搜索
SQLServer地址搜索性能优化
这是一个很久以前的例子，现在在整理资料时无意发现，就拿出来再改写分享。 1.需求  1.1 基本需求： 根据输入的地址关键字，搜索出完整的地址路径，耗时要控制在几十毫秒内。  1.2 数据库地址表结构和数据：  表TBAddress    表数据    1.3 例子：  e.g. 给出一个字符串如“广 大”，找出地址全路径中包含有“广” 和“大”的所有地址，結果如下： 下面将通过4个方法来实现，再分析其中的性能优劣，然后选择一个比较优的方法。  2.创建表和插入数据  2.1 创建数据表TBAddress use test; go /* create table */ if object_
疑难问题

22,300

社区成员

121,734

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章