复杂的sql时间计算

我有2个表VISIT和CUSTINFO 在VISIT中,我有以下内容。
EnterTime, NextTime, CustInfono
在CUSTINFO,我有以下内容。
ApptTime, CustInfono
以下是我在简单的if-else语句中的逻辑。
if(visit.custinfono==0)

{ Result=NextTime-EnterTime }

else

{

where visit.custinfono= custinfo.custinfono    

if(EnterTime>ApptTime)

{ Result=NextTime-EnterTime }

else

{ Result=NextTime-ApptTime }

if(Result<0)

{ Result= 0 }

} 
我怎么能在sql语句中完成这个? 或者我是否必须查询所有必要的字段并在我的代码中进行计算?     
已邀请:
结果列在一列:
select
    case 
        when visit_custinfono = 0 then nexttime_minus_entertime 
        when visit_custinfono = custinfo_custinfono then
            case 
                when EnterTime > ApptTime then nexttime_minus_entertime_p 
                else nexttime_minus_appttime_p
            end
    end as result
from
(
    select
        visit_custinfono,
        custinfo_custinfono,
        EnterTime,
        ApptTime,
        NextTime - EnterTime as nexttime_minus_entertime,
        NextTime - ApptTime as nexttime_minus_appttime,
        case when nexttime_minus_entertime < 0 then 0 else nexttime_minus_entertime end as nexttime_minus_entertime_p,
        case when nexttime_minus_appttime < 0 then 0 else nexttime_minus_appttime end as nexttime_minus_appttime_p
    from
        (
            select
                visit.custinfono as visit_custinfono,
                custinfo.custinfono as custinfo_custinfono,
                NextTime,
                EnterTime,
                ApptTime,
                NextTime - EnterTime as nexttime_minus_entertime,
                NextTime - ApptTime as nexttime_minus_appttime
            from
                visit 
            left join
                custinfo
            on
                visit.custinfono = custinfo.custinfono
        ) as subquery1
) as subquery2
    
SELECT
     DATEDIFF(NextTime, EnterTime) as NoCustomerInfoDiffInDays,
     DATEDIFF(NextTime, EnterTime) as EnterAfterApptDiffInDays,
     DATEDIFF(NextTime, ApptTime) as EnterBeforeApptDiffInDays
FROM
     VISIT as v
LEFT OUTER JOIN
     CUSTINFO as ci
ON 
     v.CustInfono = ci.CustInfono
在您的C#中,根据您上面的条件决定将三个列中的哪一列用作每个记录的结果。     
正如@MacyAbbey所提到的,您需要使用的不仅仅是选择逻辑。我没有测试过这个,但认为它会起作用。
DELIMITER |

CREATE FUNCTION mydatediff(CustInfono INT, EnterTime TIMESTAMP, NextTime TIMESTAMP, ApptTime TIMESTAMP) RETURNS INT
  BEGIN
    DECLARE result INT DEFAULT 0;

    CASE Custinfono
      WHEN 0 THEN SET result = DATEDIFF(NextTime,EnterTime);
      ELSE
        BEGIN
          IF EnterTime>ApptTime THEN result = DATEDIFF(NextTime,EnterTime);
          ELSE SET result = DATEDIFF(NextTime,ApptTime);
          IF result < 0 THEN SET result = 0;
        END;
    END CASE;

    RETURN result;

  END;
  |
执行时:
SELECT            mydatediff(VISIT.CustInfono, VISIT.EnterTime,  VISIT.NextTime, CUSTINFO.AppTime)
FROM              VISIT
  LEFT OUTER JOIN CUSTINFO
  ON              CUSTINFO.CustInfono = VISIT.CustInfono
请原谅我的疲劳是否会妨碍此代码; p     

要回复问题请先登录注册