SELECT A.EMP_NO,
A.CORP_CD,
RNAME,
D.DEPT_CD,
TSYAIN_NAME,
A.SYUGYO_MONTH,
A.SYUGYO_DATE,
A.SYUKKIN_YOKUTIME,
A.SYUKKIN_TIME,
A.SYUKKIN_DAKOKU_DATE,
A.SYUKKIN_DAKOKU_TIME,
A.SYUKKIN_DIFFERENCE_MINUTES,
A.TAISYA_YOKUTIME,
A.TAISYA_TIME,
A.TAISYA_DAKOKU_DATE,
A.TAISYA_DAKOKU_TIME,
A.TAISYA_DIFFERENCE_MINUTES,
A.SUM_DAYTIME,
SYUKKIN_DIFFERENCE_MINUTES,
TAISYA_DIFFERENCE_MINUTES,
B.SUM_MINUTES,
F.BIKO
FROM (SELECT T.CORP_CD AS CORP_CD,
T.EMP_NO AS EMP_NO,
CASE
WHEN TO_CHAR(TO_DATE(T.SYUGYO_DATE), 'DD') >=1 THEN
TO_CHAR(ADD_MONTHS(TO_DATE(T.SYUGYO_DATE), 0), 'YYYY/MM')
ELSE
TO_CHAR(TO_DATE(T.SYUGYO_DATE), 'YYYY/MM')
END AS SYUGYO_MONTH,
SYUGYO_DATE,
SYUKKIN_YOKUTIME AS SYUKKIN_YOKUTIME,
SYUKKIN_TIME AS SYUKKIN_TIME,
SYUKKIN_DAKOKU_DATE AS SYUKKIN_DAKOKU_DATE,
SYUKKIN_DAKOKU_TIME AS SYUKKIN_DAKOKU_TIME,
SYUKKIN_DIFFERENCE_MINUTES AS SYUKKIN_DIFFERENCE_MINUTES,
TAISYA_YOKUTIME AS TAISYA_YOKUTIME,
TAISYA_TIME AS TAISYA_TIME,
TAISYA_DAKOKU_DATE AS TAISYA_DAKOKU_DATE,
TAISYA_DAKOKU_TIME AS TAISYA_DAKOKU_TIME,
TAISYA_DIFFERENCE_MINUTES AS TAISYA_DIFFERENCE_MINUTES,
T.SYUKKIN_DIFFERENCE_MINUTES + T.TAISYA_DIFFERENCE_MINUTES AS SUM_DAYTIME
FROM T_DAKOKU_DIFFERENCETIME T
WHERE 1 = 1
AND SYUGYO_DATE >= '2011/06/01' AND SYUGYO_DATE <= '2011/06/30'
AND EMP_NO in ('S000006','S000001','020227','S000002','S000041','S000048','0007','S000008','S000040','S000042','S000043','S000044','S000045','S000007','S000092','S000030','S000046','S000085','S000086','S000090','S000091','S000096','S000031','S000047','S000003','S000098','S000013','S000049','S000100','0001','0002','0003','0004','0005','0006','0008','0009','0010','0011','0012','0013','0014','0015','0016','0017','0018','0019','0020','0021','0022','0023','0024','0025','0026','0027','0028','0029','0030','0031','1','10','100','1000','1001','1002','1003','1004','1005','1006','1007','1008','1009','101','1010','1011','1012','1013','1014','1015','1016','1017','1018','1019','102','1020','1021','1022','1023','1024','1025','1026','1027','1028','1029','103','1030','1031','1032','1033','1034','1035','1036','1037','1038','1039','104','1040','1041','1042','1043','1044','1045','1046','1047','1048','1049','105','1050','1051','1052','1053','1054','1055','1056','1057','1058','1059','106','1060','1061','1062','1063','1064','1065','1066','1067','1068','1069','107','1070','1071','1072','1073','1074','1075','1076','1077','1078','1079','108','1080','1081','1082','1083','1084','1085','1086','1087','1088','1089','109','1090','1091','1092','1093','1094','1095','1096','1097','1098','1099','11','110','1100','1101','1102','1103','1104','1105','1106','1107','1108','1109','111','1110','1111','1112','1113','1114','1115','1116','1117','1118','1119','112','1120','1121','1122','1123','1124','1125','1126','1127','1128','1129','113','1130','1131','1132','1133','1134','1135','1136','1137','1138','1139','114','1140','1141','1142','1143','1144','1145','1146','1147','1148','1149','115','1150','1151','1152','1153','1154','1155','1156','1157','1158','1159','116','1160','1161','1162','1163','1164','1165','1166','1167','1168','1169','117','1170','1171','1172','1173','1174','1175','1176','1177','1178','1179','118','1180','1181','1182','1183','1184','1185','1186','1187','1188','1189','119','1190','1191','1192','1193','1194','1195','1196','1197','1198','1199','12','120','1200','1201','1202','1203','1204','1205','1206','1207','1208','1209','121','1210','1211','1212','1213','1214','1215','1216','1217','1218','1219','122','1220','1221','1222','1223','1224','1225','1226','1227','1228','1229','123','1230','1231','1232','1233','1234','1235','1236','1237','1238','1239','124','1240','1241','1242','1243','1244','1245','1246','1247','1248','1249','125','1250','1251','1252','1253','1254','1255','1256','1257','1258','1259','126','1260','1261','1262','1263','1264','1265','1266','1267','1268','1269','127','1270','1271','1272','1273','1274','1275','1276','1277','1278','1279','128','1280','1281','1282','1283','1284','1285','1286','1287','1288','1289','129','1290','1291','1292','1293','1294','1295','1296','1297','1298','1299','13','130','1300','1301','1302','1303','1304','1305','1306','1307','1308','1309','131','1310','1311','1312','1313','1314','1315','1316','1317','1318','1319','132','1320','1321','1322','1323','1324','1325','1326','1327','1328','1329','133','1330','1331','1332','1333','1334','1335','1336','1337','1338','1339','134','1340','1341','1342','1343','1344','1345','1346','1347','1348','1349','135')
) A,--查询一个月的数据
(
SELECT CORP,EMP_NO,SUM(SUM_MINUTES) AS SUM_MINUTES FROM (
SELECT CORP,
EMP_NO,
SUM(SYUKKIN_DIFFERENCE_MINUTES + TAISYA_DIFFERENCE_MINUTES) AS SUM_MINUTES --只根据emp_no和corp分组查询
FROM (SELECT CORP_CD AS CORP,
EMP_NO,
CASE
WHEN TO_CHAR(TO_DATE(T.SYUGYO_DATE), 'DD') >=1 THEN
TO_CHAR(ADD_MONTHS(TO_DATE(T.SYUGYO_DATE), 0), 'YYYY/MM')
ELSE
TO_CHAR(TO_DATE(T.SYUGYO_DATE), 'YYYY/MM')
END AS SYUGYO_DATE,
T.SYUKKIN_DIFFERENCE_MINUTES,
T.TAISYA_DIFFERENCE_MINUTES
FROM T_DAKOKU_DIFFERENCETIME T
WHERE 1 = 1
AND SYUGYO_DATE >= '2011/06/01' AND SYUGYO_DATE <= '2011/06/30'
) M GROUP BY M.SYUGYO_DATE, M.EMP_NO, CORP
ORDER BY EMP_NO, M.SYUGYO_DATE
)
having sum(SUM_MINUTES) > 20 --最关键的就是这里，如果符合这个条件，就把这个月的所有记录查出来
GROUP BY EMP_NO, CORP
) B,--查询sum结果
(SELECT NAME AS TSYAIN_NAME, MANNO AS EMP_NO, SADS AS SADS FROM TSYAIN
WHERE 1 = 1
AND SDATE <= '2011/06/01' AND EDATE >= '2011/06/30'
) C,--取出姓名(NAME)和部门id(SADS)
( SELECT NVL(RNAME,NAME) AS RNAME,CORP AS CORP,CODE AS DEPT_CD FROM MSZK ) D,--取出部门名(RNAME)
(SELECT CORP, EMP_NO, M.SYUGYO_DATE AS SYUGYO_MONTH
FROM (SELECT CORP_CD AS CORP,
EMP_NO,
CASE
WHEN TO_CHAR(TO_DATE(T.SYUGYO_DATE), 'DD') >=1 THEN
TO_CHAR(ADD_MONTHS(TO_DATE(T.SYUGYO_DATE), 0), 'YYYY/MM')
ELSE
TO_CHAR(TO_DATE(T.SYUGYO_DATE), 'YYYY/MM')
END AS SYUGYO_DATE,
T.SYUKKIN_DIFFERENCE_MINUTES,
T.TAISYA_DIFFERENCE_MINUTES
FROM T_DAKOKU_DIFFERENCETIME T
WHERE 1 = 1
AND SYUGYO_DATE >= '2011/06/01' AND SYUGYO_DATE <= '2011/06/30'
) M GROUP BY M.SYUGYO_DATE, M.EMP_NO, CORP
ORDER BY EMP_NO, M.SYUGYO_DATE
) E,--取出日期(SYUGYO_DATE)属于哪个月
(
SELECT TS.CORP_CD,TS.EMP_NO,TS.SYUGYO_DATE,TS.BIKO FROM T_SYUGYO_DETAIL_DAY TS
WHERE 1 = 1
AND SYUGYO_DATE >= '2011/06/01' AND SYUGYO_DATE <= '2011/06/30'
) F --取出备注(TS.BIKO)
WHERE A.EMP_NO = B.EMP_NO
AND A.EMP_NO = C.EMP_NO
AND A.CORP_CD = D.CORP
AND C.SADS = D.DEPT_CD
AND A.EMP_NO = E.EMP_NO
AND A.SYUGYO_MONTH = E.SYUGYO_MONTH
AND C. EMP_NO = E.EMP_NO
AND F.CORP_CD = D.CORP
AND F.EMP_NO = E.EMP_NO
AND F.SYUGYO_DATE = A.SYUGYO_DATE
ORDER BY
SUM_MINUTES DESC,
EMP_NO ,
A.SYUGYO_DATE
我再想是不是用了3次 CASE
WHEN TO_CHAR(TO_DATE(T.SYUGYO_DATE), 'DD') >=1 THEN
TO_CHAR(ADD_MONTHS(TO_DATE(T.SYUGYO_DATE), 0), 'YYYY/MM')
ELSE
TO_CHAR(TO_DATE(T.SYUGYO_DATE), 'YYYY/MM')
END AS SYUGYO_DATE
而导致的问题？还是 EMP_NO in 的问题?
AND EMP_NO in ('S000006','S000001','020227','S000002','S000041','S000048','0007','S000008','S000040','S000042','S000043','S000044','S000045','S000007','S000092','S000030','S000046','S000085','S000086','S000090','S000091','S000096','S000031','S000047','S000003','S000098','S000013','S000049','S000100','0001','0002','0003','0004','0005','0006','0008','0009','0010','0011','0012','0013','0014','0015','0016','0017','0018','0019','0020','0021','0022','0023','0024','0025','0026','0027','0028','0029','0030','0031','1','10','100','1000','1001','1002','1003','1004','1005','1006','1007','1008','1009','101','1010','1011','1012','1013','1014','1015','1016','1017','1018','1019','102','1020','1021','1022','1023','1024','1025','1026','1027','1028','1029','103','1030','1031','1032','1033','1034','1035','1036','1037','1038','1039','104','1040','1041','1042','1043','1044','1045','1046','1047','1048','1049','105','1050','1051','1052','1053','1054','1055','1056','1057','1058','1059','106','1060','1061','1062','1063','1064','1065','1066','1067','1068','1069','107','1070','1071','1072','1073','1074','1075','1076','1077','1078','1079','108','1080','1081','1082','1083','1084','1085','1086','1087','1088','1089','109','1090','1091','1092','1093','1094','1095','1096','1097','1098','1099','11','110','1100','1101','1102','1103','1104','1105','1106','1107','1108','1109','111','1110','1111','1112','1113','1114','1115','1116','1117','1118','1119','112','1120','1121','1122','1123','1124','1125','1126','1127','1128','1129','113','1130','1131','1132','1133','1134','1135','1136','1137','1138','1139','114','1140','1141','1142','1143','1144','1145','1146','1147','1148','1149','115','1150','1151','1152','1153','1154','1155','1156','1157','1158','1159','116','1160','1161','1162','1163','1164','1165','1166','1167','1168','1169','117','1170','1171','1172','1173','1174','1175','1176','1177','1178','1179','118','1180','1181','1182','1183','1184','1185','1186','1187','1188','1189','119','1190','1191','1192','1193','1194','1195','1196','1197','1198','1199','12','120','1200','1201','1202','1203','1204','1205','1206','1207','1208','1209','121','1210','1211','1212','1213','1214','1215','1216','1217','1218','1219','122','1220','1221','1222','1223','1224','1225','1226','1227','1228','1229','123','1230','1231','1232','1233','1234','1235','1236','1237','1238','1239','124','1240','1241','1242','1243','1244','1245','1246','1247','1248','1249','125','1250','1251','1252','1253','1254','1255','1256','1257','1258','1259','126','1260','1261','1262','1263','1264','1265','1266','1267','1268','1269','127','1270','1271','1272','1273','1274','1275','1276','1277','1278','1279','128','1280','1281','1282','1283','1284','1285','1286','1287','1288','1289','129','1290','1291','1292','1293','1294','1295','1296','1297','1298','1299','13','130','1300','1301','1302','1303','1304','1305','1306','1307','1308','1309','131','1310','1311','1312','1313','1314','1315','1316','1317','1318','1319','132','1320','1321','1322','1323','1324','1325','1326','1327','1328','1329','133','1330','1331','1332','1333','1334','1335','1336','1337','1338','1339','134','1340','1341','1342','1343','1344','1345','1346','1347','1348','1349','135');--涉及到那么多表，可以考虑视图
不过看到你 又是1又是2的 排名还那么前 弄的我没底了 请大拿解释解释 in 和 not in 的效率问题
LZ 弱弱的问一句啊 你那么多子查询 每个里面都需要排序嘛把子查询的 order by 去掉吧