存储过程来计算不同的项目数 [英] Stored Procedure to count varying no of items

问题描述

我的存储过程如下：

I have stored procedure as:

procedure [dbo].[GetIssueBookCount](@stud_id varchar(100),  @book_id varchar(100) )
as
begin

    declare @TotalCount int  = 0;

    select @TotalCount = count(*)  from bookissue where bookid = @book_id and stuid = @stud_id

    set @TotalCount = isnull(@TotalCount,0)

    select @TotalCount as TotalCount;

    select * from library

end

并且想要计算库中可用书籍的数量。

计数应根据问题和书籍的退货而有所不同。

如果上面sp错误，建议正确一个。

and want to count the no of books available in the library.
The count should vary according to issue and return of books.
If above sp is wrong suggest correct one.

推荐答案

仅基于您上面的代码...您不需要从库中选择*。你也不需要这么多套装和选择。



尝试只是

Based on just the code you have above... You dont need the select * from Library. You also dont need to have so many sets and selects.

try just

Select Count(*) As TotalCount FROM bookissue WHERE bookid = @book_id AND stuid = @stud_id

猜测只会告诉你特定的次数该书已经发给该学生。



如果数据库根据每条记录上的标志存储该书是OnSite或Offsite，您很可能需要考虑，或每笔交易的新记录。



如果您想要返回库中所有书籍的计数，您需要链接一些表格，但没有更多信息我无法帮忙。

At a guess that will only tell you the number of times that particular book has been issued to that student.

You will most likely need to take into account if the database stores the book being OnSite or Offsite based on a flag on each record, or a new record for each transaction.

If you want to return a count of all books in the library you would need to link some tables, but without more information I cant help.

我在这里得到什么



你需要计算图书馆里的书籍吗？

What i am getting here

you need to have the count for books available in the library?

select count(1) from library
where bookid not in (select bookid from bookissue)

两个表中都必须有一些关系。我在这里做的是我计算了图书馆中书籍中不存在的书籍。



我不确定你有哪些栏目以及有什么条件申请检查，但它可以帮助您找到您要找的东西。

There must be some relation in both the tables. What i did here is that i counted the books in the library that does not exists in bookissue table.

I am not sure what columns you have and what conditions to be applied to check but it can help you to find what you are looking for.

这篇关于存储过程来计算不同的项目数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！