MySQL转置查询结果行中的列

r9f1avp5 于 2022-10-31 发布在 Mysql

关注(0)|答案(2)|浏览(84)

在我的MySQL表Product中，我想计算有多少Product_Id保存在Fav、Purchased或Delivered中，然后按Fav、Purchase、Delivery或相反的顺序将结果组织在空值或非空值的行中，如下所述。
使用此查询：

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

谢谢你的帮助

mysql

来源：https://stackoverflow.com/questions/74206925/mysql-transpose-columns-in-rows-for-a-query-result

2条答案

按热度按时间

sbdsn5lh1#

我无法运行测试，因为我不知道您的数据库是如何构建的，但这可能会为您的第一个选项做工作：

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

赞(0）回复(0）举报 2022-10-31

23c0lvtd2#

也许您可以将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

赞(0）回复(0）举报 2022-10-31

我来回答

MySQL转置查询结果行中的列

2条答案

相关问题

热门标签

最新问答