中小企業の働き方改革をサポート>>

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)」としてみたりしましたが上手くいきませんでした。
要はグルーピング対象外および小計対象外の項目の扱い方がよくわかっていないのかと思います。
どなたかご存知の方がいらっしゃれば是非ご教示いただけませんでしょうか?

宜しくお願いします。

このQ&Aに関連する最新のQ&A

A 回答 (5件)

サブクエリとは、基本的にSELECTした結果をテーブルと見立てて使うことです(Viewのようなイメージです)。



したがって、サブクエリ自体は完結したものであって(*)、それを使うメインクエリはサブクエリを構成している個別のテーブルには関知しません。
つまり、メインクエリは以下のようなレイアウトのテーブルに対してSELECTを実行しているのと等しいです。
(項目名はサブクエリだけを抜き出して実行したときの戻りの項目名です)
TK14_NYUUKIN_DATE,
TK14_KANJYA_NO,
TK01_NAME,
TK13_SEIKYUU_START,
TK13_SEIKYUU_END,
SUMAMT
これらの値を生み出しているサブクエリ内のテーブルとは関係がなくなっています。それがBなどのエイリアスを指定できない理由です。

そう考えていただくと、テーブルの項目名にSUM(xxxx)などという名前は使えませんよね。それがSUMAMTという別名を付けている理由です。

(*) 別に相関サブクエリというものがあり、それはこの説明には当てはまらないかもしれませんが、これは必要になったときに勉強していただければと思います。
    • good
    • 0

なるほど、このクエリならそうなるでしょうね。


「最初の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が発生します。
これはなぜなんでしょうか??
奥深いですね。。。

補足日時:2008/10/14 00:55
    • good
    • 0

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を外すと、小計が先頭に出力されてしまいます。

宜しくお願いします。

補足日時:2008/10/13 16:08
    • good
    • 0

unionの場合は、unionで結合した最初のSELECTの見出しを元にORDERを掛けることになります。


したがって、最初のSELECTに使われている見出しが入金日、IDではなくなっているのではないでしょうか?
もしくは、私が書いたように「ORDER BY 1,2」と戻り値の列番号を指定する方法もあります。

この回答への補足

いずれのSELECTも見出しは同一です。
また、「ORDER BY 1,2」の場合、「ORA-01785:ORDER BY 項目は、SELECTリスト式の数値である必要あります。」エラーが発生してしまいます。

尚、「ORDER BY 1」は上手くいきますが・・・???
どうなってるのでしょうか?

補足日時:2008/10/13 03:36
    • good
    • 0

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にはなにか特殊な制限でもあるのでしょうか??
ご存知でしたら教えていただけないでしょうか?
宜しくお願いします。

補足日時:2008/10/12 23:05
    • good
    • 0

このQ&Aに関連する人気のQ&A

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

関連するカテゴリからQ&Aを探す

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

QORDER BY の項目を SELECT する効果

ORDER BY の項目を SELECT する項目に含めるか否かで、速度や
メモリ消費の点で何らかの差がでるでしょうか?どちらが望ましい
という点も含めてプロフェッショナルの方の回答をお待ちしています。

Aベストアンサー

長い間、標準SQL及び多くのRDBMSでは、「ORDER BYで指定する項目は、SELECTの選択リストで指定した項目でなければならない」という制限がありました。
「SELECTの選択リストで指定していない項目でも、ORDER BY指定が可能」となったのは、比較的、最近のことです。
現在でも、「ORDER BYで式を指定」した場合などで、RDBMSにより、最適なアクセス計画を生成できないといった問題があります。

記述したSQLで、性能が出せるかどうかは、使用しているRDBMS、バージョンにより、大きな違いが生じます。
ORDER BYに関しては、メモリ消費よりは、インデクスを有効活用し、ソートを抑止できるかどうかが、利用者側にはっきり見えるところです。

自分で使うRDBMS及びバージョンで、どういうアクセス計画が生成されるかという、分析は必須です。

Q3つの表の外部結合

表A、B、Cの3つがあり、Aのすべての行を出力したいと考えています。
外部結合を用いるのだとは思うのですが、3つの表に対して行う場合の
書き方がわからず困っています。
ご教授いただけないでしょうか?
select * from a,b,c
where a.商品ID =b.商品ID (+) and b.商品ID (+) =c.商品ID (+)
としてみましたが、うまくいきませんでした。

