请问高手 SQL语句该怎么写。

fhsoft8508539 2010-04-12 11:55:17
有这么一张表：

SORDER KOTECD JSTSTM JSTETM WAREHUSFG(入库状态)
04113510 2025 2010-04-03 11:12:00 2010-04-03 11:12:00 1
04113510 2026 2010-04-03 11:36:00 2010-04-03 11:36:00 1
04113511 2015 2010-04-03 02:10:00 2010-04-03 02:10:00 1
04113511 2025 2010-04-03 08:38:00 2010-04-03 08:38:00 1
04113511 2026 2010-04-03 08:45:00 2010-04-03 08:45:00 1
04113540 2015 2010-04-02 22:50:00 2010-04-02 22:51:00 1
04113540 2025 2010-04-03 20:06:00 2010-04-03 20:06:00 1
04113540 2026 2010-04-03 20:33:00 2010-04-03 20:33:00 1
04113550 2015 2010-04-02 22:49:00 2010-04-03 01:59:00 1
04113550 2025 2010-04-03 22:11:00 2010-04-03 22:11:00 1
04113550 2026 2010-04-03 22:21:00 2010-04-03 22:21:00 1
04113560 2015 2010-04-03 01:59:00 2010-04-03 03:01:00 1
04113560 2025 2010-04-04 01:02:00 2010-04-04 01:03:00 1
04113560 2026 2010-04-04 01:08:00 2010-04-04 01:09:00 1
04113630 2015 2010-04-03 09:43:00 2010-04-03 09:44:00 1
04113630 2025 2010-04-03 15:13:00 2010-04-03 15:13:00 1
04113630 2026 2010-04-03 15:33:00 2010-04-03 15:33:00 1
04113640 2015 2010-04-03 10:48:00 2010-04-03 10:48:00 1
04113640 2025 2010-04-03 17:35:00 2010-04-03 17:35:00 1
04113640 2026 2010-04-03 17:58:00 2010-04-03 17:58:00 1
04113650 2015 2010-04-03 10:48:00 2010-04-03 11:52:00 1
04113650 2025 2010-04-03 21:38:00 2010-04-03 21:38:00 1
04113650 2026 2010-04-03 21:44:00 2010-04-03 21:44:00 1
04113660 2015 2010-04-03 11:52:00 2010-04-03 12:47:00 1
04113660 2025 2010-04-03 18:16:00 2010-04-03 18:16:00 1
04113660 2026 2010-04-03 18:25:00 2010-04-03 18:26:00 1
04113670 2015 2010-04-03 12:48:00 2010-04-03 13:55:00 1
04113670 2025 2010-04-03 21:48:00 2010-04-03 21:48:00 1
04113670 2026 2010-04-03 21:49:00 2010-04-03 21:49:00 1
04113680 2015 2010-04-03 14:23:00 2010-04-03 14:23:00 1
04113680 2025 2010-04-04 00:44:00 2010-04-04 00:46:00 1
......


表中SORDER为表单号 KOTECD为工序号 JSTSTM为工序开始时间，JSTETM为工序结束时间。

现在要统计每个表单从2015工序开始到2026工序结束，需要多少时间，以小时显示。

请问高手 SQL语句该怎么写。
...全文
151 17 打赏 收藏 转发到动态 举报
写回复
用AI写文章
17 条回复
切换为时间正序
请发表友善的回复…
发表回复
ws_hgo 2010-04-12
  • 打赏
  • 举报
 回复
[Quote=引用 11 楼 fhsoft8508539 的回复:]
高手HTL258：
刚才忘记了，后台数据库为oracle！！！

你的SQL语句无法执行，提示DATEDIFF为无效字符。

请问在oracle数据库中该怎么写？

本人比较菜。
[/Quote]
那就去oracle问问
htl258_Tony 2010-04-12
  • 打赏
  • 举报
 回复
