提问人：nodev_101 提问时间：11/16/2023 最后编辑：MT0nodev_101 更新时间：11/18/2023 访问量：23
需要对映射到多个fund_id的单个account_id进行 Oracle 查询
Need a Oracle Query for Single account_id Mapped With Multiple fund_id
问：
我需要处理如下数据集：
account_id Fund_id
ASC23454 765432
ASC67654 988776
ASC23454 899098
ASC23454 765787
ASC90878 899877
ASC90878 788978
ASC98765 889909
ASC09876 908990
因此，我需要在 oracle 中编写一个查询，该查询将仅显示具有多个不同 .account_idfund_id
预期输出：
account_id Fund_id
ASC23454 765432
ASC23454 899098
ASC23454 765787
ASC90878 899877
ASC90878 788978
任何人都可以帮我查询上述输出。
答：
2赞
MT0
11/16/2023
#1
使用分析函数计算每个 ：COUNTDISTINCTfund_idaccount_id
SELECT account_id,
fund_id
FROM (
SELECT account_id,
fund_id,
COUNT(DISTINCT fund_id) OVER (PARTITION BY account_id) AS num_funds
FROM table_name
)
WHERE num_funds > 1;
其中，对于示例数据：
CREATE TABLE table_name (account_id, Fund_id) AS
SELECT 'ASC23454', 765432 FROM DUAL UNION ALL
SELECT 'ASC67654', 988776 FROM DUAL UNION ALL
SELECT 'ASC23454', 899098 FROM DUAL UNION ALL
SELECT 'ASC23454', 765787 FROM DUAL UNION ALL
SELECT 'ASC90878', 899877 FROM DUAL UNION ALL
SELECT 'ASC90878', 788978 FROM DUAL UNION ALL
SELECT 'ASC98765', 889909 FROM DUAL UNION ALL
SELECT 'ASC09876', 908990 FROM DUAL;
输出：
| ACCOUNT_ID | FUND_ID |
|---|---|
| ASC23454 | 765432 |
| ASC23454 | 765787 |
| ASC23454 | 899098 |
| ASC90878 | 788978 |
| ASC90878 | 899877 |
0赞
d r
11/17/2023
#2
一种选择是使用 subquery grouped by with 子句来过滤行：EXISTSACCOUNT_IDHAVING
WITH -- S a m p l e S Q L :
tbl AS
( Select 'ASC23454' "ACCOUNT_ID", 765432 "FUND_ID" From Dual Union All
Select 'ASC67654', 988776 From Dual Union All
Select 'ASC23454', 899098 From Dual Union All
Select 'ASC23454', 765787 From Dual Union All
Select 'ASC90878', 899877 From Dual Union All
Select 'ASC90878', 788978 From Dual Union All
Select 'ASC98765', 889909 From Dual Union All
Select 'ASC09876', 908990 From Dual
)
-- M a i n S Q L :
Select t.ACCOUNT_ID, t.FUND_ID
From tbl t
Where EXISTS(Select 1
From tbl
Where ACCOUNT_ID = t.ACCOUNT_ID
Group By ACCOUNT_ID Having Count(Distinct FUND_ID) > 1)
/* R e s u l t :
ACCOUNT_ID FUND_ID
---------- ----------
ASC90878 788978
ASC90878 899877
ASC23454 765787
ASC23454 899098
ASC23454 765432 */
评论