問題描述
希望這篇文章能幫助我和許多像我一樣的人更好地理解 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'
這篇關于HAVING vs WHERE vs GROUP BY 子句，何時使用它們以及是否使用 &#39;&#39;的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！