mysql

1.https://blog.csdn.net/qq_36802726/article/details/81208853     SpringBoot jpa多条件查询（参数可能为空）语句

2.MyBatis查询语句写法

 <select id="findHomeCondition" resultType="HashMap">
        SELECT
        proj.proj_code AS 'project_code',
        proj.proj_name AS 'project_name',
        proj.pk_proj AS 'pk_project',
        res.pk_user_user_id AS 'user',
        COUNT(res.pk_user_user_id) AS 'count',
        tab.`manager`, tab.managerId AS managerId,
        dept.name
        FROM project proj
        LEFT JOIN resource res ON res.pk_proj_pk_proj = proj.pk_proj
        LEFT JOIN user user ON res.pk_user_user_id = `user`.USER_ID
        LEFT JOIN dept dept ON proj.dept_id = dept.id
        LEFT JOIN (
        SELECT
        user.USER_NAME AS 'manager',
        user.user_id AS managerId,
        res1.id AS table_id,
        res1.pk_proj_pk_proj AS pk_proj
        FROM resource res1
        LEFT JOIN user user
        ON res1.pk_user_user_id = `user`.user_id
        LEFT JOIN project proj1
        ON res1.pk_proj_pk_proj = proj1.pk_proj
        WHERE is_pm = 1
        ) tab
        ON tab.table_id = res.id
        <trim prefix="where" suffixOverrides="and">
            <if test="projName != null and projName != ''">
                proj.proj_name like '${projName}' and
            </if>
            <if test="projCode != null and projCode != ''">
                proj.proj_code like '${projCode}' and
            </if>
            proj.status in (3, 4) and
            <if test="deptSet.size>0">
                 dept.id in
                <foreach item="item" index="index" collection="deptSet"  open="(" separator="," close=")">
                #{item}
            </foreach>
            </if>
        </trim>
        GROUP BY proj.proj_code
        ORDER BY proj.create_time DESC
        <if test="pageable.pageNumber != null">
            limit ${pageable.pageNumber * pageable.pageSize}, ${pageable.pageSize}
        </if>
    </select>

3.jpa查询

模糊查询 M2019XXX

 @Query(value = "select count(pk_proj) from project where INNER_CONTRACT like :contractName%",nativeQuery = true)

其他查询

 /**
     * 查询当月到现在,填写周报数
     * @param userId
     * @return
     */
    @Query(value = "SELECT count(weekly_id) from weekly where user_id=?1 and  submit_time>=(DATE_FORMAT( CURDATE(), '%Y-%m-01 00:00:00'))AND submit_time<= now()",nativeQuery = true)
    Long findMonthWeeklyCount(Long userId);

    /**
     * 查询周五未填写周报的user_id
     * @return
     */
    @Query(value = "select  user_info.user_id from user_info  left join weekly on user_info.user_id=weekly.user_id  and  DATEDIFF(now(),weekly.submit_time) <7 AND DATEDIFF(now(),weekly.submit_time)>0  where user_info.user_id not  in (select DISTINCT user_id from  weekly  where  DATEDIFF(now(),submit_time) <7 AND DATEDIFF(now(),submit_time)>0) ",nativeQuery = true)
    List<BigInteger> unfilledReminder();

    /**
     * 没有审批条数(当月到现在)
     * @param userId
     * @return
     */
    @Query(value="SELECT count(weekly_id) FROM weekly WHERE approver_id=?1 and approver_status=2 and submit_time>=(DATE_FORMAT( CURDATE(), '%Y-%m-01 00:00:00'))AND submit_time<= now()",nativeQuery = true)
    Long monthWeeklyNoApprovelCount(Long userId);

    /**
     * 选择日期查看填写
     * @param userId
     * @param queryData
     * @return
     */
    @Query(value="select weekly_id,approver,approver_id,approver_status,approver_time,submit_time,submitter,work_completion,learning_completion,exp_and_rec_Summary,work_plan,learning_plan,user_id from Weekly where user_id=?1 and  TO_DAYS(submit_time)=TO_DAYS(?2)",nativeQuery = true)
    WeeklyEntry dateQueryList(Long userId, Date queryData);

    /**
     * 判断是否是主查看人和次查看人
     */
    @Query(value="select count(user_id) from user where report_id=?1",nativeQuery = true)
    Long findIsSeePerson(Long userId);

    /**
     * 主查看人查看
     * @param userId
     * @param queryData
     * @return
     */
    @Query(value="select weekly_id,approver,approver_id,approver_status,approver_time,submit_time,submitter,work_completion,learning_completion,exp_and_rec_Summary,work_plan,learning_plan,user_id from Weekly where approver_id=?1 and  TO_DAYS(submit_time)=TO_DAYS(?2)",nativeQuery = true)
    List<WeeklyEntry> mainQueryList(Long userId, Date queryData);

    /**
     * 次查看人查看
     * @param userId
     * @param queryData
     * @return
     */
    @Query(value="select weekly_id,approver,approver_id,approver_status,approver_time,submit_time,submitter,work_completion,learning_completion,exp_and_rec_Summary,work_plan,learning_plan,user_id from Weekly where user_id in ?1 and TO_DAYS(submit_time)=TO_DAYS(?2)",nativeQuery = true)
    List<WeeklyEntry> secondaryQueryList(List <Integer>  userId, Date queryData);

4. mysql出现too many connections错误   解决办法

 全连接(1)

SELECT
	a.media_type AS mediaType,
	count(a.media_type) AS dataNum,
	b.mediaName AS mediaName
FROM
	biz_crl_whole_network_data a
LEFT JOIN biz_crl_media_type b ON a.media_type = b.id
LEFT OUTER JOIN biz_crl_monitor_account_data c ON a.url = c.url
WHERE
	to_days(a.writingtime) = to_days('2019-08-28')
GROUP BY
	a.media_type
UNION
	SELECT
		c.media_type AS cmediaType,
		count(c.media_type) AS cdataNum,
		b.mediaName AS mediaName
	FROM
		biz_crl_whole_network_data a
	LEFT JOIN biz_crl_media_type b ON a.media_type = b.id
	RIGHT OUTER JOIN biz_crl_monitor_account_data c ON a.url = c.url
	WHERE
		to_days(c.writingtime) = to_days('2019-08-28')
	GROUP BY
		c.media_type

 结果如下：

 

  全连接(2)

SELECT
	*
FROM
	biz_crl_whole_network_data a
LEFT OUTER JOIN biz_crl_monitor_account_data c ON a.url = c.url
LEFT JOIN biz_crl_media_type b ON a.media_type = b.id
WHERE
	to_days(a.writingtime) = to_days('2019-08-28')
GROUP BY
	a.id
UNION
	SELECT
		*
	FROM
		biz_crl_whole_network_data a
	RIGHT OUTER JOIN biz_crl_monitor_account_data c ON a.url = c.url
	LEFT JOIN biz_crl_media_type b ON c.media_type = b.id
	WHERE
		to_days(c.writingtime) = to_days('2019-08-28')
	GROUP BY
		c.id

结果如下：