56,679
社区成员
发帖
与我相关
我的任务
分享
select user_id,
sum(CASE WHEN EXTRACT(MONTH FROM pub_date)=1 THEN book_id*(10^book_id) ELSE 0 END) as 1月份,
sum(CASE WHEN EXTRACT(MONTH FROM pub_date)=2 THEN book_id*(10^book_id) ELSE 0 END) as 2月份,
sum(CASE WHEN EXTRACT(MONTH FROM pub_date)=3 THEN book_id*(10^book_id) ELSE 0 END) as 3月份,
sum(CASE WHEN EXTRACT(MONTH FROM pub_date)=4 THEN book_id*(10^book_id) ELSE 0 END) as 4月份,
sum(CASE WHEN EXTRACT(MONTH FROM pub_date)=5 THEN book_id*(10^book_id) ELSE 0 END) as 5月份,
sum(CASE WHEN EXTRACT(MONTH FROM pub_date)=6 THEN book_id*(10^book_id) ELSE 0 END) as 6月份,
sum(CASE WHEN EXTRACT(MONTH FROM pub_date)=7 THEN book_id*(10^book_id) ELSE 0 END) as 7月份,
sum(CASE WHEN EXTRACT(MONTH FROM pub_date)=8 THEN book_id*(10^book_id) ELSE 0 END) as 8月份,
sum(CASE WHEN EXTRACT(MONTH FROM pub_date)=9 THEN book_id*(10^book_id) ELSE 0 END) as 9月份,
sum(CASE WHEN EXTRACT(MONTH FROM pub_date)=10 THEN book_id*(10^book_id) ELSE 0 END) as 10月份
from 表
group by user_id
select user_id,
sum(CASE WHEN book_id=1 THEN 1 ELSE 0 END)>0 as book_1,
sum(CASE WHEN book_id=2 THEN 1 ELSE 0 END)>0 as book_2,
sum(CASE WHEN book_id=3 THEN 1 ELSE 0 END)>0 as book_3,
sum(CASE WHEN book_id=4 THEN 1 ELSE 0 END)>0 as book_4,
sum(CASE WHEN book_id=5 THEN 1 ELSE 0 END)>0 as book_5,
sum(CASE WHEN book_id=6 THEN 1 ELSE 0 END)>0 as book_6,
sum(CASE WHEN book_id=7 THEN 1 ELSE 0 END)>0 as book_7,
sum(CASE WHEN book_id=8 THEN 1 ELSE 0 END)>0 as book_8,
sum(CASE WHEN book_id=9 THEN 1 ELSE 0 END)>0 as book_9,
sum(CASE WHEN book_id=10 THEN 1 ELSE 0 END)>0 as book_10,
from 表
group by user_id