样例表 表aaa
T_DAY PROVINCE U_Count
20120101 010 1
20120101 010 2
20120102 010 4
20120102 0431 1
20120131 0431 1
20120131 010 2
20120208 0431 2
20120208 010 2
20120208 010 2表A
T_DAY PROVINCE day_num month_num year_num
20120208 010 4 6 6
20120208 0431 2 2 2以下是aaa表查询出 20120208当天的sum()的数量,想用一条语句里写入20120208前一个月的 及 前 一年的,如何写尼? insert into A
(T_DAY,PROVINCE,day_num)
SELECT t.T_DAY,t.PROVINCE,'XX用户数',
SUM(t.U_Count) AS 本日值
from aaa t
where t.CLCT_DAY = '20120208'
group by t.T_DAY,t.PROVINCE;
T_DAY PROVINCE U_Count
20120101 010 1
20120101 010 2
20120102 010 4
20120102 0431 1
20120131 0431 1
20120131 010 2
20120208 0431 2
20120208 010 2
20120208 010 2表A
T_DAY PROVINCE day_num month_num year_num
20120208 010 4 6 6
20120208 0431 2 2 2以下是aaa表查询出 20120208当天的sum()的数量,想用一条语句里写入20120208前一个月的 及 前 一年的,如何写尼? insert into A
(T_DAY,PROVINCE,day_num)
SELECT t.T_DAY,t.PROVINCE,'XX用户数',
SUM(t.U_Count) AS 本日值
from aaa t
where t.CLCT_DAY = '20120208'
group by t.T_DAY,t.PROVINCE;
前一年CLCT_DAY>=add_months(sysdate,-12)
(select '20120101' T_DAY, '010' PROVINCE, 1 U_Count
from dual
union all
select '20120101', '010', 2
from dual
union all
select '20120102', '010', 4
from dual
union all
select '20120102', '043', 1
from dual
union all
select '20120131', '043', 1
from dual
union all
select '20120131', '010', 2
from dual
union all
select '20120208', '043', 2
from dual
union all
select '20120108', '043', 32
from dual
union all
select '20110208', '043', 23
from dual
union all
select '20120208', '010', 2
from dual
union all
select '20120208', '010', 2 from dual),
bbb as(
SELECT T_DAY,PROVINCE,
SUM(U_Count) u_count
from aaa
group by T_DAY,PROVINCE)select a.*,b.U_Count lastmonth,c.U_count lastyear from bbb a,bbb b,bbb c
where a.T_DAY='20120208' and a.province=b.province(+) and a.province=c.province(+)
and b.t_day(+)='20120108' and c.t_day(+)='20110208'
insert into A
(T_DAY,PROVINCE,day_num)
SELECT t.T_DAY,t.PROVINCE,'XX用户数',
SUM(t.U_Count) AS 本日值
from aaa t
where t.CLCT_DAY = '20120208'
group by t.T_DAY,t.PROVINCE;
插入三列,怎么select了四列?楼主的语句是不是有问题啊。
(select '20120101' T_DAY, '010' PROVINCE, 1 U_Count
from dual
union all
select '20120101', '010', 2
from dual
union all
select '20111202', '010', 4
from dual
union all
select '20111202', '043', 1
from dual
union all
select '20111231', '043', 1
from dual
union all
select '20121130', '010', 2
from dual
union all
select '20121108', '043', 2
from dual
union all
select '20121108', '043', 32
from dual
union all
select '20121208', '043', 23
from dual
union all
select '20121208', '010', 2
from dual
union all
select '20121208', '010', 2 from dual)
SELECT sum(CASE WHEN T_DAY = '20121208'
THEN U_Count ELSE 0 END) day_num
,sum(CASE WHEN substr(T_DAY,1,6) = to_char(to_date('201212','YYYYMM') - 1,'YYYYMM')
THEN U_Count ELSE 0 END) month_num
,sum(CASE WHEN substr(T_DAY,1,6) BETWEEN to_char(to_date('201212','YYYYMM') - 11,'YYYYMM') AND '201212'
THEN U_Count ELSE 0 END) year_num
FROM aaa;
-------- ------------------------------------------ ----------
20111008 020 5
20111118 010 6
20111031 020 5
20120109 020 8
20120315 010 3
20120214 010 7
20120314 010 9
20111016 020 6
20120410 020 5已选择9行。
SQL> select t_day,
2 province,
3 sum(u_count) day_num,
4 (select sum(u_count)
5 from aaa
6 where trunc(add_months(to_date(t.t_day, 'yyyymmdd'), -1), 'mm') =
7 trunc(to_date(t_day, 'yyyymmdd'), 'mm')
8 and t.province = province) month_num,
9 (select sum(u_count)
10 from aaa
11 where trunc(add_months(to_date(t.t_day, 'yyyymmdd'), -12), 'yyyy') =
12 trunc(to_date(t_day, ' yyyymmdd'), 'yyyy')
13 and t.province = province) year_num
14 from aaa t
15 where t_day = '20120408'
16 group by t_day, province
17 ;T_DAY PROVINCE DAY_NUM MONTH_NUM
-------- ------------------------------------------ ---------- ----------
YEAR_NUM
----------
20120408 010 9 213
66120120408 020 11 174
756