Aベストアンサー

ansi構文の趣旨からいえば、結合条件と絞り込み条件は分けて書くので・・

select *
from a
left join b on (a.商品ID =b.商品ID)
left join c on (b.商品ID =c.商品ID)
where a.年月 = 任意の値

と書くのが一般的でしょうね。

Qupdate文で改行を入れる

こんにちは。
いつもお世話になっています。

update文を使用して、データに改行を入れたいのですが、どうすればよいでしょうか。

対象列のデータ型はVARCHAR2です。

例えばSQLPLUSから、
SPL>update 「テーブル名」set 「対象列」='あ改行い改行う改行'
を投入し、そのあと
select 「対象列」 from 「テーブル名」
とすると
SQL>あ
SQL>い
SQL>う
と出てくればよいんですが、
SQL>update 「テーブル名」set 「対象列」='あ\nい\nう\n'
としても
SQL>あ\nい\nう\n
と、「\n」が文字列としてでてきてしまいます。

SPL>update 「テーブル名」set 「対象列」='あ
2い
3う'
と投入すると、期待通りになるのですが、
そうではなく一行にまとめたいのですがどうすれば良いでしょうか。

Oracle9iを使用しています。
宜しくお願いいたします。

Aベストアンサー

こんにちわyukio200263さん
以下のSQL文でどうでしょうか?

UPDATE 「テーブル名」
SET 「対象列」= 'あ' || CHR(13) || CHR(10) ||'い'

ちょっと長くなってしまいますが、一行で可能です。

QSELECTで1件のみ取得するには?

こんにちわ。
いまORACLE9iを使用している者です。

ACCESSでは
SELECT TOP 1 項目名 FROM テーブル名
ORDER BY 項目名;
で並べ替えたデータ群のうち,先頭の1件だけを
取ることができますが,
ORACLEでそのような機能(SQL)はあるでしょうか?
教えてください。
よろしくお願いします。

Aベストアンサー

order by と rownum を併用する場合は注意が必要です。

[tbl01]
cola | colb
------------
1000 | aaaa
1001 | bbbb

というデータがある場合、
select cola from tbl01 where rownum < 1 order by cola desc;
とすると、「1001」ではなく、「1000」が返されます。
これは、order by の前に rownum < 1 が適用されてしまうからです。

解決するには、
select aaa from (select cola aaa from tbl01 order by cola desc) where rownum = 1;
とすれば良いです。

Q単一グループのグループ関数ではありません。

オラクル10を使っています。

select NAMAE max(TEN) from KYOUKA WHERE OK=1 and CLASS IS ('A', 'B', 'C');

で単一グループのグループ関数ではありません。
というエラーになります。

CLASS | OK | NAMAE | TEN |
--------------------------
A 1 a01 50
A 1 a02 60
B 1 b01 10
C 1 c01 70
C 0 c02 100

a02 60
b01 10
c01 70
という結果になってほしいです。

Aベストアンサー

SQLの根本的な文法が理解できていないのかと

GROUP BY 句について調べてみましょう

QChr(13)とChr(10)の違いは?

myStr = Replace(myStr, Chr(13), "")

myStr = Replace(myStr, Chr(10), "")

で、改行を置換しているのですが、どちらかでも改行できる時や
どちらかじゃないと改行できない時があります。

そもそもChr(13)とChr(10)の違いはなんでしょう?
どちらも改行ですよね?
何が違うのでしょう?

Aベストアンサー

こんにちは。お邪魔します。

Chr(10) ・・・ ラインフィード ・・・ 略して、Lf

Chr(13) ・・・ キャリッジリターン ・・・ 略して、Cr

改行文字に何を使うのかはアプリケーションが規定するものです。

Win・Office環境では
上のふたつを組合わせた改行[CrLf]を使うことが多いと思います。
次に[Lf]、そして、[Cr]

なので、ソースによっては
  myStr = Replace(myStr, vbCrLf, "")
と、一発で置換できるケースも少なくないです。
■ソースごとに改行文字を確認しておくことから始めた方が有利になります。■

