callie的提问 - 我的帆软

ORACLE里面这两个应该怎么拼在一起

这个是A 这个是BA是上下班时间B是加班时间两个表里面的日期都是不全的怎么拼在一起先把两个表里面都有的日期都求出来然后再join吗我想要的日期姓名部门然后两个表后面的这些

361个浏览 4个回答 2022年08月17日发布

oracle的unionall咋写一直报错

SELECT TO_CHAR(JB.出勤日期, 'YYYY-MM-DD') AS 日期,JB.MEMBER_NAME,JB.DEPARTMENT_NAME,JB.正式外包区分,JB.上班签到开始时间,JB.下班开始时间 FROM(SELECT E.出勤日期, E.MEMBER_NAME, E.DEPARTMENT_NAME, E.正式外包区分, MIN(CASE WHEN E.打卡目的 = '上班' THEN E.签到开始时间 ELSE NULL END) 上班签到开始时间, MAX(CASE WHEN E.打卡目的 = '下班' THEN E.签到开始时间 ELSE NULL END) 下班开始时间 FROM ( SELECT S.WORK_DATE / (1000 * 60 * 60 * 24)+ TO_DATE('1970-01-01 08:00:00', 'YYYY-MM-DD HH24:MI:SS') AS 出勤日期, S.SIGN_DATETIME / (1000 * 60 * 60 * 24)+ TO_DATE('1970-01-01 08:00:00', 'YYYY-MM-DD HH24:MI:SS') AS 签到开始时间, CASE WHEN S.SIGN_STATE = 1 THEN '正常' WHEN S.SIGN_STATE = 2 THEN '迟到' WHEN S.SIGN_STATE = 3 THEN '早退' WHEN S.SIGN_STATE = 4 THEN '矿工' WHEN S.SIGN_STATE = 5 THEN '请假' WHEN S.SIGN_STATE = 6 THEN '公出' WHEN S.SIGN_STATE = 7 THEN '加班免签' WHEN S.SIGN_STATE = 8 THEN '免签' WHEN S.SIGN_STATE = 9 THEN '无考勤' WHEN S.SIGN_STATE = 10 THEN '无效打卡' END AS 打卡状态, M.MEMBER_NAME, M.DEPARTMENT_NAME, CASE WHEN S.CREATE_TYPE = 1 THEN '排班' WHEN S.CREATE_TYPE = 2 THEN '请假' END AS 打卡原因, CASE WHEN S.ONOFF_STATE = 1 THEN '上班' WHEN S.ONOFF_STATE = 2 THEN '下班' END AS 打卡目的, CASE WHEN M.ACCOUNT_ID = '670869647114347' THEN '正式' ELSE '外包' END AS 正式外包区分 FROM V3XUSER.ATT_SIGN_DETAIL S LEFT JOIN V3XUSER.ATT_MEMBER M ON S.MEMBER_ID = M.MEMBER_ID WHERE ( M.DEPARTMENT_NAME = '来料检验' OR M.DEPARTMENT_NAME = '来料检查组' OR M.DEPARTMENT_NAME = '出货检验' OR M.DEPARTMENT_NAME = '过程检验' ) AND M.SCHEMEOVERTIME_SRC >0 AND S.SIGN_DATETIME IS NOT NULL)E WHERE E.出勤日期 >= TO_DATE('2022-07-01', 'YYYY-MM-DD') AND E.出勤日期 < TO_DATE('2022-08-01', 'YYYY-MM-DD')+ 1 GROUP BY E.出勤日期, E.MEMBER_NAME, E.DEPARTMENT_NAME, E.正式外包区分)JB UNION ALL SELECT TO_CHAR(开始,'YYYY-MM-DD')AS 日期,MEMBER_NAME,DEPARTMENT_NAME,OVERTIME_TYPE,正式外包区分,判断 FROM (SELECT tmp.开始, tmp.结束, tmp.减小时与不减, tmp.OT_MINUTE, tmp.OT_HOURS, tmp.MEMBER_NAME, tmp.DEPARTMENT_NAME, tmp.OVERTIME_TYPE, tmp.DEPARTMENT_ID, tmp.正式外包区分, CASE WHEN instr(tmp.减小时与不减, '.')= 0 AND tmp.减小时与不减*1>=1 THEN tmp.减小时与不减 WHEN tmp.减小时与不减*1>=0.499999 AND tmp.减小时与不减*1<1 THEN 0.5 WHEN tmp.减小时与不减*1>=0 AND tmp.减小时与不减*1<0.5 THEN 0 WHEN substr(tmp.减小时与不减, instr(tmp.减小时与不减, '.')+ 1, 1)* 1 >= 5 AND tmp.减小时与不减*1>=1 THEN substr(tmp.减小时与不减, 1, instr(tmp.减小时与不减, '.')-1)+ 0.5 WHEN substr(tmp.减小时与不减, instr(tmp.减小时与不减, '.')+ 1, 1)* 1 <5 AND tmp.减小时与不减*1>=1 then substr(tmp.减小时与不减, 1 , instr(tmp.减小时与不减, '.')-1)+ 0 END AS 判断 FROM ( SELECT t.开始, t.结束, round((t.结束-t.开始)* 24-1) AS 小时, t.OT_MINUTE, t.OT_HOURS, t.MEMBER_NAME, t.DEPARTMENT_NAME, t.OVERTIME_TYPE, t.DEPARTMENT_ID, t.正式外包区分, CASE WHEN to_char(t.开始, 'HH24')* 1 <= 11 AND to_char(t.结束, 'HH24')* 1 >= 13 THEN (t.结束-t.开始)* 24-1 ELSE (t.结束-t.开始)* 24 END AS 减小时与不减 FROM ( SELECT S.OT_START_DATETIME / (1000 * 60 * 60 * 24)+ TO_DATE('1970-01-01 08:00:00', 'YYYY-MM-DD HH24:MI:SS') AS 开始, S.OT_END_DATETIME / (1000 * 60 * 60 * 24)+ TO_DATE('1970-01-01 08:00:00', 'YYYY-MM-DD HH24:MI:SS') AS 结束, S.OVERTIME_TYPE, S.OT_MINUTE, S.OT_HOURS, M.MEMBER_NAME, M.DEPARTMENT_NAME, M.DEPARTMENT_ID, CASE WHEN M.DEPARTMENT_ID ='-2670959779560862801' THEN '外包' WHEN M.DEPARTMENT_ID ='-6677953676770980574' THEN '正式' WHEN M.DEPARTMENT_ID ='-6898710850797023410' THEN '正式' WHEN M.DEPARTMENT_ID ='5519322349391003696' THEN '外包' WHEN M.DEPARTMENT_ID ='-2175274102399940138' THEN '正式' END AS 正式外包区分 FROM V3XUSER.ATT_OVERTIME_DETAILS S LEFT JOIN V3XUSER.ATT_MEMBER M ON S.MEMBER_ID = M.MEMBER_ID WHERE M.SCHEMEOVERTIME_SRC >0 ) twHERE T.DEPARTMENT_NAME='来料检验' OR T.DEPARTMENT_NAME='来料检查组' OR T.DEPARTMENT_NAME='出货检验' OR T.DEPARTMENT_NAME='过程检验') tmpWHERE tmp.减小时与不减>0 AND tmp.开始 >= TO_DATE('2022-07-01','YYYY-MM-DD') AND tmp.结束 < TO_DATE('2022-08-01','YYYY-MM-DD')+1) ------我想要按日期和人和部门把这些后面的都合起来

