求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' 无效
...全文
164 点赞 收藏 18
写回复
18 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
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




回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2010-09-17 09:51
社区公告
暂无公告