嵌套SQL SELECT
问题描述:
我有一个大的SQL表和我有回报以下查询的一些基本信息:嵌套SQL SELECT
SELECT
CustomerID AS "Customer Name"
,COUNT(ID) AS "Total Emails"
FROM EmailDatas WHERE Expired = 0
GROUP BY CustomerID
结果
Customer Name Total Emails
A 1000
B 9200
C 11400
我所试图做的就是计数电子邮件在同一查询返回今天的日期,类似的:
结果
Customer Name Total Emails Emails Today
A 1000 34
B 9200 7
C 11400 54
我可以修改原来的查询返回的信息:
SELECT
CustomerID AS "Customer Name"
,COUNT(ID) AS "Total Emails"
FROM EmailDatas WHERE Expired = 0 and starttime > getdate()
GROUP BY CustomerID
我需要的是基本上有这两个查询组合。
希望这是有道理的。
谢谢!
答
您可以使用条件聚合。像这样的:
SELECT CustomerID AS "Customer Name",
COUNT(ID) AS "Total Emails",
SUM(CASE WHEN CAST(starttime as date) = CAST(getdate() as date) THEN 1 ELSE 0 END) as "Today's"
FROM EmailDatas
WHERE Expired = 0
GROUP BY CustomerID;
答
你也可以这样做。
select t1.CustomerName,t1.TotalEmails,
case when t2.EmailsToday is null then 0
else t2.EmailsToday end as EmailsToday
from (SELECT
CustomerID AS CustomerName
,COUNT(ID) AS TotalEmails
FROM EmailDatas WHERE Expired = 0
GROUP BY CustomerID) t1
left join
(SELECT
CustomerID AS CustomerName
,COUNT(ID) AS EmailsToday
FROM EmailDatas WHERE Expired = 0 and starttime > getdate()
GROUP BY CustomerID) t2
on t1.CustomerName=t2.CustomerName
答
这应该工作
SELECT
t1.CustomerID AS [Customer Name],
t1.[Total Emails],
t2.[Emails Today]
FROM
(SELECT
CustomerID
,COUNT(ID) AS [Total Emails]
FROM EmailDatas WHERE Expired = 0
GROUP BY CustomerID) AS t1 LEFT OUTER JOIN
(SELECT
CustomerID
,COUNT(ID) AS [Emails Today]
FROM EmailDatas WHERE Expired = 0 and starttime > getdate()
GROUP BY CustomerID) AS t2
ON t1.CustomerID = t2.CustomerID
什么'这里starttime' ??? – Abbas
请用你正在使用的数据库标记你的问题。 –
使用的标记dbms。 (您正在使用产品特定的功能。) – jarlh