mysql行转列聚合问题

最新推荐文章于 2024-02-01 17:18:00 发布

小炎说java

最新推荐文章于 2024-02-01 17:18:00 发布

阅读量135

点赞数

文章标签： mysql

本文链接：https://blog.csdn.net/weixin_45697659/article/details/130170747

版权

背景：联查表时，任何信息相同，除了一个字段不同，且有多个，这时候可以采用列转行来实现

一、语法

二、全聚合问题

一、语法

1： GROUP_CONCAT( DISTINCT 表.buyer ) as 字段名

2：distinct表示去重

二、全聚合问题

1：使用此方式进行行转列，必须将所有的查询字段都放在聚合条件中

SELECT
        tq.quality_id,
        quality_no,
        inspection_time,
        check_peo,
        no_quality_reason,
        problem_kind,
        no_quality_state,
        qualified_percent,
        tq.more_num,
        tq.more_status,
        tq.type,
        quality_time,
        inspection_num,
        check_num,
        quality_num,
        no_quality_num,
        quality_level,
        problem_type,
        tq.remark,
        tq.state,
        problem_feedback,
        problem_deal,
        tq.deal_method,
        tq.active,
        tq.price,
        subscribe.requisitioner,
        subscribe.place,
        subscribe.subscribe_type,
        subscribe.detail_no,
        subscribe.purpose,
        subscribe.num,
        subscribe.plan_period,
        product.product_name,
        product.reference_name,
        product.product_no,
        product.specifications,
        product.unit,
        supplier_name,
        puorder.delivery_time,
        receive.receive_time,
        GROUP_CONCAT( DISTINCT sb.buyer ) bidder
        FROM
        t_daily_quality tq
        LEFT JOIN t_daily_subscribe subscribe ON tq.detail_id = subscribe.detail_id
        LEFT JOIN t_daily_product product ON tq.product_id = product.product_id
        LEFT JOIN t_daily_supplier supplier ON tq.supplier_id = supplier.supplier_id
        LEFT JOIN t_production_subscribe_buyer_task  sb ON sb.detail_id = tq.detail_id AND sb.adoption = '1'
        LEFT JOIN t_daily_purchase_receive receive ON receive.receive_id = tq.receive_id
        LEFT JOIN t_daily_purchase_order puorder ON puorder.order_id = receive.order_id
GROUP BY
        quality_id,quality_no,inspection_time,check_peo,no_quality_reason,problem_kind,
        no_quality_state,qualified_percent,more_num,more_status,type,quality_time,
        inspection_num,check_num,quality_num,no_quality_num,quality_level,problem_type,
        remark,state,problem_feedback,problem_deal,deal_method,active,price,requisitioner,
        place,subscribe_type,detail_no,purpose,num,plan_period,product_name,reference_name,
        product_no,specifications,product.unit,supplier_name,delivery_time,receive_time