一个表,其中有两个字段account_date,amountamount是金额,account_date是入账时间,采用的是yyyymmdd格式我想求出所有12月份的金额总计写了这个select case when SUBSTR(to_char(account_DATE,'YYYYMMDD'),5,2)='12' then sum(amount) end from tablename group by.......不知道是否可行,请教
调试欢乐多
when extract (month from to_date(account_DATE,'YYYYMMDD')) = 12
then sum(amount)
else
0
end
from tablename
select sum(amount)
from tablename
where SUBSTR(to_char(account_DATE,'YYYYMMDD'),5,2)='12'
select sum(amount)
from tablename
where to_char(account_date,'mm')='12'
select to_char(account_date,'yyyy-mm') months,sum(amount) amt
from tablename
group by to_char(account_date,'yyyy-mm')
select sum(case when SUBSTR(to_char(account_DATE,'YYYYMMDD'),5,2)='12' then amount
else 0
end)
from tablename
SELECT
distinct(SUBSTR(to_char(A.VOUCHER_DATE,'YYYYMMDD'),1,4)||SUBSTR(to_char(B.VOUCHER_DATE,'YYYYMMD'),1,4))
FROM TABLE1 A,
TABLE2 B
写了一个小视图,要得到表1,表2中的voucher_date的年份,并且不要重复,现在是table1中有数据,table2中没有数据,空的,通过上面的视图我得到的数据是空的,可我的原意是只要一个表中有数据都要显示出来,不知道问题出在哪,请指教,谢谢。
distinct(SUBSTR(to_char(A.VOUCHER_DATE,'YYYYMMDD'),1,4))
FROM TABLE1 A
UNION
SELECT
distinct(SUBSTR(to_char(B.VOUCHER_DATE,'YYYYMMD'),1,4))
FROM TABLE2 B
这样不就行了你写的那个会笛卡尔积的
另外得到问题就结贴吧