[Quote=引用 10 楼 fhsoft8508539 的回复:]
高手HTL258：
你的SQL语句在oracle中无法执行。提示DATEDIFF为无效字符。oracle数据库中该怎么写？

本人比较菜。
[/Quote]

SELECT SORDER,TO_NUMBER(SUM(DATEDIFF(MINUTE,JSTSTM,JSTETM)*1.0/60),2) SUMTM

FROM TB 

WHERE WAREHUSFG=1 AND KOTECD BETWEEN 2015 AND 2026

GROUP BY SORDER
TRY
fhsoft8508539 2010-04-12
  • 打赏
  • 举报
 回复
高手HTL258：
刚才忘记了，后台数据库为oracle！！！

你的SQL语句无法执行，提示DATEDIFF为无效字符。

请问在oracle数据库中该怎么写？

本人比较菜。
fhsoft8508539 2010-04-12
  • 打赏
  • 举报
 回复
高手HTL258：
你的SQL语句在oracle中无法执行。提示DATEDIFF为无效字符。oracle数据库中该怎么写？

本人比较菜。
htl258_Tony 2010-04-12
  • 打赏
  • 举报
 回复
[Quote=引用 5 楼 fcpslove 的回复:]
楼上的貌似对的。。
楼上的帮我去搞搞我的那个问题吧。。。。
http://topic.csdn.net/u/20100412/11/a4ea520e-7dd0-44d2-98bb-9f62f0ed6160.html?seed=1197185924&r=64591966#r_64591966
[/Quote]这题比较麻烦，到晚上没解决再看。
fhsoft8508539 2010-04-12
  • 打赏
  • 举报
 回复
htl258果然是SQL高手。
fcpslove 2010-04-12
  • 打赏
  • 举报
 回复
[Quote=引用 9 楼 fcpslove 的回复:]
沟胸兄，怎么写的，请给我个例子吧，小弟这里感谢先
还是其他高手有相关的代码吗，像Tony==高手，也烦请你们给点意见 -_-!
[/Quote]

期待中...
htl258_Tony 2010-04-12
  • 打赏
  • 举报
 回复 


/*------------------------------------------------------------------

--  Author : htl258(Tony)

--  Date   : 2010-04-12 12:08:01

--  Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 

	Jul  9 2008 14:43:34 

	Copyright (c) 1988-2008 Microsoft Corporation

	Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)



------------------------------------------------------------------*/

--> 生成测试数据表:tb



IF OBJECT_ID('[tb]') IS NOT NULL

	DROP TABLE [tb]

GO

CREATE TABLE [tb]([SORDER] NVARCHAR(10),[KOTECD] INT,[JSTSTM] DATETIME,[JSTETM] DATETIME,[WAREHUSFG] INT)

INSERT [tb]

SELECT '04113510',2025,N'2010-04-03 11:12:00',N'2010-04-03 11:12:00',1 UNION ALL

SELECT '04113510',2026,N'2010-04-03 11:36:00',N'2010-04-03 11:36:00',1 UNION ALL

SELECT '04113511',2015,N'2010-04-03 02:10:00',N'2010-04-03 02:10:00',1 UNION ALL

SELECT '04113511',2025,N'2010-04-03 08:38:00',N'2010-04-03 08:38:00',1 UNION ALL

SELECT '04113511',2026,N'2010-04-03 08:45:00',N'2010-04-03 08:45:00',1 UNION ALL

SELECT '04113540',2015,N'2010-04-02 22:50:00',N'2010-04-02 22:51:00',1 UNION ALL

SELECT '04113540',2025,N'2010-04-03 20:06:00',N'2010-04-03 20:06:00',1 UNION ALL

SELECT '04113540',2026,N'2010-04-03 20:33:00',N'2010-04-03 20:33:00',1 UNION ALL

SELECT '04113550',2015,N'2010-04-02 22:49:00',N'2010-04-03 01:59:00',1 UNION ALL

