gpt4 book ai didi

MySQL 按查询分组

转载 作者:行者123 更新时间:2023-11-29 08:54:05 25 4
gpt4 key购买 nike

Please see link to get table in better format

在 MySQL 数据库中,我有两个表“VEHICLE”和“VEHICLE_CLASS”,如下所示:

VEHICLE 
-------------------------------------------
VEHICLE_ID | VEHICLE_CLASS_ID | STATUS
-------------------------------------------
vehicle_001 | vehicle_class_001 | 0
vehicle_002 | vehicle_class_002 | 1
vehicle_003 | vehicle_class_003 | 2
vehicle_004 | vehicle_class_001 | 0
vehicle_005 | vehicle_class_002 | 2
vehicle_006 | vehicle_class_001 | 0
vehicle_007 | NULL | 1
----------------------------------------------


VEHICLE_CLASS
------------------------------------------
VEHICLE_CLASS_ID | VEHICLE_CLASS_NAME
-----------------------------------------
vehicle_class_001 | ABC
vehicle_class_002 | BCD
vehicle_class_003 | EFG
vehicle_class_004 | XYZ
vehicle_class_005 | PQR
vehicle_class_006 | STU
---------------------------------------
  • 共有三种状态:0 = 已启动、1 = 进行中、2 = 已完成

我需要一个查询来获取结果行,如下所示:

----------------------------------------------------------------------- 
VEHICLE_CLASS_NAME | COMPLETED_ VEHICLES | NOT_COMPLETED_ VEHICLES
---------------------------------------------------------------------------
ABC | |vehicle_001, vehicle_004, vehicle_006
BCD | vehicle_005 |vehicle_002
EFG | vehicle_003 |
  • 如果 Status=2 则已完成,否则未完成。

这是我编写的查询,但它没有给我正确的行。

SELECT 
veh.VEHICLE_CLASS_ID,
vehclass.VEHICLE_CLASS_NAME,
IF(STATUS=2, GROUP_CONCAT(`VEHICLE_ID`),'') COMPLETED_ VEHICLES,
IF(STATUS<2,GROUP_CONCAT(`VEHICLE_ID`),'') NOT_COMPLETED_ VEHICLES
FROM
VEHICLE veh
LEFT JOIN VEHICLE_CLASS vehclass
on veh.VEHICLE_CLASS_ID = vehclass.VEHICLE_CLASS_ID
GROUP BY
veh.VEHICLE_CLASS_ID
having
veh.VEHICLE_CLASS_ID is not null;

请帮助我,我怎样才能获得结果行。谢谢。

最佳答案

MySQL

SELECT 
vehclass.VEHICLE_CLASS_NAME,
GROUP_CONCAT(CASE veh.STATUS WHEN 2 THEN `VEHICLE_ID` ELSE NULL END) COMPLETED_VEHICLES,
GROUP_CONCAT(CASE veh.STATUS WHEN 2 THEN NULL ELSE `VEHICLE_ID` END) NOT_COMPLETED_VEHICLES
FROM VEHICLE veh
LEFT JOIN VEHICLE_CLASS vehclass
ON veh.VEHICLE_CLASS_ID = vehclass.VEHICLE_CLASS_ID
GROUP BY veh.VEHICLE_CLASS_ID
HAVING veh.VEHICLE_CLASS_ID is not null;

SQLFiddle

MSSQL 2008

SELECT 
vehclass.VEHICLE_CLASS_NAME,
STUFF((SELECT CASE v.STATUS WHEN 2 THEN ',' + v.VEHICLE_ID ELSE '' END
FROM Vehicle v
WHERE v.VEHICLE_CLASS_ID = veh.VEHICLE_CLASS_ID
FOR XML PATH (''))
, 1, 1, '') as COMPLETED_VEHICLES,
STUFF((SELECT CASE v.STATUS WHEN 2 THEN '' ELSE ',' + v.VEHICLE_ID END
FROM Vehicle v
WHERE v.VEHICLE_CLASS_ID = veh.VEHICLE_CLASS_ID
FOR XML PATH (''))
, 1, 1, '') as NOT_COMPLETED_VEHICLES
FROM VEHICLE veh
LEFT JOIN VEHICLE_CLASS vehclass
ON veh.VEHICLE_CLASS_ID = vehclass.VEHICLE_CLASS_ID
GROUP BY veh.VEHICLE_CLASS_ID, vehclass.VEHICLE_CLASS_NAME
HAVING veh.VEHICLE_CLASS_ID is not null;

SQLFiddle

甲骨文

为了在oracle中实现这一点,请参见this link使用什么来代替 GROUP_CONCAT

  1. 自定义用户定义的聚合函数 - 很可能是您的最佳选择
  2. wm_concat - 未记录、不受支持,可能存在。
  3. listagg - 11g 版本 2

关于MySQL 按查询分组,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/10435594/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com