HAVING，WHERE和GROUP BY子句，何时使用它们以及是否使用'' [英] HAVING vs WHERE vs GROUP BY clauses, when to use them and if you use ' '

问题描述

希望这篇文章能帮助我和其他许多我更好地理解WHERE，HAVING，GROUP BY等问题.每个人都有自己的语法用法，并且有多种方法可以使MYSQL正常工作，想法是在帮助我的同时帮助整个社区:)下面是设计查询的一种建议方法.

Hopefully this post will help me and many others like me better understand the issues of WHERE, HAVING, GROUP BY etc. Everyone has their own way of doing syntax and since there is more than one way to make something work in MYSQL the idea would be to help me make this work while helping the community at large too :) Below is one suggested way of designing my query.

SELECT t1.post_id, t2.name,
           MAX(case when meta_key = 'value' THEN `meta_value` ELSE NULL END) as  Email,
       MAX(CASE WHEN `meta_key` = 'value' THEN `meta_value` ELSE NULL END) as CustomerId,
       MAX(CASE WHEN `meta_key` = 'value' THEN `meta_value` ELSE NULL  END) as DeliveryDate,
        MAX(CASE WHEN `meta_key` = 'value' THEN `meta_value` ELSE NULL  END) as DeliveryTime,
        MAX(CASE WHEN `meta_key` = 'value' THEN `meta_value` ELSE NULL  END) as DeliveryType,
          MAX(case when meta_key = 'value' THEN `meta_value` ELSE NULL END) as  Zip,
       MAX(case when meta_key = 'value' THEN `meta_value` ELSE NULL END) as  OrderNote,
       MAX(case when meta_key = 'value' THEN `meta_value` ELSE NULL END) as  PaymentTotal,
       MAX(case when meta_key = 'value' THEN `meta_value` ELSE NULL END) as  OrderStatus

FROM table_A  t1
inner join table_B t2 on find_in_set(t1.post_id, t2.payment_ids)
where  OrderStatus rlike '%trans%|ready'
    and DeliveryDate >= current_date - interval 7 day
    and DeliveryType = 'pickup'
group by 
    t1.post_id, 
    t2.name

这会产生错误>>>>. #1054-"where子句"中的未知列"DeliveryDate""我猜想由于"orderStatus"会产生此错误.不是实际的列名，而是从另一个列中拉出的值，然后通过:变成自己的列:

This produces an error >>>> "#1054 - Unknown column 'DeliveryDate' in 'where clause'" I presume it produces this error since "orderStatus" is not an actual column name but is a value being pulled from another column and then being made its own column through the :

MAX(case when meta_key = '_order_status' THEN `meta_value` ELSE NULL END) as  OrderStatus

因此，我想我需要在语句的SELECT区域和WHERE区域中将名称括在''中.产生错误>>>>>>>>>>>>> "警告:#1292截断了错误的日期值:"DeliveryDate""

So I presumed that I needed to enclose the name in ' ' both in the SELECT area of the statements and in the WHERE area. BUT that produces the error >>>>>>>>>>>> "Warning: #1292 Truncated incorrect date value: 'DeliveryDate'"

为什么会这样，怎么解决?

Why would this be and whats the solution?

编辑由于某些人建议不能以上述方式使用WHERE子句，因此我在下面的代码中使用了HAVING子句.这是代码:

EDITING Because some have suggested the WHERE clause can not be used in the manner above, I have used the HAVING clause using the code below. Here is the code:

选择....... ^^从上方..............

SELECT.......^^from above..............

FROM table_A t1
inner join table_B t2 on find_in_set(t1.post_id, t2.payment_ids)
GROUP BY post_id
HAVING DeliveryDate = (DATE_SUB(CURDATE(), INTERVAL 7 DAY)) 
AND DeliveryType = 'pickup' 
AND  OrderStatus = 'ready' 
OR OrderStatus = 'transit'
ORDER BY 'DeliveryTime'  DESC

以上方法也不起作用.这里的问题是AND子句更重要，似乎使日期过滤器失效.当我使用此代码时，它将返回所有记录，而不考虑日期.

The above doesnt work either. The issue here is that the AND clauses are more important and seem to kncok out the date filter. When I use this code, this returns all records regardless of dates.

编辑2>>>>>>>>>尝试过也不过，它仍然无法过滤出3个月大的条目

EDIT 2 >>>>>>>>>> Tried this too btu it still doesnt filter out the 3 month old entry

选择....... ^^从上方..............

SELECT.......^^from above..............

