CREATE TABLE `deal_` (
`id_` bigint(20) NOT NULL auto_increment,
`user_id_` bigint(20) default NULL,
`count_` bigint(20) default NULL,
PRIMARY KEY (`id_`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;CREATE TABLE `user_` (
`id_` bigint(20) NOT NULL auto_increment,
`parent_id_` bigint(20) default NULL,
PRIMARY KEY (`id_`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
user表中 每个记录(id_)都属于一个父类(parent_id_)(这表里就只有两级)
deal 表中是所有用户的交易的记录(包括个人和父亲的)
怎么能用sql 查询出来 所有 父类的 交易总量和下级所有的子类的交易总量结果如下:父类id 父类的交易总量 下级所有的子类的交易总量
1 100 10000
2 200 50000
`id_` bigint(20) NOT NULL auto_increment,
`user_id_` bigint(20) default NULL,
`count_` bigint(20) default NULL,
PRIMARY KEY (`id_`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;CREATE TABLE `user_` (
`id_` bigint(20) NOT NULL auto_increment,
`parent_id_` bigint(20) default NULL,
PRIMARY KEY (`id_`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
user表中 每个记录(id_)都属于一个父类(parent_id_)(这表里就只有两级)
deal 表中是所有用户的交易的记录(包括个人和父亲的)
怎么能用sql 查询出来 所有 父类的 交易总量和下级所有的子类的交易总量结果如下:父类id 父类的交易总量 下级所有的子类的交易总量
1 100 10000
2 200 50000
sum(count_)父类交易总量,
(select sum(b.count_) from user_ a,deal_ b
where a.id_=b.user_id_ and t.user_id_=a.parent_id_)子类交易总量
from deal_ t
---------- ----------
1 100
2 200
13 200
15 200
22 120
23 1406 rows selected.SQL> select * from users; ID P_ID
---------- ----------
22 2
23 2
13 1
15 1
SQL> select y.p_id,
2 (select sum(count) from deal where user_id=y.p_id) as p_total,y.total
3 from
4 (
5 select
6 distinct p_id,total
7 from
8 (
9 select a.id,a.p_id,
10 sum(b.count) over(partition by p_id) total
11 from users a,deal b
12 where a.id=b.user_id
13 ) x
14 order by 1
15 ) y; P_ID P_TOTAL TOTAL
---------- ---------- ----------
1 100 400
2 200 260