SELECT '04113550',2025,N'2010-04-03 22:11:00',N'2010-04-03 22:11:00',1 UNION ALL

SELECT '04113550',2026,N'2010-04-03 22:21:00',N'2010-04-03 22:21:00',1 UNION ALL

SELECT '04113560',2015,N'2010-04-03 01:59:00',N'2010-04-03 03:01:00',1 UNION ALL

SELECT '04113560',2025,N'2010-04-04 01:02:00',N'2010-04-04 01:03:00',1 UNION ALL

SELECT '04113560',2026,N'2010-04-04 01:08:00',N'2010-04-04 01:09:00',1 UNION ALL

SELECT '04113630',2015,N'2010-04-03 09:43:00',N'2010-04-03 09:44:00',1 UNION ALL

SELECT '04113630',2025,N'2010-04-03 15:13:00',N'2010-04-03 15:13:00',1 UNION ALL

SELECT '04113630',2026,N'2010-04-03 15:33:00',N'2010-04-03 15:33:00',1 UNION ALL

SELECT '04113640',2015,N'2010-04-03 10:48:00',N'2010-04-03 10:48:00',1 UNION ALL

SELECT '04113640',2025,N'2010-04-03 17:35:00',N'2010-04-03 17:35:00',1 UNION ALL

SELECT '04113640',2026,N'2010-04-03 17:58:00',N'2010-04-03 17:58:00',1 UNION ALL

SELECT '04113650',2015,N'2010-04-03 10:48:00',N'2010-04-03 11:52:00',1 UNION ALL

SELECT '04113650',2025,N'2010-04-03 21:38:00',N'2010-04-03 21:38:00',1 UNION ALL

SELECT '04113650',2026,N'2010-04-03 21:44:00',N'2010-04-03 21:44:00',1 UNION ALL

SELECT '04113660',2015,N'2010-04-03 11:52:00',N'2010-04-03 12:47:00',1 UNION ALL

SELECT '04113660',2025,N'2010-04-03 18:16:00',N'2010-04-03 18:16:00',1 UNION ALL

SELECT '04113660',2026,N'2010-04-03 18:25:00',N'2010-04-03 18:26:00',1 UNION ALL

SELECT '04113670',2015,N'2010-04-03 12:48:00',N'2010-04-03 13:55:00',1 UNION ALL

SELECT '04113670',2025,N'2010-04-03 21:48:00',N'2010-04-03 21:48:00',1 UNION ALL

SELECT '04113670',2026,N'2010-04-03 21:49:00',N'2010-04-03 21:49:00',1 UNION ALL

SELECT '04113680',2015,N'2010-04-03 14:23:00',N'2010-04-03 14:23:00',1 UNION ALL

SELECT '04113680',2025,N'2010-04-04 00:44:00',N'2010-04-04 00:46:00',1

GO

--SELECT * FROM [tb]



-->SQL查询如下:

SELECT SORDER,SUM(DATEDIFF(HH,JSTSTM,JSTETM)) SUMTM

FROM TB 

WHERE WAREHUSFG=1 AND KOTECD BETWEEN 2015 AND 2026

GROUP BY SORDER

/*

SORDER     SUMTM

---------- -----------

04113510   0

04113511   0

04113540   0

04113550   3

04113560   2

04113630   0

04113640   0

04113650   1

04113660   1

04113670   1

04113680   0



(11 行受影响)

*/

--还是要这个结果

SELECT SORDER,CAST(SUM(DATEDIFF(MI,JSTSTM,JSTETM)*1./60) AS DEC(18,2)) SUMTM

FROM TB 

WHERE WAREHUSFG=1 AND KOTECD BETWEEN 2015 AND 2026

GROUP BY SORDER

/*

SORDER     SUMTM

---------- ---------------------------------------

04113510   0.00

04113511   0.00

04113540   0.02

04113550   3.17

04113560   1.07

04113630   0.02

04113640   0.00

04113650   1.07

04113660   0.93

04113670   1.12

04113680   0.03



(11 行受影响)

*/
fcpslove 2010-04-12
  • 打赏
  • 举报
 回复
