我试着到处寻找答案,但没有人回答我的确切问题。我有一个相对简单的查询。不过,我还是个新手,还在学SQL。
我需要查询两个日期不同的列。我想返回当前帐户数和当前未清余额的行,在同一个查询中,返回90天前数据相同列的行。这样,我们可以看到在过去90天里,账户数量和余额增加了多少。最理想的情况是,我正在寻找这样的结果:
PropCode|PropCat|Accts|AcctBal|PriorAccts|PriorBal|
----------------------------------------------------
77 |Comm | 350 | 1,000| 275 | 750
下面是我的起始查询。我意识到这是完全错误的,但我已经尝试了许多不同的解决方案,但似乎没有一个适合我的具体问题。我把它放进去是为了说明我的需要。accts&AcctBal列将包含2014年1月31日的数据。PriorAcct&PriorBal列将包含10/31/13数据。
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不太确定):
...
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')
关于mysql - SQL-在where子句中以不同的日期两次查询同一列,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/22100150/