Oracle9iのSQLPlusにて、日ごとのIDごとの小計をとりたいだけなのですが、
グループ関数の使い方がよくわかっていないのか上手くいきません。
以下のように出力したいと思い、以下のようなSQLを作成しましたが、、、、
<最終形>
入金日 ID 名前 請求開始日 請求終了日 合計金額
20080901 252 あああ 20080701 20080731 17000
20080901 252 空白 空白 空白 17000 ← ID=252の小計
20080901 534 いいい 20080601 20080630 18000
20080901 534 空白 空白 空白 18000 ← ID=534の小計
20080901 空白 空白 空白 空白 35000 ← 20080901の小計
<SQL文>
select 入金日,ID,名前,請求開始日,請求終了日,sum(入金額)
~
group by
rollup(入金日,ID,名前,請求開始日,請求終了日)
<現状>
入金日 ID 名前 請求開始日 請求終了日 合計金額
20080901 252 あああ 20080701 20080731 17000
20080901 252 あああ 20080701 空白 17000 ← 不要
20080901 252 あああ 空白 空白 17000 ← 不要
20080901 252 空白 空白 空白 17000
20080901 534 いいい 20080601 20080630 18000
20080901 534 いいい 20080601 空白 18000 ← 不要
20080901 534 いいい 空白 空白 18000 ← 不要
20080901 空白 空白 空白 空白 35000
上記、SQL文ではrollupにて全項目を指定しているから当然なのかもしれませんが、
試しに、小計対象外を外に出して「group by 名前,請求開始日,請求終了日,rollup(入金日,ID)」としてみたりしましたが上手くいきませんでした。
要はグルーピング対象外および小計対象外の項目の扱い方がよくわかっていないのかと思います。
どなたかご存知の方がいらっしゃれば是非ご教示いただけませんでしょうか?
宜しくお願いします。
No.5ベストアンサー
- 回答日時:
サブクエリとは、基本的にSELECTした結果をテーブルと見立てて使うことです(Viewのようなイメージです)。
したがって、サブクエリ自体は完結したものであって(*)、それを使うメインクエリはサブクエリを構成している個別のテーブルには関知しません。
つまり、メインクエリは以下のようなレイアウトのテーブルに対してSELECTを実行しているのと等しいです。
(項目名はサブクエリだけを抜き出して実行したときの戻りの項目名です)
TK14_NYUUKIN_DATE,
TK14_KANJYA_NO,
TK01_NAME,
TK13_SEIKYUU_START,
TK13_SEIKYUU_END,
SUMAMT
これらの値を生み出しているサブクエリ内のテーブルとは関係がなくなっています。それがBなどのエイリアスを指定できない理由です。
そう考えていただくと、テーブルの項目名にSUM(xxxx)などという名前は使えませんよね。それがSUMAMTという別名を付けている理由です。
(*) 別に相関サブクエリというものがあり、それはこの説明には当てはまらないかもしれませんが、これは必要になったときに勉強していただければと思います。
No.4
- 回答日時:
なるほど、このクエリならそうなるでしょうね。
「最初のSELECTの見出しを元にORDERを掛ける」というのは、「UNIONした場合は、SELECTの要素である項目に対してではなく、実行された結果に対してしかOrderが切れない」という意味だったのですが、今回のクエリは文字列結合してしまっているので、戻り列は1つですね。
したがって、ORDER BY 1,2はエラーになります。
そのため、今回のケースで、文字列結合する時点で項目別の並び順を指定できるようにするためには、サブクエリにする必要があると思います。
以下の通りです。
(OracleはOrderをかけるときにNULLの方が大きいとみなされるはずですから、これでいけると思います)
SELECT
TK14_NYUUKIN_DATE || ',' ||
TK14_KANJYA_NO || ',' ||
TK01_NAME || ',' ||
TK13_SEIKYUU_START || ',' ||
TK13_SEIKYUU_END || ',' ||
SUMAMT
FROM
(SELECT
B.TK14_NYUUKIN_DATE,
B.TK14_KANJYA_NO,
C.TK01_NAME,
A.TK13_SEIKYUU_START,
A.TK13_SEIKYUU_END,
SUM(B.TK14_NYUUKIN_GAKU) SUMAMT
FROM TK13_SEIKYUUSYO A
INNER JOIN TK14_NYUUKIN B ON TK13_KANJYA_NO=TK14_KANJYA_NO AND TK13_SEIKYUU_DATE = TK14_SEIKYUU_DATE
INNER JOIN TK01_KANJYA C ON A.TK13_KANJYA_NO = C.TK01_KANJYA_NO
WHERE B.TK14_NYUUKIN_DATE >= &1 AND B.TK14_NYUUKIN_DATE <= &2 AND B.TK14_NYUUKIN_GAKU > 0
GROUP BY B.TK14_NYUUKIN_DATE,B.TK14_KANJYA_NO,C.TK01_NAME,A.TK13_SEIKYUU_START,A.TK13_SEIKYUU_END
UNION
SELECT
B.TK14_NYUUKIN_DATE,
B.TK14_KANJYA_NO,
NULL,NULL,NULL,
SUM(B.TK14_NYUUKIN_GAKU)
FROM TK13_SEIKYUUSYO A
INNER JOIN TK14_NYUUKIN B ON TK13_KANJYA_NO = TK14_KANJYA_NO AND TK13_SEIKYUU_DATE = TK14_SEIKYUU_DATE
INNER JOIN TK01_KANJYA C ON A.TK13_KANJYA_NO = C.TK01_KANJYA_NO
WHERE B.TK14_NYUUKIN_DATE >= &1 AND B.TK14_NYUUKIN_DATE <= &2 AND B.TK14_NYUUKIN_GAKU > 0
GROUP BY ROLLUP(B.TK14_NYUUKIN_DATE,B.TK14_KANJYA_NO)
)
ORDER BY TK14_NYUUKIN_DATE,TK14_KANJYA_NO
この回答への補足
いろいろと勉強になります。。
まず、メインクエリにて、TK14_NYUUKIN_DATE等の項目に「B.」とテーブル名を付けると、ORA-0904が発生しますね。。このテーブルの別名はサブクエリだけに有効となるのでしょうか?そもそも別名は不要ですか?
また、メインクエリのSUMAMTとは単なる別名でしょうか?でもメインクエリに SUM(TK14_NYUUKIN_GAKU)と書いてもやはりORA-0904が発生します。
これはなぜなんでしょうか??
奥深いですね。。。
No.3
- 回答日時:
ORA-01785は戻りの列数が1つしかないのにORDER BY 2と切った場合などに出るエラーですが、当方では再現しません(11gですが)。
エラーの出るクエリを示していただけませんか。
この回答への補足
お手数おかけし申し訳ありません。
以下クエリです。
select
B.TK14_NYUUKIN_DATE || ',' ||
B.TK14_KANJYA_NO || ',' ||
C.TK01_NAME || ',' ||
A.TK13_SEIKYUU_START || ',' ||
A.TK13_SEIKYUU_END || ',' ||
SUM(B.TK14_NYUUKIN_GAKU)
from
TK13_SEIKYUUSYO A
inner join
TK14_NYUUKIN B
on
TK13_KANJYA_NO = TK14_KANJYA_NO
and TK13_SEIKYUU_DATE = TK14_SEIKYUU_DATE
inner join
TK01_KANJYA C
on
A.TK13_KANJYA_NO = C.TK01_KANJYA_NO
where
B.TK14_NYUUKIN_DATE >= &1
and B.TK14_NYUUKIN_DATE <= &2
and B.TK14_NYUUKIN_GAKU > 0
group by
B.TK14_NYUUKIN_DATE,
B.TK14_KANJYA_NO,
C.TK01_NAME,
A.TK13_SEIKYUU_START,
A.TK13_SEIKYUU_END
union
select
B.TK14_NYUUKIN_DATE || ',' ||
B.TK14_KANJYA_NO || ',' ||
null || ',' ||
null || ',' ||
null || ',' ||
SUM(B.TK14_NYUUKIN_GAKU)
from
TK13_SEIKYUUSYO A
inner join
TK14_NYUUKIN B
on
TK13_KANJYA_NO = TK14_KANJYA_NO
and TK13_SEIKYUU_DATE = TK14_SEIKYUU_DATE
inner join
TK01_KANJYA C
on
A.TK13_KANJYA_NO = C.TK01_KANJYA_NO
where
B.TK14_NYUUKIN_DATE >= &1
and B.TK14_NYUUKIN_DATE <= &2
and B.TK14_NYUUKIN_GAKU > 0
group by
rollup(B.TK14_NYUUKIN_DATE,
B.TK14_KANJYA_NO)
order by
1,2;
先ほどのエラーが発生します。
ちなみにorder byを外すと、小計が先頭に出力されてしまいます。
宜しくお願いします。
No.2
- 回答日時:
unionの場合は、unionで結合した最初のSELECTの見出しを元にORDERを掛けることになります。
したがって、最初のSELECTに使われている見出しが入金日、IDではなくなっているのではないでしょうか?
もしくは、私が書いたように「ORDER BY 1,2」と戻り値の列番号を指定する方法もあります。
この回答への補足
いずれのSELECTも見出しは同一です。
また、「ORDER BY 1,2」の場合、「ORA-01785:ORDER BY 項目は、SELECTリスト式の数値である必要あります。」エラーが発生してしまいます。
尚、「ORDER BY 1」は上手くいきますが・・・???
どうなってるのでしょうか?
No.1
- 回答日時:
ROLLUPはGROUP BYの対象に対して機械的に小計を算出してしまうものですので、目的の結果はシンプルには得られないと思います。
したがって、以下のいずれかの方法になるかと
・不要な小計を拾わない
SELECT 入金日,ID,名前,請求開始日,請求終了日,SUM(金額)
FROM テーブル
GROUP BY ROLLUP(入金日,ID,名前,請求開始日,請求終了日)
HAVING
(CASE WHEN 名前 IS NULL THEN 1 ELSE 0 END)
+(CASE WHEN 請求開始日 IS NULL THEN 1 ELSE 0 END)
+(CASE WHEN 請求終了日 IS NULL THEN 1 ELSE 0 END) NOT IN (1,2)
・小計は別につける
SELECT 入金日,ID,名前,請求開始日,請求終了日,SUM(金額)
FROM テーブル
GROUP BY 入金日,ID,名前,請求開始日,請求終了日
UNION
SELECT 入金日,ID,NULL,NULL,NULL,SUM(金額)
FROM テーブル
GROUP BY ROLLUP(入金日,ID)
ORDER BY 1,2
この回答への補足
amshid6さんいつもありがとうございます!
パーフェクトなご回答ありがとうございます。
union使えないかなぁ~と思っていましたので早速試してみました。
・・が、union後のselectにて、order by 入金日,IDでなぜかエラー(ORA-0904:無効な識別子です)が出てしまいます。
試しにunion後のselectのみを実行すればエラーは出ません。
unionにはなにか特殊な制限でもあるのでしょうか??
ご存知でしたら教えていただけないでしょうか?
宜しくお願いします。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) エクセルの表でダブりを解消する方法を、教えてください。 5 2023/04/12 12:11
- Access(アクセス) アクセス IDが空白の時、メッセージボックスがでるようにするには 1 2022/08/04 11:06
- Excel(エクセル) capeofdragonと申します Excel2016を使っておりまして 半角又は全角の任意文字列が 2 2022/10/31 13:51
- Excel(エクセル) 列を自動で追加したい 3 2022/07/11 12:58
- JavaScript javascriptで入力フォームが空欄の時にアラートによるエラーを出すコードを書いています。 2 2023/06/13 17:58
- Excel(エクセル) グループの最後の行に書式、計算式なども同じ行を追加するマクロを教えてもらえませんか。 7 2022/05/18 10:13
- Access(アクセス) アクセス テーブルの空白を変数に置換するボタンが作りたい 4 2022/07/08 11:19
- カードローン・キャッシング 主人の事ですが信用情報機構jiccで開示してみたらアコム、アイフル2件ありました。 契約状態→契約終 3 2022/08/04 16:56
- Excel(エクセル) 特定文字(数字)で行挿入、挿入された行で合計したい 2 2023/03/13 14:30
- Excel(エクセル) excel 表計算 加算するセルが空白の時累計を表示しない又は塗りつぶして表示しないようにしたい 4 2023/07/02 11:50
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
SQLPlusのグループ関数と小計に...
-
お土産というよりお使いなので...
-
通販の支払い遅延に対する法的処置
-
助けてください! 友だちと興味...
-
違法なアダルトサイトを閲覧し...
-
子供のいたずらの賠償責任について
-
家具搬入の際、柱にキズをつけ...
-
誤作動登録をしてしまい退会申...
-
解約
-
支給お願いします。 抜ける無料...
-
自転車で信号無視して車と接触事故
-
事故られたのですが
-
お世話になった人と縁を切りた...
-
一時停止不履行の車と衝突しそ...
-
走行中のトラックからの落石に...
-
有料エロサイト
-
月極駐車場を借りるのですが、...
-
駐車場で接触事故を起こしまし...
-
クーリングオフについて。 先日...
-
物損事故。示談最中での車両売却
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
水商売でよくある、遅刻欠勤の...
-
2年前の料金請求
-
ラインギフトの支払い方法 につ...
-
お土産というよりお使いなので...
-
倒産した会社のその後
-
公的機関への日付なし請求書
-
別れた彼にお金を返して欲しい
-
SQLPlusのグループ関数と小計に...
-
振替払込受付証明書をもらえな...
-
1年半経ってからの結婚式費用の...
-
ゴルフ代金の諸経費って
-
派遣会社に登録して職場見学も...
-
通販の支払い遅延に対する法的処置
-
スカパー!のPPVの支払について
-
SoftBank の延滞利息金の請求月...
-
ヤフーショッピングに出店して...
-
自宅補修をしてもらった業者が...
-
ネット翻訳会社の会計処理
-
請求書つきメールの書き方
-
質問です!私は部活動で会計係...
おすすめ情報