admin管理员组

文章数量:1123197

I have a table:

t  value 
---------
A    MT 
A    RX
B    SD
B    RX
A    RX 
C    SD

I want to select common values for t=A with other t but without duplicates:

Expected output:

t  value 
---------
A    RX
B    RX

I have a table:

t  value 
---------
A    MT 
A    RX
B    SD
B    RX
A    RX 
C    SD

I want to select common values for t=A with other t but without duplicates:

Expected output:

t  value 
---------
A    RX
B    RX
  • kdb+
Share Improve this question edited 7 hours ago Cathal O'Neill 3,1791 gold badge7 silver badges20 bronze badges asked 8 hours ago ShadowpulseShadowpulse 34 bronze badges 0
Add a comment  | 

1 Answer 1

Reset to default 2

This is a long winded solution so let me break it down

q)distinct select from tab where val in exec val from select from tab where t<>`A,val in exec val from tab where t=`A
t val
-----
A RX
B RX

In the first part we exec values where t=`A

q)exec val from tab where t=`A
`MT`RX`RX

We use this list in the next part of our query which selects from our table where values are in this list, and where t<>`A

q)select from tab where t<>`A,val in exec val from tab where t=`A
t val
-----
B RX

We use the values from our previous query to select from our table

q)select from tab where val in exec val from select from tab where t<>`A,val in exec val from tab where t=`A
t val
-----
A RX
B RX
A RX

Finally, use distinct to remove duplicates

q)distinct select from tab where val in exec val from select from tab where t<>`A,val in exec val from tab where t=`A
t val
-----
A RX
B RX

Note: Since value is a q key word, it’s advised not to use it as a column name in a table.

本文标签： kdbkdbself join to select duplicate with a conditionStack Overflow

版权声明：本文标题：kdb+ - kdb : self join to select duplicate with a condition - Stack Overflow 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://www.betaflare.com/web/1736554749a1944560.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。