Mysql中的GROUP_CONCAT使用

 SELECT
    res.ITRId Id,
    res.ITRResourceName ResourceName,
    res.ITRSupplierName SupplierName,
    res.ITRDeptCityName DeptCityName,
    res.ITRDeptAirportName DeptAirportName,
    res.ITRDataFlag DataFlag,
    res.ITRDeptCityId,
    res.ITRSupplierId,
    res.ITRTcManagerId,
    bus.ITFDestCityName DestCityName,
    bus.ITFDestAirportName DestAirportName,
    city.ITCTransPortCityName
    -- (select GROUP_CONCAT(city.ITCTransPortCityName separator  ',' ) TransportCityName from SGRInternationalTransportCity city where   city.ITCTransportId = res.ITRId) as TransportCityName
    FROM SGRInternationalTransportResources res
    left JOIN SGRInternationalTransportFlightResource bus ON res.ITRId = bus.ITFTransportId
    left JOIN SGRInternationalTransportCity city ON city.ITCTransportId = res.ITRId
    where res.ITRResourceType =2

执行的结果如下:

表联合查询后ID相同的存在多条记录;其他字段值均一样仅ITCTransPortCityName字段值不同;

使用GROUP_CONCAT 优化后:

 SELECT
    res.ITRId Id,
    res.ITRResourceName ResourceName,
    res.ITRSupplierName SupplierName,
    res.ITRDeptCityName DeptCityName,
    res.ITRDeptAirportName DeptAirportName,
    res.ITRDataFlag DataFlag,
    res.ITRDeptCityId,
    res.ITRSupplierId,
    res.ITRTcManagerId,
    bus.ITFDestCityName DestCityName,
    bus.ITFDestAirportName DestAirportName,
    (select GROUP_CONCAT(city.ITCTransPortCityName separator  ',' ) TransportCityName from SGRInternationalTransportCity city where   city.ITCTransportId = res.ITRId) as TransportCityName
    FROM SGRInternationalTransportResources res
    left JOIN SGRInternationalTransportFlightResource bus ON res.ITRId = bus.ITFTransportId
    left JOIN SGRInternationalTransportCity city ON city.ITCTransportId = res.ITRId
    where res.ITRResourceType =2

已经初步得到我们想要的结果了;可结果集还是三条记录,列表呈现依然存在问题...进一步优化如下:

去除:

    left JOIN SGRInternationalTransportCity city ON city.ITCTransportId = res.ITRId

得到结果如下:

如果需要对 ITCTransPortCityName 这个字段进行查询该如何处理?用 like 么?使用 exists 进行处理

 and exists (select 1 from SGRInternationalTransportCity city where res.ITRId = city.ITCTransportId and city.ITCTransPortCityId=226 )    

已成功筛选 城市ID为 226 的城市

 

转载于:https://www.cnblogs.com/zhangruiBlog/p/8421417.html

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值
>