SQL-在where子句中用不同的日期两次查询同一列 [英] SQL - Query same column twice with different dates in where clause

问题描述

我尝试遍历所有答案，但没有一个答案能回答我的确切问题.我有什么应该是一个相对简单的查询.但是，我很新，仍然在学习SQL.
我需要查询具有不同日期的两列.我想返回具有当前帐户数和当前未结余额的行，并且在同一查询中，返回具有90天前数据的同一列的行.这样，我们可以看到过去90天内帐户和余额的数量增加了多少.理想情况下，我正在寻找这样的结果:

I have tried searching all over for answers but none have answered my exact issue. I have what should be a relatively simple query. However, I am very new and still learning SQL.
I need to query two columns with different dates. I want to return rows with the current number of accounts and current outstanding balance and in the same query, return rows for the same columns with data 90 days prior. This way, we can see how much the number of accounts and balance increased over the past 90 days. Optimally, I am looking for results like this:

PropCode|PropCat|Accts|AcctBal|PriorAccts|PriorBal|
----------------------------------------------------
 77     |Comm   | 350 | 1,000|  275      | 750

以下是我的开始查询.我意识到这是完全错误的，但是我尝试了许多不同的解决方案尝试，但是似乎都没有解决我的特定问题的方法.我将其包括在内可以让我对自己的需求有所了解.帐户和凭证AcctBal列将包含1/31/14数据. PriorAcct& PriorBal列将包含10/31/13数据.

Below is my starting query. I realize it's completely wrong but I have tried numerous different solution attempts but none seem to work for my specific problem. I included it to give an idea of my needs. The accts & AcctBal columns would contain the 1/31/14 data. The PriorAcct & PriorBal columns would contain the 10/31/13 data.

select 
prop_code AS PropCode,
prop_cat, 
COUNT(act_num) Accts,
SUM(act_bal) AcctBal,

(SELECT 
COUNT(act_num) 
FROM table1 
where date = '10/31/13'
and Pro_Group in ('BB','FF') 
and prop_cat not in ('retail', 'personal') 
and Not (Acct_Code = 53 and ACTType in (1,2,3,4,5,6,7)) 
) 
AS PriorAccts,

(SELECT 
SUM(act_bal) 
FROM table1 
where date = '10/31/13'
and Pro_Group in ('BB','FF') 
and prop_cat not in ('retail', 'personal') 
and Not (Acct_Code = 53 and ACTType in (1,2,3,4,5,6,7)) 
) 
AS PriorBal


from table1
where date = '01/31/14'
and Pro_Group in ('BB','FF') 
and prop_cat not in ('retail', 'personal') 
and Not (Acct_Code = 53 and ACTType in (1,2,3,4,5,6,7)) 
group by prop_code, prop_cat
order by prop_cat

推荐答案

为此，您可以将CASE与聚合一起使用(至少在SQL Server中，不确定MySQL):

You can use a CASE with aggregates for this (at least in SQL Server, not sure about MySQL):

...
COUNT(CASE WHEN date='1/31/14' THEN act_num ELSE NULL END) as 'Accts'
,SUM(CASE WHEN date='1/31/14' THEN act_bal ELSE NULL END) as 'AcctBal'
,COUNT(CASE WHEN date='10/31/13' THEN act_num ELSE NULL END) as 'PriorAccts'
,SUM(CASE WHEN date='10/31/13' THEN act_bal ELSE NULL END) as 'PriorAcctBal'
....
WHERE Date IN ('1/31/14', '10/31/13')

这篇关于SQL-在where子句中用不同的日期两次查询同一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！