条件 DB2 SQL 查询 [英] Conditional DB2 SQL query

问题描述

假设我有一个名为Company"的表，键为 CompanyID还有另一个名为CompanyAddress"的相关表，它具有 CompanyID 外键，因此可以轻松建立连接.

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.

这个 CompanyAddress 表可以有给定公司的多个地址，比如 AddressType = 1 或 AddressType = 2

This CompanyAddress table could have multiple addresses for a given company, say AddressType = 1, or AddressType = 2

获取字段的连接等是微不足道的，但是我想要一个条件，我在其中查询地址，如果存在则使用 AddressType = 1，如果不存在，则使用 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

推荐答案

通过使用 OLAP 函数来实现这一点实际上非常简单(如果您使用的是 DB2 for Linux/UNIX/Windows).我已经猜到了 companyAddress 表中的一些列名，但魔法"在 rank() 函数中:

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屋！