SQL小练手，孰能生巧！

1.行为分析

数据源：

	t_driver 驾驶员表（与行为表相关联）
 	t_sys_user 用户信息表
	t_feedback 行为配置表（配置公司下车辆类型的行为）
 	t_car_type 车辆类型
 	t_company 公司

需求：可以查询用户所选择的行为次数及比例；
思路：将各个公司下对应车辆类型的驾驶员选择的行为总数查询出来，再除以对应公司下所有驾驶员即可得到比例（使用了个嵌套查询，内层为数据源，外层进行一个分组及每组的总数）

mapper:

IPage<RiskAnalyzeDao> riskAnalyze(@Param("companyId") String companyId, @Param("page") PeakPage page);

 <select id="riskAnalyze" resultType="com.hb.common.common.dto.admin.RiskAnalyzeDao">
        SELECT
        t.title,
        t.names AS carType,
        t.company_name,
        count(*) AS count
        FROM
        (
        SELECT
        pf.title,
        pct.names,
        psu.company_id,
        pc.company_name
        FROM
        t_driver pd
        LEFT JOIN t_sys_user psu ON pd.user_id = psu.id
        LEFT JOIN t_feedback pf ON pd.feedback_id = pf.id
        LEFT JOIN t_car_type pct ON pct.id = pd.car_type_id
        LEFT JOIN t_company pc ON psu.company_id = pc.id
        WHERE
        pd.deleted = 0
        AND psu.deleted = 0
        AND pf.deleted =0
        AND psu.client_type LIKE '%APP%'
        <if test="companyId != null and companyId != '' and companyId != '0'.toString()">
            AND psu.company_id=#{companyId}
        </if>
        <if test="page.search != null">
            <if test="page.search.carTypeId != null and page.search.carTypeId != ''">
                AND pd.car_type_id =#{page.search.carTypeId}
            </if>
        </if>
        ) t
        GROUP BY title,company_id,carType
        ORDER BY count DESC
    </select>

业务处理：

PeakPage<RiskAnalyzeParam> page = new PeakPage<>(param.getPage(), param.getPageSize());

        if (param.getSearch() != null) {
            page.setSearch(param.getSearch());
        }

        //当前登录人员所属公司下的驾驶员人数
        Double driverCount = driverMapper.getDriverCount(companyId, page.getSearch().getCarTypeId());
        //驾驶员选择内容及次数
        IPage<RiskAnalyzeDao> riskAnalyze = driverMapper.riskAnalyze(companyId, page);
        //数据处理
        if (CollectionUtils.isNotEmpty(riskAnalyze.getRecords())) {
            riskAnalyze.getRecords().forEach(item ->

                    item.setProportion(NumberUtil.div(BigDecimal.valueOf(item.getCount()), BigDecimal.valueOf(driverCount))
                            .setScale(2, BigDecimal.ROUND_CEILING).doubleValue())
            );
        }

        return riskAnalyze;

2.获取权限：

数据源：
	t_sys_user 用户信息表
	t_sys_menu 菜单表
	t_sys_role 角色表
	t_sys_role_menu 角色菜单关联表
	t_sys_user_role 用户角色关联表

    <select id="getAuthority" resultType="java.lang.String">
        select uniq_key from t_sys_menu where id in (
        select menu_id from t_sys_role_menus where role_id in (
        select role_id from t_sys_user_roles where user_id = #{userId} and deleted = 0
        ) and deleted = 0
        )
        union
        select DISTINCT authority as uniq_key from t_sys_roles
        where id in (
            select role_id from t_sys_user_roles where user_id = #{userId} and deleted = 0
        ) and deleted = 0
    </select>

3.获取角色菜单

    <select id="listByRoles" resultType="com.hb.common.system.entity.Menu">
        select m.*,
       if((
              select count(1) from t_sys_role_menus where deleted = 0 and  menu_id = m.id and role_id in (
                  select role_id from t_sys_role_menus where role_id = #{aimId} and deleted = 0
              )
          )>0,1,0) has_role
        from t_sys_menu m where deleted = 0 order by sort asc
    </select>

4.查询子集菜单

    <select id="getMenuByButton" resultType="com.hb.common.system.entity.Menu">
        select * from t_sys_menu
        where deleted = 0
        <if test="userId != 'ADMIN'">
            and id in (
            select menu_id from t_sys_role_menus where role_id in (
            select role_id from t_sys_user_roles where user_id = #{userId} and deleted = 0
            ) and deleted = 0 )
        </if>
        and parent = #{parentId}
    </select>

5.查询用户菜单

    <select id="listMenu" resultMap="listMenu">
        select a.*, concat('',#{userId},'') as user from t_sys_menu a where id in (
        select menu_id from t_sys_role_menus where role_id in (
            select role_id from t_sys_user_roles where user_id = #{userId} and deleted = 0
        ) and deleted = 0 ) and deleted = 0 and type = 0 order by sort asc
    </select>

6.获取最近 10次 个人的类型数据
查询：10次行程有效距离，以及各行为次数（在详情中，有个状态字段区别）
思路：先从主表中获取数据，在嵌套一个子查询，根据主表的ID去查询子表数据，再对他们进行一个求和；

   <select id="getCalculate" resultType="com.hb.common.dto.app.DriverCalculateDao">
	SELECT
		sum( c.distance ) AS distance,
		SUM( c.number ) AS number 
	FROM
		(
		SELECT
			a.distance,(
			SELECT
				count( b.id ) 
			FROM
				t_journet_detail b 
			WHERE
				a.id = b.journey_id 
				AND b.dangerous_behavior = #{type} and b.deleted=0) as number
				
			FROM
				t_journey a 
			WHERE
				a.user_id = #{userId} and  a.type='4' ORDER BY a.create_date desc limit 10
				
			) c
   </select>