如果datediff大于

Question

只有当date_start和date_end的时间差大于3个月时，我才会从lat询问用户。我试过很多东西，但都没有用。这是我迄今为止所做的声明：

SELECT accounts.account_id, accounts.name, accounts.active_club_id, 
accounts.phone, MIN(shifts_accounts.date_start) as 'datestart', MAX(shifts_accounts.date_end) as 'dateend' 
FROM `shifts_accounts` 
JOIN accounts ON shifts_accounts.account_id = accounts.account_id 
JOIN accounts_groups ON accounts.account_id = accounts_groups.account_id 
WHERE accounts_groups.group_id = 7 
AND DATEDIFF('dateend',  'datestart') > 90 
AND accounts.active_club_id != 1 
AND shifts_accounts.date_start > '2014-11-28' 
AND shifts_accounts.date_start < '2017-11-28' GROUP BY accounts.account_id

Answer 1

下面是一个有效查询的示例。我不认为有足够的信息来说明你是否想要.

SELECT a.account_id
     , a.name
     , a.active_club_id
     , a.phone
     , MIN(sa.date_start) datestart
     , MAX(sa.date_end) dateend 
  FROM shifts_accounts sa
  JOIN accounts a
    ON a.account_id = sa.account_id 
  JOIN accounts_groups ag
    ON ag.account_id = a.account_id 
 WHERE ag.group_id = 7 
   AND a.active_club_id != 1 
   AND sa.date_start BETWEEN '2014-11-28' AND '2017-11-28' 
 GROUP 
    BY a.account_id
HAVING DATEDIFF(dateend, datestart) > 90;

有关进一步帮助，请参见：Why should I provide an MCVE for what seems to me to be a very simple SQL query?

Answer 2

试试这个，

SELECT accounts.account_id, accounts.name, accounts.active_club_id, 
accounts.phone, MIN(shifts_accounts.date_start) as 'datestart', MAX(shifts_accounts.date_end) as 'dateend' 
FROM `shifts_accounts` 
JOIN accounts ON shifts_accounts.account_id = accounts.account_id 
JOIN accounts_groups ON accounts.account_id = accounts_groups.account_id 
WHERE accounts_groups.group_id = 7 
AND DATEDIFF('dateend',  'datestart') > 90 
AND accounts.active_club_id != 1 
AND shifts_accounts.date_start < DATE_SUB(NOW(),INTERVAL 3 YEAR) GROUP BY accounts.account_id