条件DB2 SQL查询 [英] Conditional DB2 SQL query
问题描述
另外还有一个名为CompanyAddress的相关表,其中包含CompanyID外键,因此可以轻松建立连接。 p>
这个CompanyAddress表可能会有一个给定公司的多个地址,如AddressType = 1,或AddressType = 2
加入等等来获取字段是微不足道的,但是我想要一个有条件的地址,我在哪里查询地址,如果它在那里使用AddressType = 1,如果不是,请使用AddressType = 2
目前,我正在考虑做一个工会并删除重复,但必须有一个更好的方法。
执行此操作(如果使用DB2 for Linux / UNIX / Windows)通过使用OLAP函数实际上是微不足道的。我猜到companyAddress表中的某些列名,但magic在rank()函数中:
with preferredAddresses as(
select
companyID,
address,
addresstype,
rank()over(partition by companyID order by addresstype)as rank
从
companyAddress
)
从
选择*
公司C,
内部连接preferredAddresses A
在c.companyID = A.companyID
其中
A.rank = 1;
Lets Say I have a table called "Company", with a key of CompanyID There is another related table called "CompanyAddress", that has the CompanyID foreign key, so a join could be easily established.
This CompanyAddress table could have multiple addresses for a given company, say AddressType = 1, or AddressType = 2
The join etc to get the fields is trivial, however I want a conditional, where I query for addresses, and use AddressType = 1 if it is there, if it is not, use AddressType = 2
Currently, I am thinking of doing a union and removing duplicates but there has to be a better way
It is actually pretty trivial to do this (if you are using DB2 for Linux/UNIX/Windows) by using OLAP functions. I've guessed at some of the column names in the companyAddress table, but the "magic" is in the rank() function:
with preferredAddresses as (
select
companyID,
address,
addresstype,
rank() over (partition by companyID order by addresstype ) as rank
from
companyAddress
)
select *
from
company C,
inner join preferredAddresses A
on c.companyID = A.companyID
where
A.rank = 1;
这篇关于条件DB2 SQL查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!