oracle sql查询结果某字段行转列，逗号分隔

最新推荐文章于 2025-02-20 00:08:38 发布

weixin_30770495

最新推荐文章于 2025-02-20 00:08:38 发布

阅读量676

点赞数

CC 4.0 BY-SA版权

文章标签：数据库

原文链接：http://www.cnblogs.com/Linger-wj/p/6727620.html

本文介绍了一个使用 SQL 中 LISTAGG 函数的实际案例，通过聚合函数结合 CASE WHEN 语句来处理数据，实现对不同角色的判断与整合。此案例涉及车主和司机的角色区分，并展示了如何在 SQL 查询中进行分组与条件判断。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

LISTAGG(BS,',') WITHIN GROUP(ORDER BY ENGINE_NO)

SQL实例：

query.append("SELECT LICENSE_PLATE,SVIN,MAINTENANCE_DATE,DEFAULT_VIN,ENGINE_NO, \n");
                query.append("       case when LISTAGG(BS,',') WITHIN GROUP(ORDER BY ENGINE_NO) = 'DRIVER,OWNER' then 'OWNER' \n");
                query.append("       when LISTAGG(BS,',') WITHIN GROUP(ORDER BY ENGINE_NO) = 'OWNER' then 'OWNER' \n");
                query.append("       when LISTAGG(BS,',') WITHIN GROUP(ORDER BY ENGINE_NO) = 'DRIVER'then 'DRIVER' \n");
                query.append("       END BS  \n");
                query.append("FROM (  \n");
                query.append("      SELECT DISTINCT * FROM (SELECT MV.LICENSE_PLATE,MV.SVIN, \n");
                query.append("             TO_CHAR(MV.MAINTENANCE_DATE,'yyyy-MM-dd') MAINTENANCE_DATE,MVM.DEFAULT_VIN, \n");
                query.append("             MV.ENGINE_NO,'OWNER' BS \n");//OWNER 车主
                query.append("             FROM CMS.MAIN_VEHICLE_MENBER MVM,MAIN_VEHICLE MV \n");
                query.append("             WHERE MVM.OWNER_ID = MV.OWNER_ID \n");
                query.append("             AND MVM.MOBILE = '"+telephone+"' \n");
                query.append("      UNION \n");
                query.append("      SELECT MV.LICENSE_PLATE,MV.SVIN,TO_CHAR(MV.MAINTENANCE_DATE,'yyyy-MM-dd') MAINTENANCE_DATE, \n");//DRIVER 司机
                query.append("             DV.DEFAULT_VIN,MV.ENGINE_NO, 'DRIVER' BS FROM ( \n");
                query.append("             SELECT MVM.OWNER_ID,MVM.DEFAULT_VIN,MODR.VIN FROM CMS.MAIN_VEHICLE_MENBER MVM,CMS.MAIN_VEHICLE_DRIVER_RELATION MODR \n");
                query.append("             WHERE MVM.OWNER_ID = MODR.DRIVER_ID \n");
                query.append("             AND MVM.MOBILE = '"+telephone+"' \n");
                query.append("      ) DV, MAIN_VEHICLE MV \n");
                query.append("      WHERE DV.VIN = MV.SVIN) \n");
                query.append(" ) GROUP BY LICENSE_PLATE,SVIN,MAINTENANCE_DATE,DEFAULT_VIN,ENGINE_NO \n");