無效的請求“分組依據”（oracle）

請告訴我如何修復錯誤。一個錯誤Expression not in GROUP BY key 'isin'。

我知道我的分組操作不正確，但我不知道如何正確重做此請求的代碼。在這里，您需要找到key的最大值end_circ和最小值。有必要與和一起顯示所有列。begin_circstocks_full_idselectmaxmin

SELECT a.isin as id,
       a.state_number as number,
       a.update_time as valid_from_date,
       '2999-12-31 00:00:00' as valid_to_date,
       a.operdate as oper,
       a.inn as inn_num,
       a.name_eng as name,
       coalesce(ts.full_name_eng,a.name_eng) as full_nm,
       max (stg.end_circ) as end_date,
       min (stg.begin_circ) as start_date,
       case when sk.name_eng IS NULL then sk.name_uk else sk.name_eng end as subtype_nm
FROM (SELECT s.*, rank() over (PARTITION BY isin,state_number ORDER BY operdate desc) as rn
      FROM stocks s 
      WHERE isin IS NOT NULL and state_number IS NOT NULL) a
JOIN trading_stocks ts ON ts.emission_is=a.id
JOIN stocks_trading_grounds stg ON stg.stocks_full_id=a.id
JOIN stocks_kinds sk ON sk.id=a.kind_id
WHERE stg.end_circ >= "2021-01-01 00:00:00" and a.rn=1
GROUP BY stg.stocks_full_id

uj5u.com熱心網友回復：

GROUP BY對于 中的單個表JOIN：

SELECT a.isin as id,
       a.state_number as number,
       a.update_time as valid_from_date,
       '2999-12-31 00:00:00' as valid_to_date,
       a.operdate as oper,
       a.inn as inn_num,
       a.name_eng as name,
       coalesce(ts.full_name_eng,a.name_eng) as full_nm,
       stg.end_date,
       stg.start_date,
       case when sk.name_eng IS NULL then sk.name_uk else sk.name_eng end as subtype_nm
FROM   (SELECT s.*,
               rank() over (PARTITION BY isin,state_number ORDER BY operdate desc) as rn
        FROM stocks s 
        WHERE isin IS NOT NULL
        and state_number IS NOT NULL
       ) a
       JOIN trading_stocks ts ON ts.emission_is=a.id
       JOIN stocks_kinds sk ON sk.id=a.kind_id
       JOIN (
         SELECT stocks_full_id,
                max(stg.end_circ) as end_date,
                min(stg.begin_circ) as start_date
         FROM   stocks_trading_grounds
         GROUP BY stocks_full_id
       ) stg
       ON stg.stocks_full_id=a.id
WHERE  stg.end_date >= DATE '2021-01-01'
AND    a.rn=1

此外，雙引號不用于字串文字；它們用于帶引號的識別符號。'2021-01-01 00:00:00'對字串文字或DATE '2021-01-01'日期文字使用單引號。

uj5u.com熱心網友回復：

您必須在 group by 子句中包含所有非聚合列。所以你更新的查詢將是 -

SELECT a.isin as id,
       a.state_number as number,
       a.update_time as valid_from_date,
       '2999-12-31 00:00:00' as valid_to_date,
       a.operdate as oper,
       a.inn as inn_num,
       a.name_eng as name,
       coalesce(ts.full_name_eng,a.name_eng) as full_nm,
       max (stg.end_circ) as end_date,
       min (stg.begin_circ) as start_date,
       case when sk.name_eng IS NULL then sk.name_uk else sk.name_eng end as subtype_nm
FROM (SELECT s.*, rank() over (PARTITION BY isin,state_number ORDER BY operdate desc) as rn
      FROM stocks s 
      WHERE isin IS NOT NULL and state_number IS NOT NULL) a
JOIN trading_stocks ts ON ts.emission_is=a.id
JOIN stocks_trading_grounds stg ON stg.stocks_full_id=a.id
JOIN stocks_kinds sk ON sk.id=a.kind_id
WHERE stg.end_circ >= '2021-01-01 00:00:00' and a.rn=1
GROUP BY a.isin,
         a.state_number,
         a.update_time,
         a.operdate,
         a.inn,
         a.name_eng,
         coalesce(ts.full_name_eng,a.name_eng),
         case when sk.name_eng IS NULL then sk.name_uk else sk.name_eng end;

標籤：sql 甲骨文 通过...分组

上一篇：在SQLQuery上使用

下一篇：REGEXP_REPLACE字串以雪花中的特定子字串開頭和結尾