ひとまず、VBAでの改行の扱いに慣れるには
■VBAで用意された文字列定数について知っておいた方がよいと思います。■
 VBE(Visual Basicの編集画面)が開いている状態で、
 F2 キー → オブジェクトブラウザー起動
 検索小窓に、vbCrLf、とタイプして、検索ボタン
 VBA.Constantsクラスのメンバーが表示され
 その中にすべての改行文字(文字列定数)が含まれています。
 そのまま、F1 キーでヘルプを表示します。
詳しい説明はヘルプを読む方がいいでしょう。

実践的な話として、
Chr(10)やChr(13)は関数の戻り値です。
対して、
vbLfやvbCr(またはvbCrLf)は定数です。
定数で済ませられるなら、定数の方が何かと有利です。
定数の名前は略号ですから、実態をイメージし易くなっています。
定数の扱いに慣れることをお奨めします。
例)
  myStr = Replace(myStr, vbLf, "")
  myStr = Replace(myStr, vbCr, "")
  myStr = Replace(myStr, vbCrLf, "")

もしも文字列定数を先に覚えていたなら、疑問にもならなかったのかも知れませんね。
私の場合は、改行の何たるかを覚える前に文字列定数覚えていて、困ることありませんでしたから。

以上、ご参考まで。

こんにちは。お邪魔します。

Chr(10) ・・・ ラインフィード ・・・ 略して、Lf

Chr(13) ・・・ キャリッジリターン ・・・ 略して、Cr

改行文字に何を使うのかはアプリケーションが規定するものです。

Win・Office環境では
上のふたつを組合わせた改行[CrLf]を使うことが多いと思います。
次に[Lf]、そして、[Cr]

なので、ソースによっては
  myStr = Replace(myStr, vbCrLf, "")
と、一発で置換できるケースも少なくないです。
■ソースごとに改行文字を確認しておくことから始めた方が有利...続きを読む

Qsqlplusで表示が変なので、出力を整形したい。

いつもお世話になっています。

サーバにアクセスしてsqlplusで、
データを調べたいのですが、
出力形式が見づらくて困っています。

よくわからいのですが、
---------------------------
カラム名1
---------------------------
カラム名2
---------------------------
カラム名3
---------------------------
1の値 2の値
3の値
---------------------------
カラム名1
---------------------------
カラム名2
---------------------------
カラム名3
---------------------------

上記のように意味不明な形式で出てきます。

例えばこんな風に

select カラム1,カラム2,カラム3 from hoge;

カラム1 1の値
---------------------------
カラム2 2の値
---------------------------
カラム3 3の値

等のように分かりやすく表示できないでしょうか?

ちなみにOracle9iR2を使用しています。
sqlに関するツールは使用できないルールでして、あくまでsqlplusのコマンド上でみやすくしなければなりません。

分かりづらくですいませんが、皆さま、ご教授お願いします。

いつもお世話になっています。

サーバにアクセスしてsqlplusで、
データを調べたいのですが、
出力形式が見づらくて困っています。

よくわからいのですが、
---------------------------
カラム名1
---------------------------
カラム名2
---------------------------
カラム名3
---------------------------
1の値 2の値
3の値
---------------------------
カラム名1
---------------------------
カラム名2
---------------------------
カラム名3
-----------------------...続きを読む

Aベストアンサー

SQLPLUSを起動して、

SQL>set linesize 列数

でどうだ。

SQL>show linesize

で確認ができる。

QUnionの結果全体をOrder By したい。

いつもお世話になっています。
下記のSQLの結果をWEEK_KB,TIME_KB,SPAN_CDでソートするには
どのようにしたら良いのでしょうか?

色々考えたのですが、どうしてもわかりませんでした。
よろしくお願いいたします。

SQLServerのバージョンは2000だったと思います。。。。