350个浏览 3个回答 2022年08月17日发布

不同库查出来的东西可以leftjoin吗

我oracle里面查出来的结果，拼一个sqlserver的简称可以实现吗ds1 ds2怎么合起来我有一个图标下面是供应商的全称我是用DS1的数据源然后我有一个全称和简称的对应表是DS2请问怎么合并

355个浏览 3个回答 2022年08月12日发布

老板让我做个周的现在又让我做个日的报表这么多怎么写啊

with a as (select '1周' , 1 ,CASE WHEN 1>datepart(WK, getdate()) THEN '未到' ELSE '已过' END AS 判断1union allselect '2周', 2,CASE WHEN 2>datepart(WK, getdate()) THEN '未到' ELSE '已过' END AS 判断1union allselect '3周', 3,CASE WHEN 3>datepart(WK, getdate()) THEN '未到' ELSE '已过' END AS 判断1union allselect '4周', 4,CASE WHEN 4>datepart(WK, getdate()) THEN '未到' ELSE '已过' END AS 判断1union allselect '5周', 5,CASE WHEN 5>datepart(WK, getdate()) THEN '未到' ELSE '已过' END AS 判断1union allselect '6周', 6,CASE WHEN 6>datepart(WK, getdate()) THEN '未到' ELSE '已过' END AS 判断1union allselect '7周', 7,CASE WHEN 7>datepart(WK, getdate()) THEN '未到' ELSE '已过' END AS 判断1union allselect '8周', 8,CASE WHEN 8>datepart(WK, getdate()) THEN '未到' ELSE '已过' END AS 判断1union allselect '9周', 9,CASE WHEN 9>datepart(WK, getdate()) THEN '未到' ELSE '已过' END AS 判断1union allselect '10周', 10,CASE WHEN 10>datepart(WK, getdate()) THEN '未到' ELSE '已过' END AS 判断1union allselect '11周', 11,CASE WHEN 11>datepart(WK, getdate()) THEN '未到' ELSE '已过' END AS 判断1union allselect '12周', 12,CASE WHEN 12>datepart(WK, getdate()) THEN '未到' ELSE '已过' END AS 判断1union allselect '13周', 13,CASE WHEN 13>datepart(WK, getdate()) THEN '未到' ELSE '已过' END AS 判断1union allselect '14周', 14,CASE WHEN 14>datepart(WK, getdate()) THEN '未到' ELSE '已过' END AS 判断1union allselect '15周', 15,CASE WHEN 15>datepart(WK, getdate()) THEN '未到' ELSE '已过' END AS 判断1union allselect '16周', 16,CASE WHEN 16>datepart(WK, getdate()) THEN '未到' ELSE '已过' END AS 判断1union allselect '17周', 17,CASE WHEN 17>datepart(WK, getdate()) THEN '未到' ELSE '已过' END AS 判断1union allselect '18周', 18,CASE WHEN 18>datepart(WK, getdate()) THEN '未到' ELSE '已过' END AS 判断1union allselect '19周', 19,CASE WHEN 19>datepart(WK, getdate()) THEN '未到' ELSE '已过' END AS 判断1union allselect '20周', 20,CASE WHEN 20>datepart(WK, getdate()) THEN '未到' ELSE '已过' END AS 判断1union allselect '21周', 21,CASE WHEN 21>datepart(WK, getdate()) THEN '未到' ELSE '已过' END AS 判断1union allselect '22周', 22,CASE WHEN 22>datepart(WK, getdate()) THEN '未到' ELSE '已过' END AS 判断1union allselect '23周', 23,CASE WHEN 23>datepart(WK, getdate()) THEN '未到' ELSE '已过' END AS 判断1union allselect '24周', 24,CASE WHEN 24>datepart(WK, getdate()) THEN '未到' ELSE '已过' END AS 判断1union allselect '25周', 25,CASE WHEN 25>datepart(WK, getdate()) THEN '未到' ELSE '已过' END AS 判断1union allselect '26周', 26,CASE WHEN 26>datepart(WK, getdate()) THEN '未到' ELSE '已过' END AS 判断1union allselect '27周', 27,CASE WHEN 27>datepart(WK, getdate()) THEN '未到' ELSE '已过' END AS 判断1union allselect '28周', 28,CASE WHEN 28>datepart(WK, getdate()) THEN '未到' ELSE '已过' END AS 判断1union allselect '29周', 29,CASE WHEN 29>datepart(WK, getdate()) THEN '未到' ELSE '已过' END AS 判断1union allselect '30周', 30,CASE WHEN 30>datepart(WK, getdate()) THEN '未到' ELSE '已过' END AS 判断1union allselect '31周', 31,CASE WHEN 31>datepart(WK, getdate()) THEN '未到' ELSE '已过' END AS 判断1union allselect '32周', 32,CASE WHEN 32>datepart(WK, getdate()) THEN '未到' ELSE '已过' END AS 判断1union allselect '33周', 33,CASE WHEN 33>datepart(WK, getdate()) THEN '未到' ELSE '已过' END AS 判断1union allselect '34周', 34,CASE WHEN 34>datepart(WK, getdate()) THEN '未到' ELSE '已过' END AS 判断1union allselect '35周', 35,CASE WHEN 35>datepart(WK, getdate()) THEN '未到' ELSE '已过' END AS 判断1union allselect '36周', 36,CASE WHEN 36>datepart(WK, getdate()) THEN '未到' ELSE '已过' END AS 判断1union allselect '37周', 37,CASE WHEN 37>datepart(WK, getdate()) THEN '未到' ELSE '已过' END AS 判断1union allselect '38周', 38,CASE WHEN 38>datepart(WK, getdate()) THEN '未到' ELSE '已过' END AS 判断1union allselect '39周', 39,CASE WHEN 39>datepart(WK, getdate()) THEN '未到' ELSE '已过' END AS 判断1union allselect '40周', 40,CASE WHEN 40>datepart(WK, getdate()) THEN '未到' ELSE '已过' END AS 判断1union allselect '41周', 41,CASE WHEN 41>datepart(WK, getdate()) THEN '未到' ELSE '已过' END AS 判断1union allselect '42周', 42,CASE WHEN 42>datepart(WK, getdate()) THEN '未到' ELSE '已过' END AS 判断1union allselect '43周', 43,CASE WHEN 43>datepart(WK, getdate()) THEN '未到' ELSE '已过' END AS 判断1union allselect '44周', 44,CASE WHEN 44>datepart(WK, getdate()) THEN '未到' ELSE '已过' END AS 判断1union allselect '45周', 45,CASE WHEN 45>datepart(WK, getdate()) THEN '未到' ELSE '已过' END AS 判断1union allselect '46周', 46,CASE WHEN 46>datepart(WK, getdate()) THEN '未到' ELSE '已过' END AS 判断1union allselect '47周', 47,CASE WHEN 47>datepart(WK, getdate()) THEN '未到' ELSE '已过' END AS 判断1union allselect '48周', 48,CASE WHEN 48>datepart(WK, getdate()) THEN '未到' ELSE '已过' END AS 判断1union allselect '49周', 49,CASE WHEN 49>datepart(WK, getdate()) THEN '未到' ELSE '已过' END AS 判断1union allselect '50周', 50,CASE WHEN 50>datepart(WK, getdate()) THEN '未到' ELSE '已过' END AS 判断1union allselect '51周', 51,CASE WHEN 51>datepart(WK, getdate()) THEN '未到' ELSE '已过' END AS 判断1union allselect '52周', 52,CASE WHEN 52>datepart(WK, getdate()) THEN '未到' ELSE '已过' END AS 判断1union allselect '53周', 53,CASE WHEN 53>datepart(WK, getdate()) THEN '未到' ELSE '已过' END AS 判断1)select * from a left join (SELECT COUNT( A.采购数量 ) AS 批次, SUM( A.采购数量 ) AS 总数,大区分,供应商, WEEK1, WEEK2FROM (SELECT 供应商,采购数量, CONVERT ( VARCHAR ( 10 ), datepart(WK, 答复日期) ) + N'周' AS WEEK1, datepart(WK, 答复日期) AS WEEK2,大区分,答复日期 FROM . WHERE CONVERT ( VARCHAR ( 10 ), 答复日期, 120 ) > '2021-12-31' AND CONVERT ( VARCHAR ( 10 ), 答复日期, 120 ) < '2023-01-01' ) A -- WHERE-- 供应商 = '${供应商}'${if(大区分="全部","",if(大区分="其他","and 大区分 NOT IN ('AA' ,'AB', 'AC' ,'AD','AE')","and 大区分='"+大区分+"'")) } GROUP BY 大区分,供应商, WEEK1, WEEK2) T ON A.WEEK_=T.WEEK1 ORDER BY NUM_ ASC 你看这个日这么多365个我一个一个写吗

