如何在一个Select Query中写出这两个不同的条件？ [英] How to write this two different condition in one Select Query ?

问题描述

HEllo Team,

I have the Stored Procedure as follows:
ALTER PROCEDURE [dbo].[DEORecordCount]

AS BEGIN



select count(c.ID) as DEORECORDCOUNTDONE  , U.NETWORKID
from CUSTOMERLOANOCR C (nolock)
Inner Join USERMASTER U (nolock) on U.Id = C.ASSIGNEDTO 
Where c.PROCESSED = 1
group by U.NETWORKID,c.PROCESSED 
order by U.NETWORKID,c.PROCESSED 


select count(c.ID) as DEORecordCountPending  , U.NETWORKID
from CUSTOMERLOANOCR C (nolock)
Inner Join USERMASTER U (nolock) on U.Id = C.ASSIGNEDTO 
Where c.PROCESSED = 0
group by U.NETWORKID,c.PROCESSED 
order by U.NETWORKID,c.PROCESSED 

End
there are condition in my Stored Procedure
I wrote two different condition in two different select query .

In first condition processed = 1 which will give the count(c.ID) as DEORECORDCOUNTDONE  , U.NETWORKID

In Second Condition,PROCESSED = 0 which will give the count(c.ID) as DEORecordCountPending  ,  U.NETWORKID

How to write this two different condition in one Select Query ?? Where should i make the changes ??
Please Guide Me.
Thanks
Harshal

推荐答案

COUNT 返回非NULL值的数量。



试试这个：

Hi,

The COUNT returns the number of non-NULL values.

Try this:

select count(case c.PROCESSED when 1 then 1 else null end) as DEORECORDCOUNTDONE, count(case c.PROCESSED when 0 then 0 else null end) as DEORecordCountPending, U.NETWORKID
from CUSTOMERLOANOCR C (nolock)
Inner Join USERMASTER U (nolock) on U.Id = C.ASSIGNEDTO 
Where (c.PROCESSED = 1) or (c.PROCESSED = 0)
group by U.NETWORKID
order by U.NETWORKID

您可以使用以下查询：

Hi,

You can use following query :

select count(c.ID) as DEORECORDCOUNTDONE  , U.NETWORKID,
(case when c.PROCESSED=1 then 'Processed' else c.PROCESSED=0 then 'Pending' end) as 'Status'
from CUSTOMERLOANOCR C (nolock)
Inner Join USERMASTER U (nolock) on U.Id = C.ASSIGNEDTO
group by U.NETWORKID,c.PROCESSED
order by U.NETWORKID,c.PROCESSED

此处，状态是一个列，用于识别您的状态，即待处理或已处理。如果您有任何疑问，请告诉我。

Here, Status is a column which will identify your status i.e. pending or processed. Let me know if you have any doubt.

这篇关于如何在一个Select Query中写出这两个不同的条件？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！