SELECT
'2',
A.WEEK_KB,
A.TIME_KB,
A.SPAN_CD,
A.JUGYO_CD,
B.JUGYO_MEI,
ADD_FLG , UPD_FLG , DEL_FLG
FROM table1_wk As A
INNER JOIN 授業マスタ As B
ON A.JUGYO_CD = B.JUGYO_CD
WHERE
ID='aaa'
UNION
SELECT
'1',
C.WEEK_KB,
C.TIME_KB,
C.SPAN_CD,
C.JUGYO_CD,
D.JUGYO_MEI,
'0','0','0'
FROM table1 As C
INNER JOIN 授業マスタ AS D
ON C.JUGYO_CD = D.JUGYO_CD
WHERE
C.JUGYO_CD
NOT IN
( SELECT JUGYO_CD
FROM table1_wk
WHERE
ID='aaa' )

いつもお世話になっています。
下記のSQLの結果をWEEK_KB,TIME_KB,SPAN_CDでソートするには
どのようにしたら良いのでしょうか?

色々考えたのですが、どうしてもわかりませんでした。
よろしくお願いいたします。

SQLServerのバージョンは2000だったと思います。。。。



SELECT
'2',
A.WEEK_KB,
A.TIME_KB,
A.SPAN_CD,
A.JUGYO_CD,
B.JUGYO_MEI,
ADD_FLG , UPD_FLG , DEL_FLG
FROM table1_wk As A
INNER JOIN 授業マスタ As B
ON A.JUGYO_CD = B.JUGYO_CD
WHERE
ID='aaa'
U...続きを読む

Aベストアンサー

このまま末尾に

Order by 2,3,4

ではダメでした?
2番目から4番目までの列でSORTする、と言うANSI共通の構文です。

ダメだったら、インラインビューで括ってしまう、というのは?

select inview.* from
(
ここに上記のSQLを書く
) inview
order by 2,3,4

Q全角空白のTRIMができない・・・

文字列の前後の全角空白を除去するのに
trimが使えると思っていたのですが、できませんでした。
(半角なら取れるのですが・・・)

select trim(' あいうえお ') from dual;

何とか前後の全角空白を除去したいのですが、
何か方法はないでしょうか。

Aベストアンサー

こんな感じ。
select trim(' ' from ' あいうえお ') from dual

第2引数で消したい文字を指定できます。
select rtrim(ltrim(' あいうえお ',' '),' ') from dual

Qselectした結果の余計な余白を取るにはどうしたらよいのでしょうか

質問が二つあります。

質問1)会社の先輩に、sql/plusでORACLEのDBに接続し、以下の命令を実行するとselectした結果をcsv形式でファイル出力できると教えてもらいました。

set num 18
set linesize 1000
set pagesize 0
set trimspool on
set colsep ','

上記の「set num 18」の意味を「sqlリファレンス」で調べても載ってなく分かりませんでした。ご存知の方は教えて下さい。また、どのようなマニュアルに載っているのかも教えて下さい。

質問2)上記命令を実行後に、名前=「NAME_KANJI」、属性=VARCHAR2(60)のカラムをselectすると次の様に出力されました。「~,XXXX…(この後空白が続く),~」次に、trim(NAME_KANJI)、substr(NAME_KANJI,1)、substr(NAME_KANJI,length(NAME_KANJI))などで出力すると「XXXX]の後の空白が更に長く出力されまました。substr(NAME_KANJI,1,4)なら「XXXX]の後の空白がなくなるのですが、登録されている文字数は不定のため、文字数に応じて空白を除いて出力する方法はあるのでしょうか、あればその方法を教えて下さい。よろしくお願いします。

質問が二つあります。

質問1)会社の先輩に、sql/plusでORACLEのDBに接続し、以下の命令を実行するとselectした結果をcsv形式でファイル出力できると教えてもらいました。

set num 18
set linesize 1000
set pagesize 0
set trimspool on
set colsep ','

上記の「set num 18」の意味を「sqlリファレンス」で調べても載ってなく分かりませんでした。ご存知の方は教えて下さい。また、どのようなマニュアルに載っているのかも教えて下さい。

質問2)上記命令を実行後に、名前=「NAME_KANJI」、属性...続きを読む

Aベストアンサー

こんにちは。

回答1.
SQL*Plusユーザーズ・ガイドおよびリファレンス

回答2.
SELECT COL1||','||COL2||','||COL3
FROM テーブル名
みたいにカラムを連結させてしまう方法もありますよ。


人気Q&Aランキング

おすすめ情報