一、查询到位积极名单
SELECT
xm,
count(*) nums
FROM
suyang_test
WHERE
njid = '3' AND bjid = '2' AND schoolyear = '2023-2024'
AND xmid = '1'
AND syid = '2'
AND riqi BETWEEN '2023-09-01 00:00:00' AND '2023-10-19 20:20:20'
GROUP BY
xm
ORDER BY
nums DESC
二、查询课堂积极名单
SELECT
xm,
count(*) nums
FROM
suyang_test
WHERE
njid = '3' AND bjid = '2' AND schoolyear = '2023-2024'
AND xmid = '2'
AND syid = '13'
AND riqi BETWEEN '2023-09-01 00:00:00' AND '2023-10-19 20:20:20'
GROUP BY
xm
ORDER BY
nums DESC
三、查询主动回答名单
SELECT
xm,
count(*) nums
FROM
suyang_test
WHERE
njid = '3' AND bjid = '2' AND schoolyear = '2023-2024'
AND xmid = '2'
AND syid = '10'
AND riqi BETWEEN '2023-09-01 00:00:00' AND '2023-10-19 20:20:20'
GROUP BY
xm
ORDER BY
nums DESC
四、总体贡献最多名单
在系统中查看系统自动生成的即可。
五、mysql update语句时，在原字段上追加数据concat关键字
1、现在user表上，name 字段上加上100，update 表set 字段= concat(字段名,"要增加的数据") WHERE 条件，这样是可以的，CONCAT（）函数用于将多个字符串连接成一个字符串。
2、要是在名字前面加则set 字段= concat("要增加的数据",字段名) WHERE 条件
3、其他教程：链接地址
六、选课删除重复数据且保留最后一次提交数据
第一步，备份数据库。
第二步，选择出来，看看是否符合预期，防止误删
SELECT
*
FROM
cs_selstu
WHERE
YEAR = '2023'
AND id NOT IN (
SELECT
t.max_id
FROM
( SELECT max( id ) AS max_id FROM cs_selstu GROUP BY sid, sname ) AS t
);
第三步，正式删除之
DELETE
FROM
cs_selstu
WHERE
YEAR = '2023'
AND id NOT IN (
SELECT
t.max_id
FROM
( SELECT max( id ) AS max_id FROM cs_selstu GROUP BY sid, sname ) AS t
);
总结，实际上只需更改【2023】这个年份即可
五、多种情况模糊查询
以下可根据【$keywords】查询【拼音首字母、拼音自定义、拼音全拼、姓名、电话】甚至可以增加更多
SELECT
xm,
dianhua,
bjid,
sex,
xjh
FROM
xsinfo
WHERE
instr( CONCAT( pyszm, pyzdy, pyqp, xm, dianhua ), '$keywords' )
ORDER BY
id DESC
LIMIT 0,9