实现如下结果，sql如何实现
UserID UserName
----------- --------------------
1 Peter
2 Tom
3 Jhon
4 Bob
5 Jack
表tbRecord如下:
RecordID UserID RecordDate
----------- ----------- -----------------------
1 1 2012-10-01 12:00:00.000
2 1 2012-01-20 14:20:53.000
3 1 2012-03-04 09:00:00.000
4 1 2012-01-05 13:00:03.000
5 1 2012-05-06 08:45:23.000
6 2 2012-01-23 14:22:32.000
7 2 2012-01-12 23:00:00.000
8 2 2012-02-04 10:11:14.000
9 2 2012-03-22 11:56:43.000
10 2 2012-02-01 04:23:00.000
11 3 2012-05-06 12:44:23.000
12 3 2012-07-08 05:54:23.000
13 3 2012-05-30 20:34:48.000
14 4 2012-09-19 23:59:00.000
15 5 2012-04-22 21:33:44.000
16 5 2012-04-18 08:38:48.000
17 5 2012-04-12 07:38:21.000
18 1 2012-10-01 22:33:44.000
19 1 2012-01-20 23:38:12.000
求2012年每个用户每个月有多少天的记录数，
比如 peter用户在表tbRecord中，2012年1月有2天的记录数（虽然1月1日有3条记录，但只能算是一天的记录），2月份0天的记录。。。
结果需要如下：其中省略了4到12月份
username 1 2 3
-------------------- ----------- ----------- -----------
Peter 2 0 1
Tom 2 2 1
Jhon 0 0 0
Bob 0 0 0
Jack 0 0 0
以上只是列出2012年，还有其他年份，数据不只这些，只是列出几行数据，先假设tbUser有30行数据，tbRecord有几百万数据,写出sql（oracle）语句实现。
[解决办法]
select UserName,
(select count(distinct(to_char(RecordDate,'yyyymmdd'))) from tbRecord b where a.userid=b.userid and to_char(RecordDate,'yyyymm')='201201') 1月份记录数,
(select count(distinct(to_char(RecordDate,'yyyymmdd'))) from tbRecord b where a.userid=b.userid and to_char(RecordDate,'yyyymm')='201202') 2月份记录,
...........
from tbUser a
[解决办法]
--RecordDate是date型？
select UserName,
sum(decode(month, 1, 1, 0)) "1",
sum(decode(month, 2, 1, 0)) "2",
sum(decode(month, 3, 1, 0)) "3",
sum(decode(month, 4, 1, 0)) "4",
sum(decode(month, 5, 1, 0)) "5",
sum(decode(month, 6, 1, 0)) "6",
sum(decode(month, 7, 1, 0)) "7",
sum(decode(month, 8, 1, 0)) "8",
sum(decode(month, 9, 1, 0)) "9",
sum(decode(month, 10, 1, 0)) "10",
sum(decode(month, 11, 1, 0)) "11",
sum(decode(month, 12, 1, 0)) "12" from (
select UserName, extract(month from max(RecordDate) ) month from tbRecord left join tbUser on tbUser.UserID=tbRecord.UserID
where RecordDate between to_date('2012-01-01', 'yyyy-mm-dd') and to_date('2013-01-01', 'yyyy-mm-dd')
group by UserName, trunc(RecordDate, 'dd'))
group by UserName;
WITH user_ym_tb AS (
SELECT UserID,
months
FROM (SELECT DISTINCT UserID
FROM tbRecord WHERE RecordDate >= TO_TIMESTAMP('2012-01-01 00:00:00.000','yyyy-mm-dd hh24:mi:ss.ff')
AND RecordDate < TO_TIMESTAMP('2013-01-01 00:00:00.000','yyyy-mm-dd hh24:mi:ss.ff')
),
(SELECT TO_CHAR(LEVEL,'fm09') months FROM DUAL CONNECT BY LEVEL <= 12)
)
SELECT m.UserID,
MAX(DECODE(m.months,'01',m.cnt)) m1,
MAX(DECODE(m.months,'02',m.cnt)) m2,
MAX(DECODE(m.months,'03',m.cnt)) m3,
MAX(DECODE(m.months,'04',m.cnt)) m4,
MAX(DECODE(m.months,'05',m.cnt)) m5,
MAX(DECODE(m.months,'06',m.cnt)) m6,
MAX(DECODE(m.months,'07',m.cnt)) m7,
MAX(DECODE(m.months,'08',m.cnt)) m8,
MAX(DECODE(m.months,'09',m.cnt)) m9,
MAX(DECODE(m.months,'10',m.cnt)) m10,
MAX(DECODE(m.months,'11',m.cnt)) m11,
MAX(DECODE(m.months,'12',m.cnt)) m12
FROM (SELECT nvl(m1.UserID,m2.UserID) UserID,
nvl(m1.cnt,0) cnt,
m2.months
FROM (SELECT t.UserID,
TRUNC(t.RecordDate, 'mm') RecordDate,
COUNT(DISTINCT TRUNC(t.RecordDate)) cnt
FROM tbRecord t
WHERE RecordDate >= TO_TIMESTAMP('2012-01-01 00:00:00.000','yyyy-mm-dd hh24:mi:ss.ff')
AND RecordDate < TO_TIMESTAMP('2013-01-01 00:00:00.000','yyyy-mm-dd hh24:mi:ss.ff')
GROUP BY t.UserID,
TRUNC(t.RecordDate, 'mm')
) m1 RIGHT JOIN user_ym_tb m2
ON m1.UserID = m2.UserID
AND TO_CHAR(m1.RecordDate,'mm') = m2.months
) m
GROUP BY m.UserID
select user_name,
count(1) number,
to_char(record_date, 'yyyy-mm-dd')
from a inner join b
on a.user_id= b.user_id
group by user_name,
to_char(record_date, 'yyyy-mm-dd')
[解决办法]
'08' then 1 else 0 end) as Aug,
sum(case when substr(to_char(r.recorddate,'yyyy-MM-dd'),1,7)= substr(to_char(r.recorddate,'yyyy-MM-dd'),1,5)
[解决办法]
'09' then 1 else 0 end) as Sep,
sum(case when substr(to_char(r.recorddate,'yyyy-MM-dd'),1,7)= substr(to_char(r.recorddate,'yyyy-MM-dd'),1,5)
[解决办法]
'10' then 1 else 0 end) as Oct,
sum(case when substr(to_char(r.recorddate,'yyyy-MM-dd'),1,7)= substr(to_char(r.recorddate,'yyyy-MM-dd'),1,5)
[解决办法]
'11' then 1 else 0 end) as Nov,
sum(case when substr(to_char(r.recorddate,'yyyy-MM-dd'),1,7)= substr(to_char(r.recorddate,'yyyy-MM-dd'),1,5)
[解决办法]
'12' then 1 else 0 end) as Dece,
max(substr(to_char(r.recorddate,'yyyy-MM-dd'),1,4))as year
from userinfo u
join (select distinct m.userid, m.recorddate from userRecord m) r on r.userid=u.userid
group by u.userid,u.username,substr(to_char(r.recorddate,'yyyy-MM-dd'),1,7) order by u.userid) a
group by a.year,a.username order by a.username;
这个对你有帮助
[解决办法]
select a.username,max(a.Jan)Jan,max(a.Feb)Feb,max(a.Mar)Mar,max(a.Apr)Apr,max(a.May)May,max(a.Jun)Jun,
max(a.Jul)Jul,max(a.Aug)Aug,max(a.Sep)Sep,max(a.Oct)Oct,max(a.Nov)Nov,max(a.Dece)Dece,a.year from
(select
u.username,
sum(case when substr(to_char(r.recorddate,'yyyy-MM-dd'),1,7)= substr(to_char(r.recorddate,'yyyy-MM-dd'),1,5)
[解决办法]
'01' then 1 else 0 end) as Jan,
sum(case when substr(to_char(r.recorddate,'yyyy-MM-dd'),1,7)= substr(to_char(r.recorddate,'yyyy-MM-dd'),1,5)
[解决办法]
'02' then 1 else 0 end) as Feb,
sum(case when substr(to_char(r.recorddate,'yyyy-MM-dd'),1,7)= substr(to_char(r.recorddate,'yyyy-MM-dd'),1,5)
[解决办法]
'03' then 1 else 0 end) as Mar,
sum(case when substr(to_char(r.recorddate,'yyyy-MM-dd'),1,7)= substr(to_char(r.recorddate,'yyyy-MM-dd'),1,5)
[解决办法]
'04' then 1 else 0 end) as Apr,
sum(case when substr(to_char(r.recorddate,'yyyy-MM-dd'),1,7)= substr(to_char(r.recorddate,'yyyy-MM-dd'),1,5)
[解决办法]
'05' then 1 else 0 end) as May,
sum(case when substr(to_char(r.recorddate,'yyyy-MM-dd'),1,7)= substr(to_char(r.recorddate,'yyyy-MM-dd'),1,5)
[解决办法]
'06' then 1 else 0 end) as Jun,
sum(case when substr(to_char(r.recorddate,'yyyy-MM-dd'),1,7)= substr(to_char(r.recorddate,'yyyy-MM-dd'),1,5)
[解决办法]
'07' then 1 else 0 end) as Jul,
sum(case when substr(to_char(r.recorddate,'yyyy-MM-dd'),1,7)= substr(to_char(r.recorddate,'yyyy-MM-dd'),1,5)
[解决办法]
'08' then 1 else 0 end) as Aug,
sum(case when substr(to_char(r.recorddate,'yyyy-MM-dd'),1,7)= substr(to_char(r.recorddate,'yyyy-MM-dd'),1,5)
[解决办法]
'09' then 1 else 0 end) as Sep,
sum(case when substr(to_char(r.recorddate,'yyyy-MM-dd'),1,7)= substr(to_char(r.recorddate,'yyyy-MM-dd'),1,5)
[解决办法]
'10' then 1 else 0 end) as Oct,
sum(case when substr(to_char(r.recorddate,'yyyy-MM-dd'),1,7)= substr(to_char(r.recorddate,'yyyy-MM-dd'),1,5)
[解决办法]
'11' then 1 else 0 end) as Nov,
sum(case when substr(to_char(r.recorddate,'yyyy-MM-dd'),1,7)= substr(to_char(r.recorddate,'yyyy-MM-dd'),1,5)
[解决办法]
'12' then 1 else 0 end) as Dece,
max(substr(to_char(r.recorddate,'yyyy-MM-dd'),1,4))as year
from userinfo u
join (select distinct m.userid, m.recorddate from userRecord m) r on r.userid=u.userid
group by u.userid,u.username,substr(to_char(r.recorddate,'yyyy-MM-dd'),1,7) order by u.userid) a
group by a.year,a.username order by a.username;
