求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




22,300

社区成员

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

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