求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

回复赞

心中的彩虹 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

回复赞