楼上的貌似对的。。
楼上的帮我去搞搞我的那个问题吧。。。。
http://topic.csdn.net/u/20100412/11/a4ea520e-7dd0-44d2-98bb-9f62f0ed6160.html?seed=1197185924&r=64591966#r_64591966
htl258_Tony 2010-04-12
  • 打赏
  • 举报
 回复 
SELECT SORDER,SUM(DATEDIFF(HH,JSTSTM,JSTETM)) SUMTM

FROM TB 

WHERE WAREHUSFG=1 AND KOTECD BETWEEN 2015 AND 2026

GROUP BY SORDER
fcpslove 2010-04-12
  • 打赏
  • 举报
 回复
1楼的显然不是楼主想要的，你这样算出来的是从开始，到结束经历了多少小时。
中间是有间隔的，也就是工序没跑的时候。
结果应该是：
（2015开始-结束）+（2025开始-结束）+（2026开始-结束）
yujunlin32167 2010-04-12
  • 打赏
  • 举报
 回复
学习一下....
ws_hgo 2010-04-12
  • 打赏
  • 举报
 回复 
select datediff(hour,(select JSTSTM from t where KOTECD=2015),(select JSTETMfrom t where KOTECD=2026))
永生天地 2010-04-12
  • 打赏
  • 举报
 回复
oracle那里写了
永生天地 2010-04-12
  • 打赏
  • 举报
 回复
oracle 两个时间相减2008-05-06 16:30
两个时间相减默认的是天数
oracle 两个时间相减默认的是天数*24 为相差的小时数

oracle 两个时间相减默认的是天数*24*60 为相差的分钟数

oracle 两个时间相减默认的是天数*24*60*60 为相差的秒数

测试如下:

SQL>select sysdate from dual;

2008-2-20 14:32:35

SQL> select (sysdate-to_date('2008-02-17 23:00:00','yyyy-mm-dd hh24:mi:ss')) from dual;


SQL> select (sysdate-to_date('2008-02-17 23:00:00','yyyy-mm-dd hh24:mi:ss'))*24 from dual;

SQL> select (sysdate-to_date('2008-02-17 23:00:00','yyyy-mm-dd hh24:mi:ss'))*24*60*60 from dual;



1、对于2个DATE类型直接相减，就可以得到天数的值，如：

select to_date('2006-05-11 12:30:01','YYYY-MM-DD HH24:MI:SS') -to_date('2006-05-11 12:30:00','YYYY-MM-DD HH24:MI:SS') T from dual;

结果：
T
-------------------
.000011574

这个得到的天数很容易换算成秒，如 0.000011574*24*60*60=1s。

2、在ORACLE里日期类型是可以直接进行比较的。举个例子

SQL> SELECT to_date('2006-12-05', 'yyyy-mm-dd') - to_date('2006-12-07', 'yyyy-mm-dd') FROM dual ;

TO_DATE('2006-12-05','YYYY-MM-DD')-TO_DATE('2006-12-07','YYYY-MM-DD')
---------------------------------------------------------------------
-2
这说明2006-12-05要比7号早2天。

SQL> SELECT to_date('2006-12-07 14:23:24' , 'yyyy-mm-dd hh24:mi:ss') + 1/24 FROM dual ;

