有个统计查询需求,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