搜索记录并在sql数据库中的行中插入新值。

现在我必须写一个查询来检查从检查时间开始的时间，如果它是> 7：00：00和< 12：00：00然后将10插入列COST和20列公司通行证和总计成本+公司通过列总计即= 30， 否则，如果检查时间> 12：00：00，COST = 20且公司通行证= 30且总计= COST + COMPANY PASS。I have one table checkinoutcolumn name:EMPIDCHECKTIMEcostcompany passtotalnow i have to write a query to search the time from checktime if it is >7:00:00 and <12:00:00 then insert 10 into column COST and 20 Column COMPANY PASS and total of COST+COMPANY PASS into Column Total ie=30,else if the checktime >12:00:00 the COST=20 and COMPANY PASS=30 and total =COST+COMPANY PASS.推荐答案 这里没有显示字段的数据类型。 如果有效，请参阅以下查询。Hi, Here you have not shown datatype of fields. See following query, if it works.UPDATE checkinoutSET cost=10,companypass=20,total=30WHERE CAST(CHECKTIME AS DATETIME) BETWEEN CAST('7:00:00' AS DATETIME) AND CAST('12:00:00' AS DATETIME)UPDATE checkinoutSET cost=20,companypass=30,total=50WHERE CAST(CHECKTIME AS DATETIME) > CAST('12:00:00' AS DATETIME) 如果您正在考虑将EMPID作为条件然后检查以下查询。If you are considering EMPID as where conditions then check following query.UPDATE checkinoutSET cost=10,companypass=20,total=30WHERE CAST(CHECKTIME AS DATETIME) BETWEEN CAST('7:00:00' AS DATETIME) AND CAST('12:00:00' AS DATETIME) AND EMPID=@EMPIDUPDATE checkinoutSET cost=20,companypass=30,total=50WHERE CAST(CHECKTIME AS DATETIME) &gt; CAST('12:00:00' AS DATETIME) AND EMPID=@EMPID 这两个查询都将执行，其中一个将更新EMPID的值。 另一种方法是使用if和else条件。This both query will execute and one of them will update value for EMPID.Another way is using if and else conditions. 如果你想插入然后使用..Hi,if you want to insert then use..insert into tablenameselect [EMPID],[CHECKTIME],case when datepart(HOUR,[CHECKTIME])>7 and datepart(HOUR,[CHECKTIME]) < 12 then 10 when datepart(HOUR,[CHECKTIME])>12 then 20 else [cost] end as [cost],case when datepart(HOUR,[CHECKTIME])>7 and datepart(HOUR,[CHECKTIME])< 12 then 20 when datepart(HOUR,[CHECKTIME])>12 then 30 else [company pass] end as [company pass],case when datepart(HOUR,[CHECKTIME])>7 and datepart(HOUR,[CHECKTIME]) < 12 then 30 when datepart(HOUR,[CHECKTIME])>12 then 50 else [Total ] end as [Total ]from checkinout 如果你需要更新and if you need to updateupdate checkinoutset cost= case when datepart(HOUR,[CHECKTIME])>7 and datepart(HOUR,[CHECKTIME]) < 12 then 10 when datepart(HOUR,[CHECKTIME])>12 then 20 else [cost] end,[companypass]=case when datepart(HOUR,[CHECKTIME])>7 and datepart(HOUR,[CHECKTIME])< 12 then 20 when datepart(HOUR,[CHECKTIME])>12 then 30 else [company pass] end,[Total]=case when datepart(HOUR,[CHECKTIME])>7 and datepart(HOUR,[CHECKTIME]) < 12 then 30 when datepart(HOUR,[CHECKTIME])>12 then 50 [Total ] end 这篇关于搜索记录并在sql数据库中的行中插入新值。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！