TO_DATE('2006-12-07
-------------------
2006-12-07 15:23:24

SQL>
这是说明在某一时间上加一小时，1是代表一天，1/24就是一小时，同理1/24/60就是一分钟

select trunc(sysdate) - trunc(to_date('2006-05-28 10:20','YYYY-MM-DD HH24:MI')) from dual

3、datediff在Oralce中的实现：

create or replace function
-- -- works in roughly the same way as sybase datsdiff
-- call would be eg. datediff('month',date1,date2)
-- p_what would be 'HOUR', 'DAY','MONTH' OR 'QUARTER'
datediff( p_what in varchar2,
p_d1 in date,
p_d2 in date ) return number
as
l_result number;
begin
l_result:=;
if (upper(p_what) = 'HOUR') then
l_result:=((p_d2-p_d1)*24);
end if;
if (upper(p_what) = 'DAY') then
l_result:=(p_d2-p_d1);
end if;
if (upper(p_what) = 'MONTH') then
l_result:=round(MONTHS_BETWEEN(p_d2,p_d1),0);
end if;
if (upper(p_what) = 'QUARTER') then
l_result:=((floor(MONTHS_BETWEEN(p_d2,TRUNC(p_d2,'YEAR'))/3)+1) - (floor(MONTHS_BETWEEN(p_d1,TRUNC(p_d1,'YEAR'))/3)+1) + (((to_char(p_d2, 'yyyy')) - (to_char(p_d1, 'yyyy')))*4));
end if;
l_result:=floor(l_result);
return l_result;
end;
/


SQL> SELECT DATEDIFF('MONTH','11-JUL-1975','07-JUN-2004') FROM DUAL;

DATEDIFF('MONTH','11-JUL-1975','07-JUN-2004')
---------------------------------------------
347


xman_78tom 2010-04-12
  • 打赏
  • 举报
 回复 


-- oracle

create table tb (SORDER CHAR(8),KOTECD INT,JSTSTM DATE,JSTETM DATE,WAREHUSFG INT);

insert into tb values('04113510',2025,timestamp '2010-04-03 11:12:00',timestamp '2010-04-03 11:12:00',1);

insert into tb values('04113510',2026,timestamp '2010-04-03 11:36:00',timestamp '2010-04-03 11:36:00',1);

insert into tb values('04113511',2015,timestamp '2010-04-03 02:10:00',timestamp '2010-04-03 02:10:00',1);

insert into tb values('04113511',2025,timestamp '2010-04-03 08:38:00',timestamp '2010-04-03 08:38:00',1);

insert into tb values('04113511',2026,timestamp '2010-04-03 08:45:00',timestamp '2010-04-03 08:45:00',1);

insert into tb values('04113540',2015,timestamp '2010-04-02 22:50:00',timestamp '2010-04-02 22:51:00',1);

insert into tb values('04113540',2025,timestamp '2010-04-03 20:06:00',timestamp '2010-04-03 20:06:00',1);

insert into tb values('04113540',2026,timestamp '2010-04-03 20:33:00',timestamp '2010-04-03 20:33:00',1);

insert into tb values('04113550',2015,timestamp '2010-04-02 22:49:00',timestamp '2010-04-03 01:59:00',1);

insert into tb values('04113550',2025,timestamp '2010-04-03 22:11:00',timestamp '2010-04-03 22:11:00',1);

insert into tb values('04113550',2026,timestamp '2010-04-03 22:21:00',timestamp '2010-04-03 22:21:00',1);

insert into tb values('04113560',2015,timestamp '2010-04-03 01:59:00',timestamp '2010-04-03 03:01:00',1);

insert into tb values('04113560',2025,timestamp '2010-04-04 01:02:00',timestamp '2010-04-04 01:03:00',1);

insert into tb values('04113560',2026,timestamp '2010-04-04 01:08:00',timestamp '2010-04-04 01:09:00',1);

insert into tb values('04113630',2015,timestamp '2010-04-03 09:43:00',timestamp '2010-04-03 09:44:00',1);

insert into tb values('04113630',2025,timestamp '2010-04-03 15:13:00',timestamp '2010-04-03 15:13:00',1);

insert into tb values('04113630',2026,timestamp '2010-04-03 15:33:00',timestamp '2010-04-03 15:33:00',1);

insert into tb values('04113640',2015,timestamp '2010-04-03 10:48:00',timestamp '2010-04-03 10:48:00',1);

insert into tb values('04113640',2025,timestamp '2010-04-03 17:35:00',timestamp '2010-04-03 17:35:00',1);

insert into tb values('04113640',2026,timestamp '2010-04-03 17:58:00',timestamp '2010-04-03 17:58:00',1);

insert into tb values('04113650',2015,timestamp '2010-04-03 10:48:00',timestamp '2010-04-03 11:52:00',1);

insert into tb values('04113650',2025,timestamp '2010-04-03 21:38:00',timestamp '2010-04-03 21:38:00',1);

insert into tb values('04113650',2026,timestamp '2010-04-03 21:44:00',timestamp '2010-04-03 21:44:00',1);

insert into tb values('04113660',2015,timestamp '2010-04-03 11:52:00',timestamp '2010-04-03 12:47:00',1);

insert into tb values('04113660',2025,timestamp '2010-04-03 18:16:00',timestamp '2010-04-03 18:16:00',1);

insert into tb values('04113660',2026,timestamp '2010-04-03 18:25:00',timestamp '2010-04-03 18:26:00',1);

insert into tb values('04113670',2015,timestamp '2010-04-03 12:48:00',timestamp '2010-04-03 13:55:00',1);

insert into tb values('04113670',2025,timestamp '2010-04-03 21:48:00',timestamp '2010-04-03 21:48:00',1);

insert into tb values('04113670',2026,timestamp '2010-04-03 21:49:00',timestamp '2010-04-03 21:49:00',1);

insert into tb values('04113680',2015,timestamp '2010-04-03 14:23:00',timestamp '2010-04-03 14:23:00',1);

insert into tb values('04113680',2025,timestamp '2010-04-04 00:44:00',timestamp '2010-04-04 00:46:00',1);



select * from tb;

-- 不计算工序间的间隔时间

select sorder, to_char(sum(jstetm-jststm)*24,'990.99') sumtm

from tb

where warehusfg=1 and kotecd between 2015 and 2026

group by sorder;

--计算工序间的间隔时间

select sorder, to_char((max(jstetm)-min(jststm))*24,'990.99') sumtm

from tb

where warehusfg=1 and kotecd between 2015 and 2026

group by sorder;



drop table tb;
ws_hgo 2010-04-12
  • 打赏
  • 举报
 回复 
IF OBJECT_ID('[tb]') IS NOT NULL

    DROP TABLE [tb]

GO

CREATE TABLE [tb]([SORDER] NVARCHAR(10),[KOTECD] INT,[JSTSTM] DATETIME,[JSTETM] DATETIME,[WAREHUSFG] INT)

INSERT [tb]

SELECT '04113510',2025,N'2010-04-03 11:12:00',N'2010-04-03 11:12:00',1 UNION ALL

SELECT '04113510',2026,N'2010-04-03 11:36:00',N'2010-04-03 11:36:00',1 UNION ALL

SELECT '04113511',2015,N'2010-04-03 02:10:00',N'2010-04-03 02:10:00',1 UNION ALL

SELECT '04113511',2025,N'2010-04-03 08:38:00',N'2010-04-03 08:38:00',1 UNION ALL

SELECT '04113511',2026,N'2010-04-03 08:45:00',N'2010-04-03 08:45:00',1 UNION ALL

SELECT '04113540',2015,N'2010-04-02 22:50:00',N'2010-04-02 22:51:00',1 UNION ALL

SELECT '04113540',2025,N'2010-04-03 20:06:00',N'2010-04-03 20:06:00',1 UNION ALL

SELECT '04113540',2026,N'2010-04-03 20:33:00',N'2010-04-03 20:33:00',1 UNION ALL

SELECT '04113550',2015,N'2010-04-02 22:49:00',N'2010-04-03 01:59:00',1 UNION ALL

SELECT '04113550',2025,N'2010-04-03 22:11:00',N'2010-04-03 22:11:00',1 UNION ALL

SELECT '04113550',2026,N'2010-04-03 22:21:00',N'2010-04-03 22:21:00',1 UNION ALL

SELECT '04113560',2015,N'2010-04-03 01:59:00',N'2010-04-03 03:01:00',1 UNION ALL

SELECT '04113560',2025,N'2010-04-04 01:02:00',N'2010-04-04 01:03:00',1 UNION ALL

SELECT '04113560',2026,N'2010-04-04 01:08:00',N'2010-04-04 01:09:00',1 UNION ALL

SELECT '04113630',2015,N'2010-04-03 09:43:00',N'2010-04-03 09:44:00',1 UNION ALL

SELECT '04113630',2025,N'2010-04-03 15:13:00',N'2010-04-03 15:13:00',1 UNION ALL

SELECT '04113630',2026,N'2010-04-03 15:33:00',N'2010-04-03 15:33:00',1 UNION ALL

SELECT '04113640',2015,N'2010-04-03 10:48:00',N'2010-04-03 10:48:00',1 UNION ALL

SELECT '04113640',2025,N'2010-04-03 17:35:00',N'2010-04-03 17:35:00',1 UNION ALL

SELECT '04113640',2026,N'2010-04-03 17:58:00',N'2010-04-03 17:58:00',1 UNION ALL

SELECT '04113650',2015,N'2010-04-03 10:48:00',N'2010-04-03 11:52:00',1 UNION ALL

SELECT '04113650',2025,N'2010-04-03 21:38:00',N'2010-04-03 21:38:00',1 UNION ALL

SELECT '04113650',2026,N'2010-04-03 21:44:00',N'2010-04-03 21:44:00',1 UNION ALL

SELECT '04113660',2015,N'2010-04-03 11:52:00',N'2010-04-03 12:47:00',1 UNION ALL

SELECT '04113660',2025,N'2010-04-03 18:16:00',N'2010-04-03 18:16:00',1 UNION ALL

SELECT '04113660',2026,N'2010-04-03 18:25:00',N'2010-04-03 18:26:00',1 UNION ALL

SELECT '04113670',2015,N'2010-04-03 12:48:00',N'2010-04-03 13:55:00',1 UNION ALL

SELECT '04113670',2025,N'2010-04-03 21:48:00',N'2010-04-03 21:48:00',1 UNION ALL

SELECT '04113670',2026,N'2010-04-03 21:49:00',N'2010-04-03 21:49:00',1 UNION ALL

SELECT '04113680',2015,N'2010-04-03 14:23:00',N'2010-04-03 14:23:00',1 UNION ALL

SELECT '04113680',2025,N'2010-04-04 00:44:00',N'2010-04-04 00:46:00',1

GO





select T1.SORDER,datediff(hour,T1.[JSTSTM],T2.JSTETM) 'Time' from 

(

select SORDER,[JSTSTM] from [tb] where KOTECD=2015

) T1

join

(

 select SORDER,JSTETM from [tb] where [KOTECD]=2026

)

T2

on T1.SORDER=T2.SORDER 



SORDER     Time

---------- -----------

04113511   6

04113540   22

04113550   24

04113560   24

04113630   6

04113640   7

04113650   11

04113660   7

04113670   9



(9 行受影响)
高手整理的经典sql语句
高手整理的经典sql语句，sql，sqlserver，sql经典，sql语句
SQL高手篇:精妙SQL语句介绍
SQL高手篇:精妙SQL语句介绍,希望与大家共同分亨。
SQL高手篇精妙SQL语句介绍
SQL高手篇精妙SQL语句介绍SQL高手篇精妙SQL语句介绍
oracle sql语句学习
很全的oracle sql语句学习资料，从基础开始，逐层深入，让你成为sql高手
sql语句大全
sql语句整理，对初学者很有帮助，高手打个酱油就好了！
疑难问题

22,207

社区成员

121,730

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章