问题

以下是您提供的内容的翻译：

我有两个表 - account（帐户）和 account_bal（帐户余额）。

1. 每个帐户可以与多个与之关联的订单ID相关联。
2. 每个订单在account_bal表中有与之关联的余额。

我正在使用下面的SQL查询，尝试获取所需的输出，但某种原因没有成功。如何才能获得仅具有共同余额的order_id和customer id？请帮助我获得正确的输出。谢谢。

选择balance，order_id，account from account a，account_bal b
其中a.order_id=b.order_id
按balance，order_id，account分组;

输出：

答案1

得分: 1

以下是代码的翻译部分：

"Since you want multiple rows within each group, you want to use analytic functions (and not aggregation). You also need to remove duplicates from account:

SELECT account_id,
       order_id,
       balance
FROM   (
  SELECT a.account_id,
         a.order_id,
         b.balance,
         COUNT(*) OVER (PARTITION BY a.account_id, b.balance) AS cnt
  FROM   (SELECT DISTINCT * FROM account) a
         INNER JOIN account_bal b
         ON a.order_id=b.order_id
)
WHERE  cnt > 1;
```"

Outputs:

| ACCOUNT\_ID | ORDER\_ID | BALANCE |
| ----------:|--------:|-------:|
| 923457586 | 5678 | 23 |
| 923457586 | 2340 | 23 |
| 923457587 | 3400 | 10 |
| 923457587 | 7867 | 10 |

[fiddle](https://dbfiddle.uk/zkPQJRvo)"

<details>
<summary>英文:</summary>

Since you want multiple rows within each group, you want to use analytic functions (and not aggregation). You also need to remove duplicates from `account`:

```lang-sql
SELECT account_id,
       order_id,
       balance
FROM   (
  SELECT a.account_id,
         a.order_id,
         b.balance,
         COUNT(*) OVER (PARTITION BY a.account_id, b.balance) AS cnt
  FROM   (SELECT DISTINCT * FROM account) a
         INNER JOIN account_bal b
         ON a.order_id=b.order_id
)
WHERE  cnt &gt; 1;

Which, for the sample data:

CREATE TABLE account (account_id, Order_id) AS
SELECT 923457586, 3456 FROM DUAL UNION ALL
SELECT 923457586, 5678 FROM DUAL UNION ALL
SELECT 923457586, 2340 FROM DUAL UNION ALL
SELECT 923457586, 3456 FROM DUAL UNION ALL
SELECT 923457587, 5670 FROM DUAL UNION ALL
SELECT 923457587, 4354 FROM DUAL UNION ALL
SELECT 923457587, 7867 FROM DUAL UNION ALL
SELECT 923457587, 3400 FROM DUAL;

CREATE TABLE account_bal (Order_id, balance) AS
SELECT 3456, 43 FROM DUAL UNION ALL
SELECT 5678, 23 FROM DUAL UNION ALL
SELECT 2340, 23 FROM DUAL UNION ALL
SELECT 3456, 11 FROM DUAL UNION ALL
SELECT 5670, 78 FROM DUAL UNION ALL
SELECT 4354, 90 FROM DUAL UNION ALL
SELECT 7867, 10 FROM DUAL UNION ALL
SELECT 3400, 10 FROM DUAL;

Outputs:

fiddle

答案2

得分: 0

这是另一种使用group by和having子句执行的方法：

首先，我们识别出在多个账户中共同出现的余额并排序：

select balance
from account_bal
group by balance
having count(*) > 1

然后，通过余额进行inner join以获得预期的结果：

select distinct a.*, ab.balance
from account a 
inner join account_bal ab on ab.order_id = a.order_id
inner join (
  select balance
  from account_bal
  group by balance
  having count(*) > 1
) s on s.balance = ab.balance

select balance
from account_bal
group by balance
having count(*) > 1

select distinct a.*, ab.balance
from account a 
inner join account_bal ab on ab.order_id = a.order_id
inner join (
  select balance
  from account_bal
  group by balance
  having count(*) > 1
) s on s.balance = ab.balance