query为单个计数返回多行

3j86kqsm 于 2021-07-29 发布在 Java

关注(0)|答案(1)|浏览(292)

我创建了一个查询来返回每天的银行对账单行数。
出于某种原因，当有一个对账单传入时，会有两行相同的银行帐户名。一行返回0表示语句行总数，另一行返回正确的语句行数。
如果当天没有输入语句行，则查询只返回一行0作为总语句行，这是正确的。
请帮忙。

select  distinct cb.bank_name,
    cba.bank_account_name   Bank_Account_Name,
    cba.bank_account_num    Bank_Account_Number,
    le.name                 Legal_entity_Name,
    (select count(csl1.STATEMENT_LINE_ID)
    from ce_statement_lines csl1
    where to_char(csl1.booking_date,'yyyy/MM/dd') = to_char(:P_Date,'yyyy/MM/dd')
    and csl1.STATEMENT_HEADER_ID = CESL.STATEMENT_HEADER_ID
    --GROUP BY cba.bank_account_id, csl1.STATEMENT_LINE_ID
    ) total_statement_line_for_date 
    from
    ce_bank_accounts cba,
    xle_entity_profiles le,
    CE_STATEMENT_HEADERS CESH,
    ce_banks_v cb,
    ce_statement_lines CESL
    WHERE cba.account_owner_org_id  = le.legal_entity_id
    and cb.bank_party_id            = cba.bank_id
    and cba.bank_account_id = CESH.BANK_ACCOUNT_ID
    and CESL.STATEMENT_HEADER_ID = CESH.STATEMENT_HEADER_ID
   -- GROUP BY cb.bank_name, cba.bank_account_name, cba.bank_account_num, le.name, cba.bank_account_id
    order by cba.bank_account_name

|----------------|------------------|------------------ |-------|------|
|    Bank Name   |Bank Account Name |Bank Account Number|LE Name| Count|
|----------------|------------------|-------------------|-------|------|
|     A Name     |  A Account Name  |  A Account Number | A LE  |   0  |
|----------------|------------------|-------------------|-------|------|
|     B Name     |  B Account Name  |  B Account Number | B LE  |   0  |
|----------------|------------------|-------------------|-------|------|
|     B Name     |  B Account Name  |  B Account Number | B LE  |  17  |
|----------------|------------------|-------------------|-------|------|
|     C Name     |  C Account Name  |  C Account Number | C LE  |   0  |
|----------------|------------------|-------------------|-------|------|
|     C Name     |  C Account Name  |  C Account Number | C LE  |   1  |
|----------------|------------------|-------------------|-------|------|

sql oracle bi-publisher

来源：https://stackoverflow.com/questions/63121982/query-returns-multiple-rows-for-a-single-count

1条答案

按热度按时间

o4tp2gmn1#

我更喜欢示例数据，但是-如果您对当前查询感到满意，并且它做了您希望它做的事情，但也希望摆脱 count = 0 如果有行 count 价值观不是 0 ，那么一个简单的选项是

SQL> with test (account_num, le_name, cnt) as
  2  -- this represents result of your current query
  3    (select 'A', 'A LE',  0 from dual union all
  4     select 'B', 'B LE',  0 from dual union all
  5     select 'B', 'B LE', 17 from dual union all
  6     select 'C', 'C LE',  0 from dual union all
  7     select 'C', 'C LE',  1 from dual
  8    )
  9  -- query you might need
 10  select account_num,
 11         le_name,
 12         sum(cnt) cnt
 13  from test
 14  group by account_num,
 15           le_name
 16  order by account_num;

A LE_NAME          CNT
- --------- ----------
A A LE               0
B B LE              17
C C LE               1

SQL>

赞(0）回复(0）举报 2021-07-29

我来回答

query为单个计数返回多行

1条答案

相关问题

热门标签

最新问答