if语句在SQL中计算年龄 [英] if statement in SQL to calculate age
本文介绍了if语句在SQL中计算年龄的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我在Excel中有这个报告。我在AGE列N中计算公式计算AGE。我一直在尝试创建一个SQL查询来得出相同的答案。到目前为止，我已经找出了我需要的列，并将所有数据拉到Match Excel。只有我不知道如何和在哪里输入这个IF语句，所以它会计算我运行查询的年龄。
工作单位置MISC状态实际完成时间家长WO工作类型N报告日期报告时间站点年龄子名称变电站位置
1234567899 4074 COMP 11/5/14 3:08:49 PM CM 10/7 / 14 1:47:42 PM 29.05633102
12348574987 2946 SCHED CM 10/30/14 10:28:03 AM 188.5638542
AGE是一个公式，粘贴在下面：
= IF（（IF（Status& Comp，今天（） - 报告日期，实际完成报告日期））< 0,0，IF（STATUS<"COMP，TODAY（） - 报告日期，实际完成报告日期））
SQL Writen查询
SELECT WO.WONUM，
LOCOFFDESC.DESCRIPTION AS OFFICE，
WO.STATUS，
WO.LOCATION，
CASE
WHEN（（INSTR（LOCOFF.EXT_LOC_HIERARCHY_PATH，'/'，2,3））= 0）
THEN SUBSTR（LOCOFF.EXT_LOC_HIERARCHY_PATH，（（（INSTR LOCOFF.EXT_LOC_HIERARCHY_PATH，'/'，1,2）+ 2）），LENGTH（LOCOFF.EXT_LOC_HIERARCHY_PATH））
WHEN（（INSTR（LOCOFF.EXT_LOC_HIERARCHY_PATH，'/'，2,3））IS NOT NULL）
THEN SUBSTR（LOCOFF.EXT_LOC_HIERARCHY_PATH，（（INSTR（LOCOFF.EXT_LOC_HIERARCHY_PATH，'/'，1 ，（2）+ 2）），（INSTR（LOCOFF.EXT_LOC_HIERARCHY_PATH，'/'，2,3）） - （INSTR（LOCOFF.EXT_LOC_HIERARCHY_PATH，'/'，1,2）+ 2））
END AS SUBSTATION CASE，
WO.PARENT，
WO.WORKTYPE，
WO.REPORTEDBY，
WO.REPORTDATE，
WO.ACTFINISH，
WO.SITEID
从位置LOCOFF
RIGHT JOIN MAXPRD.WORKORDER WO
在WO.LOCATION = LOCOFF.LOCATION
内部加入最大值
ON LOCOFFANC.ANCESTOR = LOCOFFDESC.LOCATION
INNER JOIN MAXPRD.LOCHIERARCHY LOCOFFHIER
ON LOCOFFANC.ANCESTOR = LOCOFFHIER.LOCATION
WHERE LOCOFF.SITEID ='SUB'
AND LOCOFFHIER.PARENT ='2000'
GROUP BY WO.WONUM，
LOCOFFDESC.DESCRIPTION，
WO.STATUS，
WO.LOCATION，
LOCOFF.EXT_LOC_HIERARCHY_PATH，
LOCOFF.SITEID，
LOCOFFHIER.PARENT，
WO.PARENT，
WO.WORKTYPE，
WO.REPORTEDBY，
WO.REPORTDATE，
WO.ACTFINISH，
WO.SITEID
解决方案
在SELECT语句之后，在FROM语句之前，所以你有一个填充了计算值的列，类似于excel。
我们需要知道你正在使用的数据库系统以获得确切的语法。你可以尝试WO.SITEID，
SELECT WO.WONUM，
LOCOFFDESC.DESCRIPTION AS OFFICE，
WO.STATUS，
WO.LOCATION，
CASE
WHEN（（INSTR（LOCOFF.EXT_LOC_HIERARCHY_PATH，'/'，2,3））= 0）
THEN SUBSTR LOCOFF.EXT_LOC_HIERARCHY_PATH，（（INSTR（LOCOFF.EXT_LOC_HIERARCHY_PATH，'/'，1,2）+ 2）），LENGTH（LOCOFF.EXT_LOC_HIERARCHY_PATH））
WHEN（（INSTR（LOCOFF.EXT_LOC_HIERARCHY_PATH，'/ ，3））IS NOT NULL）
THEN SUBSTR（LOCOFF.EXT_LOC_HIERARCHY_PATH，（（INSTR（LOCOFF.EXT_LOC_HIERARCHY_PATH，'/'，1,2）+ 2）），（INSTR（LOCOFF.EXT_LOC_HIERARCHY_PATH，'/' ，2，3）） - （INSTR（LOCOFF.EXT_LOC_HIERARCHY_PATH，'/'，1,2）+ 2））
END作为SUBSTATION CASE，
WO.PARENT，
WO .WORKTYPE，
WO.REPORTEDBY，
WO.REPORTDATE，
WO.ACTFINISH，
WO.SITEID，
（
CASE
WHEN （
CASE WHEN WO.STATUS！='Comp'
THEN SYSDATE-WO.REPORTDATE
ELS E WO.ACTFINISH-WO.REPORTDATE
END）< 0
THEN 0
ELSE
CASE WHEN STATUS！='COMP'
THEN SYSDATE-WO.REPORTDATE
ELSE WO.ACTFINISH-WO.REPORTDATE
END
END）as age
FROM LOCATIONS LOCOFF
RIGHT JOIN MAXPRD.WORKORDER WO
ON WO.LOCATION = LOCOFF.LOCATION
INNER JOIN MAXPRD.LOCANCESTOR LOCOFFANC
ON LOCOFFANC.LOCATION = LOCOFF.LOCATION
INNER JOIN MAXPRD.LOCATIONS LOCOFFDESC
ON LOCOFFANC.ANCESTOR = LOCOFFDESC.LOCATION
INNER JOIN MAXPRD.LOCHIERARCHY LOCOFFHIER
ON LOCOFFANC.ANCESTOR = LOCOFFHIER.LOCATION
WHERE LOCOFF.SITEID ='SUB'
AND LOCOFFHIER.PARENT ='2000'
GROUP BY WO.WONUM，
LOCOFFDESC.DESCRIPTION，
WO.STATUS，
WO.LOCATION，
LOCOFF.EXT_LOC_HIERARCHY_PATH，
LOCOFF.SITEID，
LOCOFFHIER.PARENT，
WO.PARENT，
WO。 WORKTYPE，
WO.REPORTEDBY，
WO.REPORTDATE，
WO.ACTFINISH，
WO.SITEID
为MS SQL服务器编辑。编辑AGAIN for Oracle：）
I am very new at SQL and I have been working on my First Query. Now I need your help. I have this report in Excel. I have formula in the AGE column N calculating the AGE. I have been trying to create a SQL query to come up with the same answer. So far I have figured out the columns that I need and pulled all the data to Match Excel. Only thing is I don't know how and where to enter this IF statement so it will calculate the age when i run the query.
Work Order Location MISC Status Actual Finish Finish Time Parent WO Work Type N Reported Date Reported Time Site Gen Age Sub Name Substation Location
1234567899 4074 COMP 11/5/14 3:08:49 PM CM 10/7/14 1:47:42 PM 29.05633102
12348574987 2946 SCHED CM 10/30/14 10:28:03 AM 188.5638542
AGE is a formula, pasted below:
=IF ((IF(Status<>"Comp",Today()-Reported date, actual finish-reported date))<0,0, IF(STATUS<>"COMP", TODAY()-REPORTED DATE, ACTUAL FINISH-REPORTED DATE))
SQL Writen Query
SELECT WO.WONUM,
LOCOFFDESC.DESCRIPTION AS OFFICE,
WO.STATUS,
WO.LOCATION,
CASE
WHEN ((INSTR(LOCOFF.EXT_LOC_HIERARCHY_PATH, '/', 2, 3)) = 0)
THEN SUBSTR(LOCOFF.EXT_LOC_HIERARCHY_PATH, ((INSTR(LOCOFF.EXT_LOC_HIERARCHY_PATH, '/', 1, 2) + 2)), LENGTH(LOCOFF.EXT_LOC_HIERARCHY_PATH))
WHEN ((INSTR(LOCOFF.EXT_LOC_HIERARCHY_PATH, '/', 2, 3)) IS NOT NULL)
THEN SUBSTR(LOCOFF.EXT_LOC_HIERARCHY_PATH, ((INSTR(LOCOFF.EXT_LOC_HIERARCHY_PATH, '/', 1, 2) + 2)), (INSTR(LOCOFF.EXT_LOC_HIERARCHY_PATH, '/', 2, 3)) - (INSTR(LOCOFF.EXT_LOC_HIERARCHY_PATH, '/', 1, 2) + 2))
END AS "SUBSTATION CASE",
WO.PARENT,
WO.WORKTYPE,
WO.REPORTEDBY,
WO.REPORTDATE,
WO.ACTFINISH,
WO.SITEID
FROM LOCATIONS LOCOFF
RIGHT JOIN MAXPRD.WORKORDER WO
ON WO.LOCATION = LOCOFF.LOCATION
INNER JOIN MAXPRD.LOCANCESTOR LOCOFFANC
ON LOCOFFANC.LOCATION = LOCOFF.LOCATION
INNER JOIN MAXPRD.LOCATIONS LOCOFFDESC
ON LOCOFFANC.ANCESTOR = LOCOFFDESC.LOCATION
INNER JOIN MAXPRD.LOCHIERARCHY LOCOFFHIER
ON LOCOFFANC.ANCESTOR = LOCOFFHIER.LOCATION
WHERE LOCOFF.SITEID = 'SUB'
AND LOCOFFHIER.PARENT = '2000'
GROUP BY WO.WONUM,
LOCOFFDESC.DESCRIPTION,
WO.STATUS,
WO.LOCATION,
LOCOFF.EXT_LOC_HIERARCHY_PATH,
LOCOFF.SITEID,
LOCOFFHIER.PARENT,
WO.PARENT,
WO.WORKTYPE,
WO.REPORTEDBY,
WO.REPORTDATE,
WO.ACTFINISH,
WO.SITEID
解决方案
You place the calculation after the SELECT statement, before the FROM statement, so you have a column filled with calculated values, similar to excel.
We need to know what database system you are working with to get the exact syntax. Can you try after WO.SITEID,
SELECT WO.WONUM,
LOCOFFDESC.DESCRIPTION AS OFFICE,
WO.STATUS,
WO.LOCATION,
CASE
WHEN ((INSTR(LOCOFF.EXT_LOC_HIERARCHY_PATH, '/', 2, 3)) = 0)
THEN SUBSTR(LOCOFF.EXT_LOC_HIERARCHY_PATH, ((INSTR(LOCOFF.EXT_LOC_HIERARCHY_PATH, '/', 1, 2) + 2)), LENGTH(LOCOFF.EXT_LOC_HIERARCHY_PATH))
WHEN ((INSTR(LOCOFF.EXT_LOC_HIERARCHY_PATH, '/', 2, 3)) IS NOT NULL)
THEN SUBSTR(LOCOFF.EXT_LOC_HIERARCHY_PATH, ((INSTR(LOCOFF.EXT_LOC_HIERARCHY_PATH, '/', 1, 2) + 2)), (INSTR(LOCOFF.EXT_LOC_HIERARCHY_PATH, '/', 2, 3)) - (INSTR(LOCOFF.EXT_LOC_HIERARCHY_PATH, '/', 1, 2) + 2))
END AS "SUBSTATION CASE",
WO.PARENT,
WO.WORKTYPE,
WO.REPORTEDBY,
WO.REPORTDATE,
WO.ACTFINISH,
WO.SITEID,
(
CASE
WHEN (
CASE WHEN WO.STATUS != 'Comp'
THEN SYSDATE-WO.REPORTDATE
ELSE WO.ACTFINISH-WO.REPORTDATE
END) < 0
THEN 0
ELSE
CASE WHEN STATUS != 'COMP'
THEN SYSDATE-WO.REPORTDATE
ELSE WO.ACTFINISH-WO.REPORTDATE
END
END) as age
FROM LOCATIONS LOCOFF
RIGHT JOIN MAXPRD.WORKORDER WO
ON WO.LOCATION = LOCOFF.LOCATION
INNER JOIN MAXPRD.LOCANCESTOR LOCOFFANC
ON LOCOFFANC.LOCATION = LOCOFF.LOCATION
INNER JOIN MAXPRD.LOCATIONS LOCOFFDESC
ON LOCOFFANC.ANCESTOR = LOCOFFDESC.LOCATION
INNER JOIN MAXPRD.LOCHIERARCHY LOCOFFHIER
ON LOCOFFANC.ANCESTOR = LOCOFFHIER.LOCATION
WHERE LOCOFF.SITEID = 'SUB'
AND LOCOFFHIER.PARENT = '2000'
GROUP BY WO.WONUM,
LOCOFFDESC.DESCRIPTION,
WO.STATUS,
WO.LOCATION,
LOCOFF.EXT_LOC_HIERARCHY_PATH,
LOCOFF.SITEID,
LOCOFFHIER.PARENT,
WO.PARENT,
WO.WORKTYPE,
WO.REPORTEDBY,
WO.REPORTDATE,
WO.ACTFINISH,
WO.SITEID
Edited for MS SQL server. Edited AGAIN for Oracle :)
这篇关于if语句在SQL中计算年龄的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
