doujie9882 2018-02-13 06:35
浏览 316
已采纳

原始查询 - MYSQL(IF ELSE里面的sql)

Im trying to execute a query that collects all of the data.
So here is my condition if the client_type of a row is Corporation the query will fetch the data of corporation field and it will assign it to fullname or else it will fetch the firstname, lastname and middlename and assign it as fullname. I manage to do this query. but i have an error.

  (SELECT SA.id,SA.sa_code,S.importer_name as name,
  CONCAT_WS(" ",(IF(C.client_type = 'Corporation',C.corporation), 
  C.firstname,' ',C.middlename,' ',C.lastname)) as fullname,
  C.client_type,C.corporation,
  CI.invoice_code,CI.created_at as invoice_date,
  SA.created_at as salesagreement_date,
  S.id as seller_id, C.id as 
  buyer_id,SA.product_id,P.unit_code,SA.purchase_price
  FROM sales_agreement SA
  LEFT JOIN charge_invoice CI ON CI.id = SA.invoice_id
  LEFT JOIN importer S ON S.id = SA.seller_id
  LEFT JOIN clients C ON C.id = SA.buyer_id
  LEFT JOIN products P ON P.id = SA.product_id
  WHERE SA.deleted = 0
  ORDER BY SA.created_at DESC
  )P

enter image description here

  • 写回答

1条回答 默认 最新

  • duanguangwang5829 2018-02-13 06:39
    关注

    For the sake of clarity, I would replace your call to CONCAT_WS with an explicit CASE expression:

    SELECT
        SA.id,
        ...
        CASE WHEN C.client_type = 'Corporation'
             THEN C.corporation
             ELSE CONCAT_WS(' ', C.firstname, C.middlename, C.lastname) END AS fullname,
        ...
    FROM sales_agreement SA
    ...
    

    The problem with your current call to CONCAT_WS is that the IF function call does not have an else condition, and also you have a list of things wrapped in parentheses being passed as the first (and only) parameter to CONCAT_WS. In addition, if you pass a space separator to CONCAT_WS, then you don't have to also include spaces in the call itself. This is the whole point of CONCAT_WS that it handles the separator for you.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 求差集那个函数有问题,有无佬可以解决
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥65 汇编语言除法溢出问题