mysql 表自连接_mysql 一个表自连查询数据？

展开全部

建表和视图语句：

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

只能做到这个效果了