如何根据查询SQL的结果运行子查询
如何根据查询SQL的结果运行子查询
提问于 2015-02-20 15:24:46
我有两个问题，我想运行。这里的想法是通过事务"type“对事务表运行一个查询。基于这些结果，我希望运行另一个查询，查看客户根据特定类型进行的最后一次事务处理，以查看服务ID是否相同。如果不一样的话，我想把它标记为“升级”
下面是根据事务类型从事务表中提取结果的初始查询：
Select customerid, serviceid
from Transactions
where (dtcreated > @startdate and dtcreated < @enddate) and (transactiontype = 'Cust Save')这方面的产出是：
Customerid ServiceID
1 11
2 21
3 21
4 11
5 12
6 11接下来我要做的是运行这个查询，匹配customerID以查看客户最后的收费是什么：
Select serviceID, MAx(dtcreated) as MostRecent
From Transactions
Where (transactiontype = 'Cust Purchase')
Group By serviceID我将这两个查询组合在一起的最后输出是：
Customerid ServiceID Last Purchase Upgraded?
1 11 11 No
2 21 11 Yes
3 21 12 Yes
4 11 10 Yes
5 12 12 No
6 11 11 No我以为这可能管用，但它并没有给我想要的东西。它返回太多的结果，因此查询显然不正确。
Select serviceID, MAx(dtcreated) as MostRecent
From Transactions
WHERE Where (transactiontype = 'Cust Purchase') AND EXISTS
(Select customerid, serviceid
from Transactions
where (dtcreated > @startdate and dtcreated < @enddate) and (transactiontype = 'Cust Save'))
GROUP BY serviceid回答 3
Stack Overflow用户
回答已采纳
发布于 2015-02-20 15:49:40
如果我正确地理解了需求，您可以使用ROW_NUMBER来确定哪条记录是最新的customerID记录。然后，您可以将它加入事务表，以确定ServiceID中是否有匹配：
SELECT r.CustomerID,
t.ServiceID,
t.dtCreated,
Upgraded = CASE WHEN t.ServiceID = cp.ServiceID THEN 0 ELSE 1 END
FROM Transactions AS t
INNER JOIN
( SELECT CustomerID,
ServiceID,
dtCreated,
RowNumber = ROW_NUMBER() OVER(PARTITION BY CustomerID ORDER BY dtCreated DESC)
FROM Transactions
WHERE transactiontype = 'Cust Purchase'
) AS cp
ON cp.CustomerID = t.CustomerID
AND cp.RowNumber = 1
WHERE t.dtcreated > @startdate
AND t.dtcreated < @enddate
AND t.transactiontype = 'Cust Save'Stack Overflow用户
发布于 2015-02-20 15:30:36
我想你需要这样的东西。在这里，t1给出了每个客户创建的最大date；t2包含给定日期范围内的所有事务；t3给出了每个客户的最后一次购买。
select
t1.customerid,
t3.serviceid as Last_Purchase_ServiceID,
t1.dtcreated as Last_Purchase_DateCreated,
t2.ServiceID as Current_Purchase_ServiceID,
t2.dtcreated as Current_Purchase_DateCreated
from
(
select customerid, max(dtcreated) as dtcreated
from
Transactions
group by customerid
)
t1
join
(
select customerid, serviceid, dtcreated
from Transactions
where (dtcreated > @startdate and dtcreated < @enddate) and (transactiontype = 'Cust Save')
) t2 on t1.customerid = t2.customerid
join
Transactions t3 on t1.customerid = t3.customerid and t1.dtcreated = t3.dtcreatedStack Overflow用户
发布于 2015-02-20 15:52:14
试试OUTER APPLY
Select t.customerid, t.serviceid, o.MostRecent
from Transactions t
OUTER APPLY (
Select MAx(dtcreated) as MostRecent
From Transactions
Where transactiontype = 'Cust Purchase' and Customerid = t.Customerid --And ServiceID = t.ServiceID
) o
where (t.dtcreated > @startdate and t.dtcreated < @enddate) and (t.transactiontype = 'Cust Save')页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/28632433
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号