在where子句中使用Select Min和MAx

I am using a number of select statements to drive results, however I have 2 select statements which returns the minimum time for appointment and max time for appointment. However where the min and max are the same, I want to remove from within my where clause.

我使用了一些select语句来驱动结果，但是我有2个select语句，它们返回约会的最短时间和约会的最长时间。但是，如果min和max相同，我想从where子句中删除。

SELECT
  APPOINTMENTS.userid,
  users.LOCATIONID,
  MIN(APPOINTMENTTIME) AS mintime,
  MAX(APPOINTMENTTIME) AS maxtime

FROM appointments
WHERE APPOINTMENTDATE BETWEEN '2017-01-07' AND '2017-01-07'
AND NOT mintime <> maxtime
GROUP BY appointments.USERID,
         users.LOCATIONID,
         appointments.APPOINTMENTDATE

I get the error Mintime is not valid.

我收到错误Mintime无效。

I am sure I need to hold this in a new sub select but not sure..

我确信我需要在新的子选择中保留它但不确定..

Cheers

干杯

3 个解决方案

#1

The HAVING clause is used for aggregate functions, e.g.

HAVING子句用于聚合函数，例如

HAVING NOT MIN(APPOINTMENTTIME) <> MAX(APPOINTMENTTIME)

I don't know why you are using a double negative in your predicate though, based on your description you just need <>

我不知道为什么你在谓词中使用双重否定，根据你的描述你只需要<>

HAVING MIN(APPOINTMENTTIME) <> MAX(APPOINTMENTTIME)

#2

Try this as per Sql Server

按照Sql Server尝试这个

        SELECT * FROM (
        select   
        APPOINTMENTS.userid, 
        users.LOCATIONID,
        min(APPOINTMENTTIME) as mintime, 
        max(APPOINTMENTTIME) as maxtime
        from appointments
        where 
        APPOINTMENTDATE between '2017-01-07' and '2017-01-08'

        group by 
        appointments.USERID,users.LOCATIONID,
        appointments.APPOINTMENTDATE
        )as t WHERE  mintime <> maxtime

#3

Please try

请尝试

select   
APPOINTMENTS.userid, 
users.LOCATIONID,
min(APPOINTMENTTIME) as mintime, 
max(APPOINTMENTTIME) as maxtime

from appointments
where 
APPOINTMENTDATE between '2017-01-07' and '2017-01-07'

group by 
appointments.USERID,users.LOCATIONID,
appointments.APPOINTMENTDATE
having min(APPOINTMENTTIME)  <> max(APPOINTMENTTIME)

#1