HAVING vs WHERE vs GROUP BY 子句，何时使用它们以及是否使用 ''
希望这篇文章能帮助我和许多像我一样的人更好地理解 WHERE、HAVING、GROUP BY 等问题.每个人都有自己的语法处理方式，因为在 MYSQL 中有不止一种方法可以使某些事情起作用我的想法是帮助我完成这项工作，同时也帮助整个社区:) 下面是设计我的查询的一种建议方法.
SELECT t1.post_id, t2.name,MAX(case when meta_key = 'value' THEN `meta_value` ELSE NULL END) 作为电子邮件，MAX(CASE WHEN `meta_key` = 'value' THEN `meta_value` ELSE NULL END) 作为 CustomerId，MAX(CASE WHEN `meta_key` = 'value' THEN `meta_value` ELSE NULL END) 作为 DeliveryDate，MAX(CASE WHEN `meta_key` = 'value' THEN `meta_value` ELSE NULL END) 作为 DeliveryTime，MAX(CASE WHEN `meta_key` = 'value' THEN `meta_value` ELSE NULL END) 作为 DeliveryType，MAX(case when meta_key = 'value' THEN `meta_value` ELSE NULL END) 作为 Zip，MAX(case when meta_key = 'value' THEN `meta_value` ELSE NULL END) 作为 OrderNote，MAX(case when meta_key = 'value' THEN `meta_value` ELSE NULL END) 作为 PaymentTotal,MAX(case when meta_key = 'value' THEN `meta_value` ELSE NULL END) 作为 OrderStatusFROM table_A t1find_in_set(t1.post_id, t2.payment_ids) 上的内连接 table_B t2其中 OrderStatus rlike '%trans%|ready'和 DeliveryDate >= current_date - 间隔 7 天和 DeliveryType = 'pickup'通过...分组t1.post_id,t2.name这会产生一个错误 >>>>#1054 - 'where 子句'中的未知列'DeliveryDate'"我认为它会产生这个错误，因为orderStatus"不是实际的列名，而是从另一列中提取的值，然后通过 :
MAX(case when meta_key = '_order_status' THEN `meta_value` ELSE NULL END) as OrderStatus所以我假设我需要在语句的 SELECT 区域和 WHERE 区域中将名称括在 ' ' 中.但是产生错误 >>>>>>>>>>>警告:#1292 截断的错误日期值:'DeliveryDate'"
为什么会这样?解决方案是什么?
<块引用><块引用><块引用>EDITING 因为有人建议不能以上述方式使用 WHERE 子句，所以我使用下面的代码使用了 HAVING 子句.代码如下:
选择.......^^从上面......
FROM table_A t1find_in_set(t1.post_id, t2.payment_ids) 上的内连接 table_B t2GROUP BY post_idHAVING DeliveryDate = (DATE_SUB(CURDATE(), INTERVAL 7 DAY))AND DeliveryType = '提货'AND OrderStatus = '准备好'或 OrderStatus = 'transit'ORDER BY 'DeliveryTime' DESC以上也行不通.这里的问题是 AND 子句更重要，并且似乎淘汰了日期过滤器.当我使用此代码时，无论日期如何，它都会返回所有记录.
编辑 2 >>>>>>>>>也试过了，但它仍然没有过滤掉 3 个月大的条目
选择.......^^从上面......
FROM table_A t1find_in_set(t1.post_id, t2.payment_ids) 上的内连接 table_B t2GROUP BY post_idHAVING MAX(CASE WHEN 'meta_key' = 'value' THEN 'meta_value' ELSE NULL END)>= current_date - 间隔 7 天AND DeliveryType = '提货'AND OrderStatus = '准备好'或 OrderStatus = 'transit'ORDER BY 'DeliveryTime' DESC编辑 3 >>>>>>>>简化代码.结果一样.即使使用 CURDATE() 仍然显示 3 个月前的记录
......................
FROM table_A t1find_in_set(t1.post_id, t2.payment_ids) 上的内连接 table_B t2GROUP BY post_idHAVING MAX(CASE WHEN 'meta_key' = 'value' THEN 'meta_value' ELSE NULL END)= CURDATE()AND DeliveryType = '提货'AND OrderStatus = '准备好'或 OrderStatus = 'transit'ORDER BY 'DeliveryTime' DESC编辑 4 >>>>>>>>>>>>>>>>>最小的例子...
选择 t1.post_id, t2.name,
MAX(CASE WHEN `meta_key` = 'value' THEN `meta_value` ELSE NULL END) 作为 DeliveryDate，MAX(CASE WHEN `meta_key` = 'value' THEN `meta_value` ELSE NULL END) 作为 DeliveryTime，MAX(CASE WHEN `meta_key` = 'value' THEN `meta_value` ELSE NULL END) 作为 DeliveryType，MAX(case when meta_key = 'value' THEN `meta_value` ELSE NULL END) 作为 OrderStatusFROM table_A t1find_in_set(t1.post_id, t2.payment_ids) 上的内连接 table_B t2GROUP BY post_idHAVING MAX(CASE WHEN 'meta_key' = 'value' THEN 'meta_value' ELSE NULL END)= CURDATE()AND DeliveryType = '提货'AND OrderStatus = '准备好'或 OrderStatus = 'transit'ORDER BY 'DeliveryTime' DESC我希望这只会返回今天的记录.IT 是在满足其他 HAVING 子句要求的同时返回所有时间的所有记录
解决方案按照@O 的答案.Jones 是一个嵌套查询:
SELECT post_id， 姓名， 电子邮件， 顾客ID， 邮寄日期， 交货时间, 交货类型， 压缩, 订单备注, 支付总额， 订单状态从(选择 t1.post_id, t2.name, MAX(CASE WHEN meta_key = 'value' THEN meta_value ELSE NULL END) 作为电子邮件, MAX(CASE WHEN meta_key = 'value' THEN meta_value ELSE NULL END) 作为 CustomerId, MAX(CASE WHEN meta_key = 'value' THEN meta_value ELSE NULL END) 作为 DeliveryDate, MAX(CASE WHEN meta_key = 'value' THEN meta_value ELSE NULL END) 作为 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) 作为 OrderStatusFROM table_A t1内JOIN 表_B t2ON FIND_IN_SET(t1.post_id, t2.payment_ids)团体由 t1.post_id, t2.name) AS 派生表WHERE OrderStatus RLIKE '%trans%|ready'AND DeliveryDate >= CURRENT_DATE - 间隔 7 天AND DeliveryType = '提货'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
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
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?
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
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.
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
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
EDIT 4 >>>>>>>>>>>>>>>>>>>>>> minimal exmaple...
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
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
解决方案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'
相关文章