展开全部
建表和视图语句:
DROP TABLE IF EXISTS `62616964757a686964616fe59b9ee7ad9431333433616161tab`;
CREATE TABLE `tab` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`userid` int(11) NULL DEFAULT NULL,
`date` datetime NULL DEFAULT NULL,
`instructions` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`amount` decimal(18, 2) NULL DEFAULT NULL,
`type` tinyint(1) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 9 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
create view tab_v as
select
`tab`.`userid` AS `userid`,
date_format( `tab`.`date`, '%Y-%m' ) AS `ym`,
`tab`.`instructions` AS `instructions`,
`tab`.`amount` AS `amount`,
`tab`.`type` AS `type`
from
`tab`
查询语句:
select t0.userid 用户ID,
t0.ym 年月,
t1.cnt 本月收入笔数,
t2.instructions 本月最大收入项目,
t2.amount 本月最大收入金额,
t3.instructions 本月最小收入项目,
t3.amount 本月最小收入金额,
t4.cnt 本月支出笔数,
t5.instructions 本月最大支出项目,
t5.amount 本月最大支出金额,
t6.instructions 本月最小支出项目,
t6.amount 本月最小支出金额,
t7.cnt 本月结余
from (select distinct a.userid,
a.ym
from tab_v a) t0
left join (select a.userid,
a.ym,
count(1) cnt
from tab_v a
where a.type = 2
group by a.userid,
a.ym) t1
on t0.userid = t1.userid
and t0.ym = t1.ym
left join (select a.userid,
a.ym,
a.amount,
group_concat(b.instructions) instructions
from (select a.userid,
a.ym,
max(a.amount) amount
from tab_v a
where a.type = 2
group by a.userid,
a.ym) a,
tab_v b
where a.userid = b.userid
and a.ym = b.ym
and a.amount = b.amount
group by a.userid,
a.ym,
a.amount) t2
on t0.userid = t2.userid
and t0.ym = t2.ym
left join (select a.userid,
a.ym,
a.amount,
group_concat(b.instructions) instructions
from (select a.userid,
a.ym,
min(a.amount) amount
from tab_v a
where a.type = 2
group by a.userid,
a.ym) a,
tab_v b
where a.userid = b.userid
and a.ym = b.ym
and a.amount = b.amount
group by a.userid,
a.ym,
a.amount) t3
on t0.userid = t3.userid
and t0.ym = t3.ym
left join (select a.userid,
a.ym,
count(1) cnt
from tab_v a
where a.type = 1
group by a.userid,
a.ym) t4
on t0.userid = t4.userid
and t0.ym = t4.ym
left join (select a.userid,
a.ym,
a.amount,
group_concat(b.instructions) instructions
from (select a.userid,
a.ym,
max(a.amount) amount
from tab_v a
where a.type = 1
group by a.userid,
a.ym) a,
tab_v b
where a.userid = b.userid
and a.ym = b.ym
and a.amount = b.amount
group by a.userid,
a.ym,
a.amount) t5
on t0.userid = t5.userid
and t0.ym = t5.ym
left join (select a.userid,
a.ym,
a.amount,
group_concat(b.instructions) instructions
from (select a.userid,
a.ym,
min(a.amount) amount
from tab_v a
where a.type = 1
group by a.userid,
a.ym) a,
tab_v b
where a.userid = b.userid
and a.ym = b.ym
and a.amount = b.amount
group by a.userid,
a.ym,
a.amount) t6
on t0.userid = t6.userid
and t0.ym = t6.ym
left join (select a.userid,
a.ym,
sum(case
when type = 1 then
-1 * a.amount
when 2 then
a.amount
end) cnt
from tab_v a
group by a.userid,
a.ym) t7
on t0.userid = t7.userid
and t0.ym = t7.ym
只能做到这个效果了