面试题
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