如何在子查询中实现分组依据 [英] how to implement Group by in Sub Query

问题描述

我是Ramesh.

我的subQuery sp是

Hi this is Ramesh.

My subQuery sp is

alter proc [dbo].[UDSP_GetPivotTable_Tmf_New_WithAllClients]          
(          
@ClientID int    
)          
as   
SELECT  distinct
        ( SELECT  COUNT(*)   FROM tbl_MainList WHERE  Code = ''Visit'';    and ClientID=@ClientID  )   as Visit,
        (SELECT COUNT(*)  FROM tbl_MainList WHERE Code= ''Ptp''   and ClientID=@ClientID)as Ptp
from tbl_MainList where ClientID=@ClientID;  

上面的SP返回
7 4 6 27 9 379269.00 180283.00 93013.00 128875.00 71626.00
7 4 6 27 9 379269.00 180283.00 93013.00 128875.00 71626.00 Ashish
7 4 6 27 9 379269.00 180283.00 93013.00 128875.00 71626.00 Ayyappa

但是查询返回的所有记录相同，但员工姓名不同.

如何在过程或子查询中传递员工姓名.
假设我在过程中传递员工姓名，它将仅执行第一条语句.第二件事出了错.

The Above SP Returns
7 4 6 27 9 379269.00 180283.00 93013.00 128875.00 71626.00
7 4 6 27 9 379269.00 180283.00 93013.00 128875.00 71626.00 Ashish
7 4 6 27 9 379269.00 180283.00 93013.00 128875.00 71626.00 Ayyappa

But the Query returns all the records same but Different Employee Names.

How to pass in Employee name in procedure or Subquery.
suppose i am passing employee name in procedure it will execute only first statement.Second thing gives wrong.

推荐答案

您需要做的是:

Hi,

What you need to do is:

alter proc [dbo].[UDSP_GetPivotTable_Tmf_New_WithAllClients]          
(          
@ClientID int    
)          
as   

DECLARE @VISIT INT
DECLARE @PTP INT

SELECT @VISIT=SELECT  COUNT(*)   FROM tbl_MainList WHERE  Code = 'Visit' and ClientID=@ClientID

SELECT @PTP = SELECT COUNT(*)  FROM tbl_MainList WHERE Code= 'Ptp'   and ClientID=@ClientID

SELECT @VISIT AS VISIT, @PTP AS PTP

这篇关于如何在子查询中实现分组依据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！