子查询中的条件 - 如果子查询返回2个记录，则选择一个值，否则为实际值

I have a subquery inside a big query which returns multiple values sometime and some time only one value. Below is my query and the returned values

我在一个大查询中有一个子查询，它有时返回多个值，有时只返回一个值。下面是我的查询和返回的值

select tran.customer_type from transaction_record tran where tran.TRANSACTION_ID=txn.id

customer_type can be 2 records - "LP" and "NA"

customer_type可以是2条记录 - “LP”和“NA”

要么

customer_type can be 2 records - "SOEMTHING ELSE" and "NA"

customer_type可以是2条记录 - “SOEMTHING ELSE”和“NA”

要么

customer_type can be 1 records - "NA"

customer_type可以是1条记录 - “NA”

Here my probem is if i have 2 records i have to print value without NA and if i have one record i have to print what ever be the value is

在这里我的探测器是如果我有2条记录我必须打印没有NA的值，如果我有一条记录我必须打印什么是值的是

1 个解决方案

#1

Not exectly efficient (2 queries), but it should work!

效率不高（2个查询），但应该有效！

Inner query counts status, id combinatios per group and outer query removes all NA statuses that have another record on same ID.

内部查询计数状态，每组ID组合和外部查询删除在同一ID上具有另一记录的所有NA状态。

Innermost query is just for table simulation (I like it more than create table, insert scripts).

最内层的查询仅适用于表模拟（我更喜欢创建表，插入脚本）。

SELECT * FROM 
        (
                SELECT status, id, count(*)
                OVER (PARTITION BY id ORDER BY 3 ) AS rn
                from (
                        SELECT 'NA' status, 1 id FROM dual
                        UNION ALL
                        SELECT 'LP' status, 1 id FROM dual
                        UNION ALL
                        SELECT 'NA' status, 2 id FROM dual
                        UNION ALL
                        SELECT 'SOEMTHING ELSE' status, 2 id FROM dual
                        UNION ALL
                        SELECT 'NA' status, 3 id FROM dual
                        UNION ALL
                        SELECT 'NA' status, 5 id FROM dual
                        UNION ALL
                        SELECT 'LP' status, 5 id FROM dual
                        UNION ALL
                        SELECT 'NA' status, 6 id FROM dual
                        UNION ALL
                        SELECT 'SOEMTHING ELSE' status, 6 id FROM dual
                        UNION ALL
                        SELECT 'NA' status, 22 id FROM dual
                    ))
    WHERE NOT (status = 'NA' AND rn=2)

#1