FROM table_A t1
inner join table_B t2 on find_in_set(t1.post_id, t2.payment_ids)
GROUP BY post_id
HAVING MAX(CASE WHEN 'meta_key' = 'value' THEN 'meta_value' ELSE NULL END)>= current_date - interval 7 day 
AND DeliveryType = 'pickup' 
AND  OrderStatus = 'ready' 
OR OrderStatus = 'transit'
ORDER BY 'DeliveryTime'  DESC

编辑3>>>>>>>>>简化代码.结果相同.即使使用CURDATE()仍显示3个月的记录

EDIT 3 >>>>>>>>>> Simplifying the code. Same result. Even with CURDATE() still shows 3 month old records

......................

......................

FROM table_A t1
inner join table_B t2 on find_in_set(t1.post_id, t2.payment_ids)
GROUP BY post_id
HAVING MAX(CASE WHEN 'meta_key' = 'value' THEN 'meta_value' ELSE NULL END)= CURDATE()
AND DeliveryType = 'pickup' 
AND  OrderStatus = 'ready' 
OR OrderStatus = 'transit'
ORDER BY 'DeliveryTime'  DESC

< p>编辑4>>>>>>>>>>>>>>>>>>> 最小的例子...

EDIT 4 >>>>>>>>>>>>>>>>>>>>>> minimal exmaple...

选择t1.post_id，t2.name，

SELECT t1.post_id, t2.name,

   MAX(CASE WHEN `meta_key` = 'value' THEN `meta_value` ELSE NULL  END) as DeliveryDate,
    MAX(CASE WHEN `meta_key` = 'value' THEN `meta_value` ELSE NULL  END) as DeliveryTime,
    MAX(CASE WHEN `meta_key` = 'value' THEN `meta_value` ELSE NULL  END) as DeliveryType,
   MAX(case when meta_key = 'value' THEN `meta_value` ELSE NULL END) as  OrderStatus

FROM table_A t1
inner join table_B t2 on find_in_set(t1.post_id, t2.payment_ids)
GROUP BY post_id
HAVING MAX(CASE WHEN 'meta_key' = 'value' THEN 'meta_value' ELSE NULL END)= CURDATE()
AND DeliveryType = 'pickup' 
AND  OrderStatus = 'ready' 
OR OrderStatus = 'transit'
ORDER BY 'DeliveryTime'  DESC

我希望这只会返回今天的记录. IT会在满足其他HAVING子句要求的同时返回所有时间的所有记录

I expect this to return the records only of today. IT is return all records of all time while meeting the other HAVING clause requirements

推荐答案

根据@O的答案.琼斯是一个嵌套查询:

The answer as per @O. Jones is a nested query:

SELECT post_id
     , name
     , Email
     , CustomerId
     , DeliveryDate
     , DeliveryTime
     , DeliveryType
     , Zip
     , OrderNote
     , PaymentTotal
     , OrderStatus
  FROM ( SELECT t1.post_id
              , t2.name
              , MAX(CASE WHEN meta_key = 'value' THEN meta_value ELSE NULL END) as Email
              , MAX(CASE WHEN meta_key = 'value' THEN meta_value ELSE NULL END) as CustomerId
              , MAX(CASE WHEN meta_key = 'value' THEN meta_value ELSE NULL END) as DeliveryDate
              , MAX(CASE WHEN meta_key = 'value' THEN meta_value ELSE NULL END) as DeliveryTime
              , MAX(CASE WHEN meta_key = 'value' THEN meta_value ELSE NULL END) as DeliveryType
              , MAX(CASE WHEN meta_key = 'value' THEN meta_value ELSE NULL END) as Zip
              , MAX(CASE WHEN meta_key = 'value' THEN meta_value ELSE NULL END) as OrderNote
              , MAX(CASE WHEN meta_key = 'value' THEN meta_value ELSE NULL END) as PaymentTotal
              , MAX(CASE WHEN meta_key = 'value' THEN meta_value ELSE NULL END) as OrderStatus
           FROM table_A t1
         INNER 
           JOIN table_B t2 
             ON FIND_IN_SET(t1.post_id, t2.payment_ids)  
         GROUP 
             BY t1.post_id
              , t2.name  
       ) AS derived_table
 WHERE OrderStatus RLIKE '%trans%|ready'
   AND DeliveryDate >= CURRENT_DATE - INTERVAL 7 DAY
   AND DeliveryType = 'pickup'

这篇关于HAVING，WHERE和GROUP BY子句，何时使用它们以及是否使用''的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！