请解决以下问题 [英] Please solve below Query

问题描述

我运行了以下查询但未执行。请帮我解决这个问题：

 选择 COUNT（< span class =code-keyword> distinct  RepID）'  MissingInventory' 
 来自 
（选择 CAST（datediff（day， 0 ，datesent） as   datetime ）datesent 
，r.repid ，TerritoryHierarchy =（ SELECT   DISTINCT   TOP  < span class =code-digit> 1  HR.TerritoryNum TerritoryID，HR.Dateto，HR.DateFrom 
  FROM  HistoryRepTerritory HR 
 其中 e.Datesent   HR.DateFrom 和 HR.DateTo 
 和 HR.RepID = r.RepID 
   -   由HR.Dateto DESC订购，HR.DateFrom DESC  
）
 来自 dbo.reps r  inner   join  dbo.Email_EmailsToSend e  on  r.RepId = e。参考
 其中 e.AlertsituationId = '  13 ' 
 和 cast（Datediff（DAY， 0 ，cast（isnull（ e.Datesent，'  1/1/1900'） as   datetime ）） as   datetime ）
   '  2014年1月1日' 和 '  2014年12月31日'）tf 
  WHERE  < span class =code-keyword> EXISTS （ SELECT   1   FROM  TerritoryHierarchy TH  inner   join  HistoryRepTerritory HT 
   HT.TerritoryNum = TH.ID）

错误消息：当子查询未与EXISTS一起引入时，只能在选择列表中指定一个表达式。

解决方案

问题是这里 -

  SELECT   DISTINCT   TOP   1  HR.Territo ryNum TerritoryID，HR.Dateto，HR.DateFrom 
  FROM  HistoryRepTerritory HR 
 其中 e.Datesent   HR.DateFrom 和 HR.DateTo 
 和 HR.RepID = r.RepID 

您只能为子查询提及一列。

试试这个 -

 选择 COUNT（ distinct  RepID ）'  MissingInventory' 
 来自 
（选择 CAST（datediff（day， 0 ，datesent） as   datetime ）datesent 
，r.repid，TerritoryHierarchy =（ SELECT   DISTINCT   TOP   1  HR.TerritoryNum 
  FROM  HistoryRepTerritory HR 
 其中 e.Datesent   HR.DateFrom 和 HR.DateTo 
 和 HR.RepID = r.RepID 
   -   由HR.Dateto DESC订购，HR.DateFrom DESC  
）
 来自 dbo.reps r  inner   join  dbo.Email_EmailsToSend e  on  r.RepId = e.Reference 
 其中 e.AlertsituationId = '  13' 
 和投（Datedi ff（DAY， 0 ，cast（isnull（e.Datesent，'  1/1/1900'） as   datetime ）） as   datetime ）
     1/1/2014' 和 '  2014年12月31日'）tf 
  WHERE   EXISTS （ SELECT   1   FROM  TerritoryHierarchy TH  inner   join  HistoryRepTerritory HT 
   HT.TerritoryNum = TH.ID）

希望，它帮助：）

Hi ,
I ran Below query but not executed. Please help me to solve this issue:

select COUNT(distinct RepID) 'MissingInventory'
from
( select CAST(datediff(day,0,datesent) as datetime) datesent
,r.repid,TerritoryHierarchy = (SELECT DISTINCT TOP 1 HR.TerritoryNum TerritoryID,HR.Dateto,HR.DateFrom
             FROM HistoryRepTerritory HR
             where e.Datesent between HR.DateFrom and HR.DateTo
             and HR.RepID = r.RepID
            -- order by HR.Dateto DESC,HR.DateFrom DESC
             )
from dbo.reps r inner join dbo.Email_EmailsToSend e on r.RepId = e.Reference
where e.AlertsituationId = '13'
and cast(Datediff(DAY,0,cast(isnull(e.Datesent,'1/1/1900') as datetime)) as datetime)
between '1/1/2014' and '12/31/2014') tf
WHERE EXISTS( SELECT 1 FROM TerritoryHierarchy TH inner join HistoryRepTerritory HT
on HT.TerritoryNum = TH.ID )

Error Message: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

解决方案

Problem is here-

SELECT DISTINCT TOP 1 HR.TerritoryNum TerritoryID,HR.Dateto,HR.DateFrom
             FROM HistoryRepTerritory HR
             where e.Datesent between HR.DateFrom and HR.DateTo
             and HR.RepID = r.RepID

You can mention only one column for the subquery.
Try this-

select COUNT(distinct RepID) 'MissingInventory'
from
( select CAST(datediff(day,0,datesent) as datetime) datesent
,r.repid,TerritoryHierarchy = (SELECT DISTINCT TOP 1 HR.TerritoryNum
             FROM HistoryRepTerritory HR
             where e.Datesent between HR.DateFrom and HR.DateTo
             and HR.RepID = r.RepID
            -- order by HR.Dateto DESC,HR.DateFrom DESC
             )
from dbo.reps r inner join dbo.Email_EmailsToSend e on r.RepId = e.Reference
where e.AlertsituationId = '13'
and cast(Datediff(DAY,0,cast(isnull(e.Datesent,'1/1/1900') as datetime)) as datetime)
between '1/1/2014' and '12/31/2014') tf
WHERE EXISTS( SELECT 1 FROM TerritoryHierarchy TH inner join HistoryRepTerritory HT
on HT.TerritoryNum = TH.ID )

Hope, it helps :)

这篇关于请解决以下问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！