回复:通过减去 SQL Server 中的其他两列来计算列 [英] Re: Computed column by subtracting two other columns in SQL Server

问题描述

我的表名 Trip 中有以下列；

I have the following columns in my table name Trip;

trip_no (PK, int, not null),
ID_company (FK, int not null),
plane (char(25), not null),
town_from (char(25) not null),
town_to (char(25) not null),
time_out (datetime, not null),
time_in (datetime, not null),
time_out_only (time(0), null),
time_in_only (time(0) null),
time_in_flight (time(0) null);

对于最后三列，我的第一行看起来像这样；

For the last three columns My first row would look like this;

time_out_only   time_in_only   time_in_flight
 14:30:00        17:50:00        null

我希望飞行时间计算到 02:20:00.于是我试了下time_in_flight等于time_in_only减time_out_only的代码，如下，

I want the time in flight to compute to 02:20:00. So I tried the the code to time_in_flight equal to time_in_only less time_out_only, as follows,

ALTER TABLE Trip
SET time_in_flight = time_in_only - time_out_only;

我收到的错误信息是

"消息 102，级别 15，状态 1，第 2 行.附近的语法不正确'Time_in_Flight'.

"Msg 102, Level 15, State 1, Line 2. Incorrect syntax near 'Time_in_Flight".

除非我在使用 SET 时不正确并且我应该使用不同的函数，否则我对我出错的地方感到困惑.但是我认为这种语法以前对我有用.

Unless I am incorrect in is using SET and I should be using a different function, I am confused as to where I am going wrong. However I thought this syntax worked for me before.

谁能指导我哪里出错了，并帮助我弄清楚如何使用一些函数和公式减去两列.

Can anyone guide me on where I am going wrong here, and help me figure out how to subtract the two columns using some function and formula.

谢谢乔西

推荐答案

首先，时间没有减法运算符.所以使用

First of all there is no substract operator for time. So using

SELECT time_in_only - time_out_only
FROM trip

你会得到:

操作数数据类型时间对于减法运算符无效.

Operand data type time is invalid for subtract operator.

如果你想要计算列，你可以先DROP column并重新创建它:

If you want computed column you can first DROP column and recreate it:

ALTER TABLE Trip
DROP COLUMN time_in_flight;

ALTER TABLE Trip
ADD time_in_flight 
  AS  DATEADD(mi, DATEDIFF(mi, time_out_only,time_in_only), CAST('00:00:00' AS TIME));

LiveDemo

如果不想重新创建表结构，手动计算使用UPDATE:

If you don't want to recreate table structure, and calculate manually use UPDATE:

UPDATE Trip
SET time_in_flight = DATEADD(mi, 
                  DATEDIFF(mi, time_out_only,time_in_only), CAST('00:00:00' AS TIME));

LiveDemo2

最后一种可能的解决方案是创建视图:

Last possible solution is to create view:

CREATE VIEW vw_Trip
AS
SELECT -- cols,
   time_in_flight = DATEADD(mi,
                 DATEDIFF(mi, time_out_only,time_in_only), CAST('00:00:00' AS TIME))
FROM Trip

这篇关于回复:通过减去 SQL Server 中的其他两列来计算列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！