MySQL

面试题
1.遇到过的面试题

SELECT c.year,c.m1,c.m2 FROM (
 SELECT a.year,b.`month`,a.account m1,b.account m2 from a a
 INNER JOIN a b on a.year = b.`year`
 GROUP BY a.year,b.month
) c
WHERE c.month = 1

更新student表数据，删除电话（phone）区号
字符串截取

update student set phone= SUBSTRING_INDEX(phone,'-',-1);

查询每门课程都大于80分的学生姓名
student_results（表名），name（姓名），score（分数）
聚合min()，分组group by，筛选having

select name from (select name,min(score) from student_results group by name having min(score)>80) student_results;

工作遇到
4.对字符串操作的细节

select k.id id, k.name name
        from d_knowledge k
                 join t_question_knowledge qk on qk.know_id = k.id
        where qk.is_last = 1
          and k.subject_id = 2
          and LEFT(detail_id, POSITION(','in detail_id) - 1) =670
        group by k.name

5.截取字符串，拼接字符串，多个id查询

t.id,
            t.content,
            t.content_png contentPng,
            t.answer,
            t.answer_png answerPng,
            t.analysis,
            t.analysis_png analysisPng,
            GROUP_CONCAT( distinct t2.`name`) knowName,
            GROUP_CONCAT( distinct t2.detail_id SEPARATOR ';' ) knowIds,
            GROUP_CONCAT( distinct f1.`name` ) cognName,
            GROUP_CONCAT( distinct f1.detail_id SEPARATOR ';' ) cognIds,
            GROUP_CONCAT( distinct c1.`name` ) compName,
            GROUP_CONCAT( distinct c1.detail_id SEPARATOR ';' ) compIds,
            GROUP_CONCAT( distinct e1.`name` ) abilityName,
            GROUP_CONCAT( distinct e1.detail_id SEPARATOR ';' ) abilityIds
        from
            (
            SELECT
                id,
                content,
                content_png,
                answer,
                answer_png,
                analysis,
                analysis_png,
                category
            FROM
                t_question
            where id IN
<!--        <foreach collection="questionIds" index="index" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>-->
         (6288, 6289, 6290)
            ) t
            left JOIN t_question_knowledge t1 on t.id = t1.question_id
            and t1.is_last = 1
            LEFT JOIN d_knowledge t2 on t1.know_id = t2.id
            and t1.is_last = 1
            left join t_question_cogn f on t.id = f.question_id
            and f.is_last = 1
            left join d_cognition_level f1 on f.cogn_id = f1.id
            left join t_question_comp c on t.id = c.question_id
            and c.is_last = 1
            left join d_key_competence c1 on c.comp_id = c1.id
            left join t_question_ability e on t.id = e.question_id
            and e.is_last = 1
            left join d_key_ability e1 on e.ability_id = e1.id
            left join d_category d on d.id = t.category
            group by t.id

查询一年内的数据

SELECT * FROM yh_content
where create_time>DATE_SUB(CURDATE(), INTERVAL 1 DAY)
where create_time>DATE_SUB(CURDATE(), INTERVAL 1 WEEK)
where create_time>DATE_SUB(CURDATE(), INTERVAL 1 MONTH)
where create_time>DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
注意：如果数据库中时间以UNIX时间戳的形式存放的话，在时间对比上需要更改为统一格式：
DATE_SUB()返回的是格式化后的时间：2014-05-17
需要用UNIX_TIMESTAMP()转化为UNIX时间戳形式对比：
复制代码 代码如下:
 
where create_time>UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 DAY))
where create_time>UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 WEEK))
where create_time>UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 MONTH))
where create_time>UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 YEAR))
详细请查看MySql时间函数：DATE_SUB、DATE_ADD、UNIX_TIMESTAMP等函数的用法

当天或当日插入的数据：

SELECT * FROM `t` WHERE DATEDIFF(create_time,NOW()) =0;

成绩排名：要求分数相同的同排名

#先分析分析
	SELECT
		*,
		@rownum := @rownum + 1 AS temp,
		@incnum :=
	CASE
			
			WHEN @rowtotal = a.totalScore THEN
			@incnum 
			WHEN @rowtotal := a.totalScore THEN
			@rownum 
		END AS rank 
	FROM
		( SELECT job_id, student_id, sum( score ) totalScore FROM s_student_job_response WHERE job_id = 1 GROUP BY student_id ) AS a,
		( SELECT @rownum := 0, @rowtotal := NULL, @incnum := 0 ) b 
	ORDER BY
	totalScore DESC 
	) c

#业务需要
SELECT
        a.*,
        IF
        (@score = totalScore, @rank, @rank := @rank + @sp) rank,
        IF
        (@score = totalScore, @sp := @sp, @sp := 1),
        @score := totalScore
        FROM
        (SELECT
        u.user_name userName,
        tt.*
        FROM
        s_student_job s
        JOIN (
        SELECT
        t.studentId,
        t.job_id,
        group_concat( t.category ORDER BY t.category ASC ) categoryStr,
        sum( t.score ) totalScore,
        group_concat( t.score ORDER BY t.category ASC ) scoreStr,
        group_concat( t.NAME ORDER BY t.category ASC ) categoryNameStr
        FROM
        (
        SELECT
        a.student_id studentId,
        CASE

        WHEN b.data_id > 0 THEN
        d.category ELSE b.category
        END AS category,
        CASE

        WHEN b.data_id > 0 THEN
        f.NAME ELSE c.NAME
        END AS NAME,
        sum( a.score ) score,
        a.job_id
        FROM
        s_student_job_response a
        JOIN t_question b ON a.question_id = b.id
        JOIN d_category c ON b.category = c.id
        LEFT JOIN t_question_data d ON b.data_id = d.id
        LEFT JOIN d_category f ON d.`category` = f.id
        WHERE
        a.job_id =  1
        AND a.is_correct = 1
        GROUP BY
        a.student_id,
        category
        ) t
        GROUP BY
        t.studentId
        ORDER BY
        totalScore DESC
        ) tt
        ON tt.job_id = s.job_id
        AND s.student_id = tt.studentId
        JOIN u_user u ON tt.studentId = u.id
        WHERE
        s.is_correct = 1
        ORDER BY
        tt.totalScore DESC)a ,( SELECT @score:= NULL,@rank := 0,  @sp := 1) b