320个浏览 2个回答 2022年08月11日发布

去除重复值不知道怎么写

COUNT(ds1.SELECT(简称,大区分=C2 && 区分=C3))这个简称我想去除重复值怎么办

259个浏览 2个回答 2022年08月11日发布

求帮改一句sqlserver

我希望展示1-12月的我的数据虽然只有1-8的但是我想要9-12也是空的在后面显示出来SELECT COUNT(A.采购数量) AS 批次,SUM(A.采购数量) AS 总数,大区分,供应商,MONTH1,MONTH2,判断 from (SELECT 供应商,采购数量,convert(varchar(10),Month(答复日期))+N'月' as MONTH1,Month(答复日期) AS MONTH2,CASE WHEN month(答复日期)>month(GETDATE()) THEN '未到' ELSE '已过' END AS 判断,大区分,答复日期 FROM . where convert(varchar(10), 答复日期, 120) > '2021-12-31' AND convert(varchar(10), 答复日期, 120) < '2023-01-01')A WHERE 供应商='${供应商}' ${if(大区分="全部","",if(大区分="其他","and 大区分 NOT IN ('AA' ,'AB', 'AC' ,'AD','AE')","and 大区分='"+大区分+"'")) } GROUP BY 大区分,供应商,MONTH1,MONTH2,判断 ORDER BY MONTH2 ASC 这是我的SQL

