求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' 无效
...全文
244 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查询字段被包含语句
总的来说，`CHARINDEX`是SQL中一个非常实用的函数，尤其在需要检查字段是否包含一系列关键词时，它可以替代多个`LIKE`条件，简化查询语句，提高代码可维护性。但在使用时，也需要考虑到性能因素，特别是在大数据场景...
Sqlserver2008--学习笔记(自己总结)
### Sqlserver2008 学习笔记精要 #### 第一章 SQL Server 基础 ##### 1. T-SQL 语句基础 - **创建数据库**: 使用 `CREATE DATABASE` 语句来创建一个新的数据库。例如： ```sql CREATE DATABASE 新数据库名; ``...
SQL 存储过程
SQL存储过程是数据库中的一种重要的编程对象，它是为执行一个或多个特定任务而编写的SQL语句集合。存储过程可以接收参数、输出参数，并且可以返回记录集或者整数值。在SQL Server中，存储过程可以极大地提高数据操作...
全文搜索存储过程
本文将深入解析一个特定的全文搜索存储过程，旨在通过关键字及其权重进行精确搜索，这一过程涉及SQL Server的全文搜索功能、存储过程的设计与实现，以及如何优化查询性能。 #### 存储过程设计与实现 存储过程`Proc...
SQLServer地址搜索性能优化
表TBAddress是存储地址信息的核心表，具有ID（主键）、Parent（父级ID）、LevelNo（层级编号）和Name（地址名称）这四个字段。为了加速查询，我们创建了两个非聚簇索引：ix_TBAddress_Parent（基于Parent和LevelNo，...
疑难问题

22,300

社区成员

121,734

社区内容

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

加载中

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

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

+ 用AI写文章