DB2 SQL在对行进行分组时获取不同的值

BUSINESSTABLE looks like this:

BUSINESSTABLE看起来像这样：

HOTEL_CHAIN    HOTEL_LOCATION   HOTEL_OWNER
_____________________________________________________
Marriott       Las Vegas        Nelson
Best Western   New York         Richards
Best Western   San Francisco    Smith
Marriott       New York         Nelson
Hilton         Boston           James

I'm trying to execute an SQL statement in a DB2 database that groups these entries by HOTEL_CHAIN. If the rows that are grouped together contain the same HOTEL_LOCATION or HOTEL_OWNER, that info should be preserved. Otherwise, a value of 'NULL' should be displayed. For example, both Marriott hotels have the same owner, Nelson, so I want to display that information in the new table. However, each Marriott hotel is in a different location, so I'd like to display 'NULL' in that column.

我正在尝试在DB2数据库中执行一个SQL语句，该数据库按HOTEL_CHAIN对这些条目进行分组。如果组合在一起的行包含相同的HOTEL_LOCATION或HOTEL_OWNER，则应保留该信息。否则，应显示值“NULL”。例如，两家万豪酒店都拥有相同的所有者尼尔森，所以我想在新表中显示这些信息。但是，每家万豪酒店都位于不同的位置，所以我想在该栏中显示“空”。

The resulting table (HOTELTABLE) should look like this:

结果表（HOTELTABLE）应如下所示：

HOTEL_CHAIN    HOTEL_LOCATION   HOTEL_OWNER
_____________________________________________________
Marriott       NULL             Nelson
Best Western   NULL             NULL
Hilton         Boston           James

I'm trying to use the following SQL statement to accomplish this:

我正在尝试使用以下SQL语句来完成此任务：

INSERT INTO HOTELTABLE(HOTEL_CHAIN,HOTEL_LOCATION,HOTEL_OWNER) 
SELECT
HOTEL_CHAIN,
CASE COUNT(DISTINCT(HOTEL_LOCATION)) WHEN 1 THEN HOTEL_LOCATION ELSE 'NULL' END,
CASE COUNT(DISTINCT(HOTEL_OWNER)) WHEN 1 THEN HOTEL_OWNER ELSE 'NULL' END,
FROM BUSINESSTABLE GROUP BY HOTEL_CHAIN

I get an SQL error SQLCODE-119 A COLUMN OR EXPRESSION IN A HAVING CLAUSE IS NOT VALID. It seems to be complaining about the 2nd HOTEL_LOCATION and the 2nd HOTEL_OWNER within my case statements. I also tried using DISTINCT(HOTEL_LOCATION) and that threw another error. Can someone please explain the correct way to code this? Thank you!

我得到一个SQL错误SQLCODE-119一个具有条款的列或表达式无效。它似乎在我的案例陈述中抱怨第二个HOTEL_LOCATION和第二个HOTEL_OWNER。我也尝试使用DISTINCT（HOTEL_LOCATION）并抛出另一个错误。有人可以解释一下编码的正确方法吗？谢谢！

2 个解决方案

#1

Don't use COUNT(DISTINCT). Use MIN() and MAX():

不要使用COUNT（DISTINCT）。使用MIN（）和MAX（）：

INSERT INTO HOTELTABLE(HOTEL_CHAIN,HOTEL_LOCATION,HOTEL_OWNER) 
    SELECT HOTEL_CHAIN,
           (CASE WHEN MIN(HOTEL_LOCATION) = MAX(HOTEL_LOCATION)
                 THEN MIN(HOTEL_LOCATION) ELSE 'NULL'
            END),
           (CASE WHEN MIN(HOTEL_OWNER) = MAX(HOTEL_OWNER)
                 THEN MIN(HOTEL_OWNER) ELSE 'NULL'
            END)
    FROM BUSINESSTABLE
    GROUP BY HOTEL_CHAIN;

Notes:

笔记：

Why not COUNT(DISTINCT)? It is generally much more expensive than MIN() and MAX() because it needs to maintain internal lists of all values.
为什么不COUNT（DISTINCT）？它通常比MIN（）和MAX（）贵得多，因为它需要维护所有值的内部列表。
I don't approve of a string value called 'NULL'. Seems like it is designed to foster confusion. Perhaps just NULL the value itself?
我不赞成名为'NULL'的字符串值。似乎它旨在助长混乱。也许只是NULL值本身？

#2

I agree Gordon for the null (gj Gordon).

我同意戈登的无效（gj Gordon）。

other method

其他方法

INSERT INTO HOTELTABLE(HOTEL_CHAIN,HOTEL_LOCATION,HOTEL_OWNER)

select distinct f1.HOTEL_CHAIN, 
       case when f2.HasDiffLocation is not null then 'NULL' else f1.HOTEL_LOCATION end as HOTEL_LOCATION,
       case when f3.HasDiffOwner is not null then 'NULL' else f1.HOTEL_OWNER end as HOTEL_OWNER
from BUSINESSTABLE f1
left outer join lateral 
(
    select 1 HasDiffLocation from BUSINESSTABLE f2b
    where f1.HOTEL_CHAIN=f2b.HOTEL_CHAIN and f1.HOTEL_LOCATION<>f2b.HOTEL_LOCATION
    fetch first rows only
) f2 on 1=1

left outer join lateral 
(
    select 1 HasDiffOwner from BUSINESSTABLE f3b
    where f1.HOTEL_CHAIN=f3b.HOTEL_CHAIN and f1.HOTEL_OWNER<>f3b.HOTEL_OWNER
    fetch first rows only
) f3 on 1=1

or like this :

或者像这样：

INSERT INTO HOTELTABLE(HOTEL_CHAIN,HOTEL_LOCATION,HOTEL_OWNER)
select distinct f1.HOTEL_CHAIN, 
ifnull(f2.result, f1.HOTEL_LOCATION) as HOTEL_LOCATION,
ifnull(f3.result, f1.HOTEL_OWNER) as HOTEL_LOCATION,
from BUSINESSTABLE f1
left outer join lateral 
(
    select 'NULL' result from BUSINESSTABLE f2b
    where f1.HOTEL_CHAIN=f2b.HOTEL_CHAIN and f1.HOTEL_LOCATION<>f2b.HOTEL_LOCATION
    fetch first rows only
) f2 on 1=1

left outer join lateral 
(
    select 'NULL' result from BUSINESSTABLE f3b
    where f1.HOTEL_CHAIN=f3b.HOTEL_CHAIN and f1.HOTEL_OWNER<>f3b.HOTEL_OWNER
    fetch first rows only
) f3 on 1=1

#1