考勤项目查询sql,用于温习,觉得可以的时候要删掉
来源:互联网 发布:c语言debug是什么意思 编辑:程序博客网 时间:2024/06/08 19:31
SELECT
mtable.kq_classesteam_id,
mtable.kq_classesteam_name,
mtable.emp_number,
mtable.kq_classesteam_orderclass_type,
GROUP_CONCAT(mtable.kq_classes_name) classes_name
FROM
(
SELECT DISTINCT
ct.kq_classesteam_id,
ct.kq_classesteam_name,
te.emp_number,
ct.kq_classesteam_orderclass_type,
tc.kq_classes_id,
kqc.kq_classes_name
FROM
kq_classesteam ct
LEFT JOIN (
SELECT
kq_classesteam_id,
count(kq_teamempl_empid) emp_number
FROM
kq_teamempl kqt
WHERE
kqt.kq_teamempl_delflag = 0
AND kqt.kq_teamempl_comno = 123
GROUP BY
kq_classesteam_id
) te ON (
ct.kq_classesteam_id = te.kq_classesteam_id
AND ct.kq_classesteam_comno = 123
AND ct.kq_classesteam_delflag=0
AND ct.kq_classesteam_deldate IS NULL
)
LEFT JOIN kq_teamdateclass tc ON (
ct.kq_classesteam_id = tc.kq_classesteam_id
AND tc.kq_teamdateclass_delflag = 0
AND tc.kq_teamdateclass_comno=123
AND tc.kq_teamdateclass_deldate IS NULL
)
LEFT JOIN kq_classes kqc ON (
tc.kq_classes_id = kqc.kq_classes_id
AND kqc.kq_classes_comno=123
AND kqc.kq_classes_delflag=0
AND kqc.kq_classes_deldate is NULL
)
WHERE
ct.kq_classesteam_delflag = 0
AND ct.kq_classesteam_comno=123
AND ct.kq_classesteam_deldate IS NULL
) mtable
GROUP BY
mtable.kq_classesteam_id
-- 根据班组id查询
SELECT
ct.kq_classesteam_id,
ct.kq_classesteam_name,
te.te_empid,
te.emp_number,
ct.kq_classesteam_orderclass_type,
tc.kq_classes_id,
tc.kq_teamdateclass_date,
CASE tc.kq_teamdateclass_date WHEN '周一' THEN '1' WHEN '周二' THEN '2' WHEN '周三' THEN '3' WHEN '周四' THEN '4' WHEN '周五' THEN '5' WHEN '周六' THEN '6' WHEN '周日' then'7' ELSE 0 END shunxu,
kqc.kq_classes_name,
kqat.timename
FROM
kq_classesteam ct
LEFT JOIN (
SELECT
kq_classesteam_id,
group_concat(kq_teamempl_empid) te_empid,
count(kq_teamempl_empid) emp_number
FROM
kq_teamempl kqt
WHERE
kqt.kq_teamempl_delflag = 0
AND kqt.kq_teamempl_deldate IS NULL
AND kqt.kq_teamempl_comno = 123
GROUP BY
kq_classesteam_id
) te ON (
ct.kq_classesteam_id = te.kq_classesteam_id
)
LEFT JOIN kq_teamdateclass tc ON (
ct.kq_classesteam_id = tc.kq_classesteam_id
AND tc.kq_teamdateclass_delflag = 0
AND tc.kq_teamdateclass_comno =123
AND tc.kq_teamdateclass_deldate IS NULL
)
LEFT JOIN kq_classes kqc ON (
tc.kq_classes_id = kqc.kq_classes_id
AND kqc.kq_classes_delflag = 0
AND kqc.kq_classes_deldate IS NULL
AND kqc.kq_classes_comno =123
)
LEFT JOIN (
SELECT
kq_classes_id,
group_concat(
concat_ws(
'~',
kq_attendancetime_checkin_time,
kq_attendancetime_checkout_time
)
) AS timename
FROM
kq_attendancetime kqa
WHERE
kqa.kq_attendancetime_comno =123
GROUP BY
kq_classes_id
) kqat ON (
kqc.kq_classes_id = kqat.kq_classes_id
)
WHERE
ct.kq_classesteam_delflag = 0
AND ct.kq_classesteam_deldate IS NULL
AND ct.kq_classesteam_comno = 123
AND ct.kq_classesteam_id = 2
ORDER BY
shunxu
-- 人员列表
SELECT
kda.kq_dayAttendance_empid,
group_concat( ( CASE WHEN kda.kq_dayAttendance_time = '2017-04-03' THEN kda.kq_classes_name ELSE NULL END ) ) AS classname_1,
group_concat( ( CASE WHEN kda.kq_dayAttendance_time = '2017-04-04' THEN kda.kq_classes_name ELSE NULL END ) ) AS classname_2,
group_concat( ( CASE WHEN kda.kq_dayAttendance_time = '2017-04-05' THEN kda.kq_classes_name ELSE NULL END ) ) AS classname_3,
group_concat( ( CASE WHEN kda.kq_dayAttendance_time = '2017-04-06' THEN kda.kq_classes_name ELSE NULL END ) ) AS classname_4,
group_concat( ( CASE WHEN kda.kq_dayAttendance_time = '2017-04-07' THEN kda.kq_classes_name ELSE NULL END ) ) AS classname_5,
group_concat( ( CASE WHEN kda.kq_dayAttendance_time = '2017-04-08' THEN kda.kq_classes_name ELSE NULL END ) ) AS classname_6,
group_concat( ( CASE WHEN kda.kq_dayAttendance_time = '2017-04-09' THEN kda.kq_classes_name ELSE NULL END ) ) AS classname_7,
kda.kq_classesteam_id,
ROUND(SUM(kqhb.workhour), 1) worktime
FROM
kq_dayAttendance kda
LEFT JOIN (
SELECT
da.kq_dayAttendance_empid,
da.kq_classes_id,
da.kq_dayAttendance_id,
kqda.kq_dayAttendancetime_checkintime,
kqda.kq_dayAttendancetime_checkouttime,
kqda.workhour
FROM
kq_dayAttendance da
LEFT JOIN (
SELECT
kq_dayAttendancetime_oldclassid,
kq_dayAttendance_id,
FORMAT(SUM(TIMESTAMPDIFF( MINUTE,kq_dayAttendancetime_checkintime,CASE WHEN kq_dayAttendancetime_checkintime > kq_dayAttendancetime_checkouttime
THEN DATE_ADD(kq_dayAttendancetime_checkouttime,INTERVAL 24 HOUR) ELSE kq_dayAttendancetime_checkouttime END )) / 60,1) workhour,
kq_dayAttendancetime_checkintime,
kq_dayAttendancetime_checkouttime
FROM
kq_dayAttendancetime daya
WHERE
daya.kq_dayAttendancetime_comno =123
GROUP BY
daya.kq_dayAttendance_id
) kqda ON da.kq_dayAttendance_id = kqda.kq_dayAttendance_id
AND da.kq_classes_id = kqda.kq_dayAttendancetime_oldclassid
WHERE
da.kq_dayAttendance_delflag = 0
AND da.kq_dayAttendance_comno = 123
AND da.kq_dayAttendance_deldate IS NULL
AND da.kq_dayAttendance_time >= '2017-04-03'
AND da.kq_dayAttendance_time <= '2017-04-09'
) kqhb ON kda.kq_dayAttendance_id = kqhb.kq_dayAttendance_id
WHERE
kda.kq_dayAttendance_delflag = 0
AND kda.kq_dayAttendance_comno = 123
AND kda.kq_dayAttendance_deldate IS NULL
GROUP BY
kda.kq_dayAttendance_empid
- 考勤项目查询sql,用于温习,觉得可以的时候要删掉
- 当觉得自己的水平停滞不前的时候可以看看
- C++ 的 RTTI 可以删掉了
- oracle dblink的使用(可以用于查询远程数据库)
- 我觉得是时候要真的开始拼命了
- sql 考勤
- 实习了一段时间觉得浪费了很多时间,觉得可以把项目组封装的webui学习一下
- 关于统计考勤记录的一段SQL
- Acm TLE的时候删掉回车和空格试试
- 当你觉得累的时候!
- Sql - EXISTS 用于子查询
- 时间工具类,主要用于sql的时间段查询
- 项目终于到了可以轻松下的时候,^_^
- 自定义可以显示考勤状态的日历控件
- seo链接策略:什么样的链接可以删掉?
- 写code的时候觉得写code累,写文字的时候觉得写文字累
- 我觉得还可以的博客哦
- 注意使用Ibatis做like查询的时候SQL注入。
- Python zip函数介绍
- postman
- java 判断字符串是否为空的四种方法
- Swift 系统学习 20 结构体 添加可失败的构造方法
- $.each(json,function(index,item){ }); 中的2个参数的意思
- 考勤项目查询sql,用于温习,觉得可以的时候要删掉
- 牛腩(7)-关于 CSS 的部分内容
- std::thread详解
- 常用设计模式
- maven+spring测试出现 java.lang.IllegalStateException: Failed to load ApplicationContext异常
- 常用工具方法整理
- Struts2 用 s:if test 判断字符串相等的方法
- javascript时间和日期,实现时钟功能
- PHP异步处理方法