有个统计查询需求,sql语句不知道怎么写,烦请高手解决:
表格见下:
select * from jd_tree a;--目录树
+------------+-------------+------------+----------+--------------+
| jd_dm | jd_mc | fjd_dm | can_deep | jb_dm |
+------------+-------------+------------+----------+--------------+
| 1000000001 | 根目录 | NULL | Y | 001 |
| 1010000001 | 目录1 | 1000000001 | Y | 001001 |
| 1020000001 | 目录2 | 1000000001 | Y | 001002 |
| 1030000001 | 目录3 | 1000000001 | N | 001003 |
| 1010100001 | 目录1-1 | 1010000001 | N | 001001001 |
| 1010200001 | 目录1-2 | 1010000001 | N | 001001002 |
| 1010300001 | 目录1-3 | 1010000001 | N | 001001003 |
| 1020100001 | 目录2-1 | 1020000001 | N | 001002001 |
| 1020200001 | 目录2-2 | 1020000001 | N | 001002002 |
| 1020300001 | 目录2-3 | 1020000001 | N | 001002003 |
| 1020400001 | 目录2-4 | 1020000001 | Y | 001002004 |
| 1020401001 | 目录2-4-1 | 1020400001 | N | 001002004001 |
| 1020402001 | 目录2-4-2 | 1020400001 | N | 001002004002 |
+------------+-------------+------------+----------+--------------+
select * from jd_djl b;--点击率
+------------+------+
| jd_dm | djl |
+------------+------+
| 1020402001 | 5 |
| 1010100001 | 1 |
| 1010200001 | 1 |
+------------+------+
有两个表格 jd_tree和jd_djl,jd_tree表中jb_dm是依次往后添加的,可用于like
现前台传一个参数jd_dm(为jd_tree表里的jd_dm值),根据前台传递的参数
1) 如前台传递的参数对应的can_deep=’Y’ ,则显示他的下一级各个的点击率;
2) 如前台传递的参数对应的can_deep=’N’,则显示该jd_dm的点击率
举例1:如传递过来的是1000000001,则应显示成下面这样
| jd_dm | jd_mc |djl |
| 1010000001 | 目录1 | 2
| 1020000001 | 目录2 | 5
| 1030000001 | 目录3 | 0
举例2:如传递过来的是1030000001,则应显示成下面这样
| jd_dm | jd_mc |djl |
| 1030000001 | 目录3 | 0
表格见下:
select * from jd_tree a;--目录树
+------------+-------------+------------+----------+--------------+
| jd_dm | jd_mc | fjd_dm | can_deep | jb_dm |
+------------+-------------+------------+----------+--------------+
| 1000000001 | 根目录 | NULL | Y | 001 |
| 1010000001 | 目录1 | 1000000001 | Y | 001001 |
| 1020000001 | 目录2 | 1000000001 | Y | 001002 |
| 1030000001 | 目录3 | 1000000001 | N | 001003 |
| 1010100001 | 目录1-1 | 1010000001 | N | 001001001 |
| 1010200001 | 目录1-2 | 1010000001 | N | 001001002 |
| 1010300001 | 目录1-3 | 1010000001 | N | 001001003 |
| 1020100001 | 目录2-1 | 1020000001 | N | 001002001 |
| 1020200001 | 目录2-2 | 1020000001 | N | 001002002 |
| 1020300001 | 目录2-3 | 1020000001 | N | 001002003 |
| 1020400001 | 目录2-4 | 1020000001 | Y | 001002004 |
| 1020401001 | 目录2-4-1 | 1020400001 | N | 001002004001 |
| 1020402001 | 目录2-4-2 | 1020400001 | N | 001002004002 |
+------------+-------------+------------+----------+--------------+
select * from jd_djl b;--点击率
+------------+------+
| jd_dm | djl |
+------------+------+
| 1020402001 | 5 |
| 1010100001 | 1 |
| 1010200001 | 1 |
+------------+------+
有两个表格 jd_tree和jd_djl,jd_tree表中jb_dm是依次往后添加的,可用于like
现前台传一个参数jd_dm(为jd_tree表里的jd_dm值),根据前台传递的参数
1) 如前台传递的参数对应的can_deep=’Y’ ,则显示他的下一级各个的点击率;
2) 如前台传递的参数对应的can_deep=’N’,则显示该jd_dm的点击率
举例1:如传递过来的是1000000001,则应显示成下面这样
| jd_dm | jd_mc |djl |
| 1010000001 | 目录1 | 2
| 1020000001 | 目录2 | 5
| 1030000001 | 目录3 | 0
举例2:如传递过来的是1030000001,则应显示成下面这样
| jd_dm | jd_mc |djl |
| 1030000001 | 目录3 | 0
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货