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

问题描述

让我说我有一个名为公司的表，其中的关键字为CompanyID
另外还有一个名为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屋！