问题在计算sql server中的记录时 [英] Problem In counting records in sql server

问题描述

我需要从提案表中计算待定提案，并显示他们的记录数。这是表结构。

Hi ,
I need to count Pending Proposal's from Proposal table and show their record count.This is the table structure.

ProposalNO    ApplicationNo       Status  Locked

        ABC           1              A      Y
        ABC           2              A      Y
        ABC           3              A      Y
        ABC           4              R      N
        ABC           5
        DEF           6              A      Y
        DEF           7              R      N
        DEF           8              A      Y
        IJK           9              A      Y
        IJK          10

现在Proposal ABC有5个应用程序，其中只有4个具有锁定列的条目，因此ABC正在等待DEF完成且IJK也处于待处理状态。现在我只需要为待处理的应用程序获取数据。这里的锁定列是应用程序的状态而不是提案。如果提案中任何应用程序的锁定状态为null，则该提议处于待处理状态。

Now Proposal ABC has 5 applications out of which only 4 have entry in locked column so ABC is pending where as DEF is Completed and IJK is also pending. Now i need to get data for pending applications only. Locked column here is the status of application not the proposal. If the locked status of any application inside a proposal is null then that proposal is pending.

ProposalNo    NoOfApplications
  ABC                 5
  IJK                 2

i总是得不到使用group by的应用程序，但我无法创建合适的查询为寻找天气，该提案尚未决定。任何sorf的帮助将不胜感激。

i can always get no of applications using group by but i am not able to create a suitable query for finding weather the proposal is pending or not. Any sorf of help will be much appreciated .

推荐答案

我认为你正在寻找：

SELECT ProposalNo, 
    COUNT(ProposalNo) AS ProposalCount
WHERE Status = 'R'
GROUP BY ProposalNo
HAVING ProposalCount > 0

让我们考虑你有桌子

Lets consider you have table

ProposalDetails(ProposalNO, ApplicationNo, Status, Locked)

有上述数据。尝试以下查询它将使您的图片清晰。

having above data. Try following query it will make your picture clear.

 SELECT	ProposalNO,
	SUM(1) [Applications],
	SUM(CASE WHEN Locked = 'Y' THEN 1 ELSE 0 END) [Locked],
	SUM(CASE WHEN Locked = 'N' THEN 1 ELSE 0 END) [UnLocked],
	SUM(CASE WHEN Locked <> 'Y' AND Locked <> 'N' THEN 1 ELSE 0 END) [UnKnown]
FROM	ProposalDetails
GROUP BY ProposalNO

现在，如果您认为有效，可以在查询中添加以下条件。

Now you can add following condition to your query if you think its valid.

HAVING SUM(CASE WHEN Locked = 'N' THEN 1 ELSE 0 END) > 0

这篇关于问题在计算sql server中的记录时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！