在我的 MySQL 表Product中，我想計算有多少 Product_Id 已保存在 Fav、Purched 或 Delivered 中。然后將結果按列中的收藏、購買、交付或反向組織為 null 或不為 null 的行，就像我在下面解釋的那樣。
使用此查詢：
SELECT COUNT(IF(Fav IS NOT NULL, ID, NULL)) AS "Fav_Not-null", COUNT(IF(Fav IS NULL, ID, NULL)) AS "Fav_Null" ,
COUNT(IF(Purchase IS NOT NULL, ID, NULL)) AS "Purchase_Not-null", COUNT(IF(Purchase IS NULL, ID, NULL)) AS "Purchase_Null" ,
COUNT(IF(Delivery IS NOT NULL, ID, NULL)) AS "Delivery_Not-null", COUNT(IF(Delivery IS NULL, ID, NULL)) AS "Delivery_Null"
FROM Product
我有這個結果：
# Fav_Not-null Fav_null Purchase_Not-null Purchase_null Delivery_Not-null Delivery_null
1 75 25 53 47 27 73
沒關系，但我想以不同的方式顯示結果，例如：
Fav Purchase Delivery
Null 25 47 73
Not-null 75 53 27
或喜歡：
Null Not-null
Fav 25 75
Purchase 47 53
Delivery 73 27
謝謝您的幫助
uj5u.com熱心網友回復：
我無法運行測驗，因為我不知道您的資料庫是如何構建的，但這可能會為您的第一個選項作業：
SELECT 'null' as '', COUNT(IF(Fav IS NULL, ID, NULL)) as "Fav", COUNT(IF(Purchase IS NULL, ID, NULL)) AS "Purchase", COUNT(IF(Delivery IS NULL, ID, NULL)) AS "Delivery"
FROM Product
union
SELECT 'not-null', COUNT(IF(Fav IS NOT NULL, ID, NULL)), COUNT(IF(Purchase IS NOT NULL, ID, NULL)), COUNT(IF(Delivery IS NOT NULL, ID, NULL))
FROM Product;
第二個選項：
Select 'Fav' as '', COUNT(IF(Fav IS NULL, ID, NULL)) as 'Null', COUNT(IF(Fav IS NOT NULL, ID, NULL)) as 'Not-null'
FROM Product
UNION
Select 'Purchase', COUNT(IF(Purchase IS NULL, ID, NULL)), COUNT(IF(Purchase IS NOT NULL, ID, NULL))
FROM Product
UNION
select 'Delivery', COUNT(IF(Delivery IS NULL, ID, NULL)), COUNT(IF(Delivery IS NOT NULL, ID, NULL))
FROM Product
uj5u.com熱心網友回復：
也許您可以使用帶有兩個查詢的 UNION ：
SELECT
'Null' AS TYPE
COUNT(IF(Fav IS NOT NULL, ID, NULL)) AS "Fav_Not-null" AS Fav,
COUNT(IF(Purchase IS NULL, ID, NULL)) AS "Purchase_Null" AS Purchase,
COUNT(IF(Delivery IS NULL, ID, NULL)) AS "Delivery_Null" AS Delivery
FROM Product
UNION
SELECT
'Not-null' AS TYPE,
COUNT(IF(Fav IS NULL, ID, NULL)) AS "Fav_Null" AS Fav,
COUNT(IF(Purchase IS NOT NULL, ID, NULL)) AS "Purchase_Not-null" AS Purchase,
COUNT(IF(Delivery IS NOT NULL, ID, NULL)) AS "Delivery_Not-null" AS Delivery
FROM Product
轉載請註明出處，本文鏈接：https://www.uj5u.com/qianduan/521034.html
標籤：mysqlsql
上一篇：如何獲得表中的兩個最大值？