postgresql make output column upper while applying DISTINCT ON

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

postgresql make output column upper while applying DISTINCT ON

问题

  1. 我试图在一个列上找到不同的值，并且我想将输出转换为大写
  1. select
  2.    DISTINCT ON (UPPER(approver_wwid)) approver_wwid ,
  3.    approver_name
  4. from cons_temp
  1. 我使用DISTINCT ON是因为名称是模糊的，而wwid可以是不同的。
  3. 我只想输出approver_wwid的大写值。
  5. ## 示例数据 ##
  6. <pre>
  7. approver_wwid | approver_name
  8. ab123         | Jhon Mark
  9. AB123         | Jhon Mark
  10. ab123         | Mark Jhon
  11. CD123         | Dev Tim
  12. cd123         | Dev Tim
  13. </pre>
  15. ## 预期输出 ##
  16. <pre>
  17. approver_
  19. <details>
  20. <summary>英文:</summary>
  22. I&#39;m trying to find distinct on one column and I want to make output to Upper

select
DISTINCT ON (UPPER(approver_wwid)) approver_wwid ,
approver_name
from cons_temp

  1. I&#39;m using Distinct on because name is ambiguous while wwid can be distinct.
  3. I just want to output value of approver_wwid in upper.
  5. ## Sample Data ##
  6. &lt;pre&gt;
  7. approver_wwid | approver_name
  8. ab123         | Jhon Mark
  9. AB123         | Jhon Mark
  10. ab123         | Mark Jhon
  11. CD123         | Dev Tim
  12. cd123         | Dev Tim
  13. &lt;/pre&gt;
  15. ## Expected Output ##
  16. &lt;pre&gt;
  17. approver_wwid | approver_name
  18. AB123         | Jhon Mark
  19. CD123         | Dev Tim
  20. &lt;/pre&gt;
  23. </details>
  26. # 答案1
  27. **得分**: 0
  29. 这将使您的第一列变成大写：
  31. ```sql
  32. SELECT
  33.    DISTINCT ON (approver_wwid) UPPER(approver_wwid) AS approver_wwid,
  34.    approver_name
  35. FROM cons_temp

但请注意，返回的行不在您的控制之下。在您的情况下，第二列可能是 'Mark Jhon' 或 'Jhon Mark'。

英文:

This will make your first column upper:

  1. SELECT
  2.    DISTINCT ON (approver_wwid) UPPER(approver_wwid) AS approver_wwid,
  3.    approver_name
  4. FROM cons_temp

But note, that it is not under your control which row will be returned.
In your case it could be 'Mark Jhon' or 'Jhon Mark' for the second column.

huangapple
  • 本文由 发表于 2020年1月3日 17:47:36
  • 转载请务必保留本文链接：https://go.coder-hub.com/59576291.html
  • postgresql
匿名

发表评论

匿名网友

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

确定