Group_CONCAT CASE multiply outputs

huangapple go评论76阅读模式
英文:

Group_CONCAT CASE multiply outputs

问题

GROUP_CONCAT(CASE WHEN t3.ship=1 AND t4.item=0 THEN CONCAT(t2.item_no, '. ', t2.item_name) END ORDER BY item_id SEPARATOR '<br>') `My Item List`

期望输出:1. Lamp


<details>
<summary>英文:</summary>

I have an SQL query with this line:

GROUP_CONCAT(CASE WHEN t3.ship=1 AND t4.item=0 THEN t2.item_name END ORDER BY item_id SEPARATOR '<br>') My Item List

Output Now: Lamp

It works perfectly, however I want to list out the item&#39;s number which is stored in item_no column.
Expected Output: 1. Lamp

I tried to add something like this, but didnt work:

GROUP_CONCAT(CASE WHEN t3.ship=1 AND t4.item=0 THEN t2.item_no, '.' ,t2.item_name END ORDER BY item_id SEPARATOR '<br>') My Item List


How can I achive this?

</details>


# 答案1
**得分**: 0

你必须使用 [`CONCAT()`][1] 连接 `item_no`、`&#39;.&#39;` 和 `item_name`:

    ... THEN CONCAT(t2.item_no, '&#39;.', t2.item_name) END ... 

  [1]: https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_concat

<details>
<summary>英文:</summary>

You must concatenate `item_no`, `&#39;.&#39;` and `item_name` with [`CONCAT()`][1]:

    ... THEN CONCAT(t2.item_no, &#39;.&#39;, t2.item_name) END ... 


  [1]: https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_concat

</details>



huangapple
  • 本文由 发表于 2023年7月17日 21:12:27
  • 转载请务必保留本文链接:https://go.coder-hub.com/76704830.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定