363个浏览 3个回答 2022年08月10日发布

求帮写一个SQLSERVER的LEFTJOIN

SELECT 销售组,供应商,大区分,CASE WHEN 大区分='AA' then '加工部品' WHEN 大区分='AB' then '机械部品' WHEN 大区分='AC' then '电子部品' WHEN 大区分='AD' then '电气部品' WHEN 大区分='AE' then '图像部品' END AS 区分 FROM ERP导入 WHERE 大区分='AA' OR 大区分='AB' OR 大区分='AC' OR 大区分='AD' OR 大区分='AE'group BY 销售组,供应商,大区分 ORDER BY 大区分 ASC 这是第一段 left join select 简称 FROM 清单这是第二段ON 第一段的供应商等于第二段的供应商全称

350个浏览 2个回答 2022年08月09日发布

我图表第二个分类怎么设置条件格式没有用

供应商动态产能查看.rar第一个分类就有用我加了一个附件

361个浏览 3个回答 2022年08月09日发布

不同的分类怎么用不同的颜色

我这个未到的分类的柱形图想用不同的颜色

316个浏览 1个回答 2022年08月09日发布

之前用控件的事件触发单元格条件格式现在怎么不能用了

编号申请.rarC10这个格子

240个浏览 1个回答 2022年08月08日发布

提问

调整图片尺寸与位置

callie（uid：755008）