プロが教えるわが家の防犯対策術!

お世話になります。

下記のテーブルから下記の条件で
データを出力するSQL文を作成したいのです。
アドバイスお願い致します。

条件1:NAMEはAを抽出
条件2:ICHIはグループ集計
条件3:ITEMはD2とD3を抽出


TESTテーブル

NAME ICHI ITEM DATA
----- ---- ---- ----
A A1 D1 1
A A1 D2 2
A A1 D3 3
A A1 D4 4
A A2 D1 10
A A2 D2 20
A A2 D3 30
A A2 D4 40
B A3 D1 100
B A3 D2 200
B A3 D3 300
B A3 D4 400

データ出力結果

NAME ICHI DATA DATA
----- ---- ---- ----
A A1 2 3
A A2 20 30

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

A 回答 (5件)

#3 です。

#4 さんとほとんど同じですが、その後気付いたのが MAX を使う方法でした。

SELECT
NAME, ICHI,
MAX(CASE WHEN ITEM = 'D2' THEN DATA ELSE NULL END) AS DATA_D2,
MAX(CASE WHEN ITEM = 'D3' THEN DATA ELSE NULL END) AS DATA_D3
FROM TEST a
WHERE NAME = 'A'
GROUP BY NAME, ICHI
;
    • good
    • 0
この回答へのお礼

アドバイスありがとうございました。
助かりました。

お礼日時:2012/06/15 13:30

select NAME,ICHI


,sum(case when ITEM = 'D2' then DATA else 0 end) as DATA_D2
,sum(case when ITEM = 'D3' then DATA else 0 end) as DATA_D3
where NAME = 'A'
group by NAME,ICHI
order by ICHI

じゃダメですか?(未検証ですけど)
    • good
    • 0

スカラ・サブクエリを使う方法も有りますね。



SELECT
NAME, ICHI,
(SELECT DATA FROM TEST b WHERE ITEM = 'D2'
AND a.NAME = b.NAME AND a.ICHI = b.ICHI) AS DATA_D2,
(SELECT DATA FROM TEST c WHERE ITEM = 'D3'
AND a.NAME = c.NAME AND a.ICHI = c.ICHI) AS DATA_D3
FROM TEST a
WHERE NAME = 'A'
GROUP BY NAME, ICHI
;

参考URL:http://codezine.jp/article/detail/751?p=1
    • good
    • 0

SELECT D2.NAME,D2.ICHI,D2.DATA,D3.DATA


FROM TEST D2 INNER JOIN TEST D3
ON D3.NAME=D2.NAME AND D3.ICHI=D2.ICHI AND D3.ITEM='D3'
WHERE D2.NAME='A' AND D2.ITEM='D2'
ORDER BY 2
でどうでしょうか。
    • good
    • 0

同TESTテーブルに下記のQuery3つ用意します。



1.Main
 NAMEとICHIの2項目でグループ化
 条件として NAMEが"A"、ITEMが"D2","D3"
 すると、以下のようになります。
 NAME ICHI
 ----- ----
 A A1
 A A2
2.Sub1
 NAMEとICHIの2項目でグループ化
 DATAを集計(SUM)します。
 条件として NAMEが"A"、ITEMが"D2"のみ
 すると、以下のようになります。
 NAME ICHI DATA1
 ----- ---- ----
 A A1 2
 A A2 20
3.Sub2
 NAMEとICHIの2項目でグループ化
 DATAを集計(SUM)します。
 条件として NAMEが"A"、ITEMが"D3"のみ
 すると、以下のようになります。
 NAME ICHI DATA2
 ----- ---- ----
 A A1 3
 A A2 30

※そして、MainにSub1,Sub2を外部結合するとできあがり!です。
    • good
    • 0

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

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

このQ&Aを見た人はこんなQ&Aも見ています

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

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

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

Q【SQL】取得項目を1レコードに並べるには?

こんにちは


【テーブル概要】
テーブルA(会員番号テーブル)
・会員番号(PK)

テーブルB(買い物情報テーブル)
・会員番号(PK)
・買い物月(PK)
・商品番号(PK)
・請求金額


【したいこと】
上記のようなテーブルがあった場合に、以下のような出力を行いたいと思っています。

会員番号,商品番号,買い物月(7月)の請求金額,買い物月(8月)の請求金額,買い物月(9月)の請求金額
--------,--------,-----------------------,-----------------------,-----------------------
会員1  ,商品1  ,          500,            ,          100
会員1  ,商品2  ,            ,          300,          100
会員2  ,商品1  ,          1500,          100,          100
会員3  ,商品3  ,            ,            ,          100
会員3  ,商品4  ,            ,          200,           

【試したこと】
一度、テーブルBを買い物月単位に割ってみました。

テーブルB_買い物月(7月)
・会員番号(PK)
・商品番号(PK)
・請求金額

テーブルB_買い物月(8月)
・会員番号(PK)
・商品番号(PK)
・請求金額

テーブルB_買い物月(9月)
・会員番号(PK)
・商品番号(PK)
・請求金額

それからINNER JOINで繋いでいけば、できるかと思ったのですが
実際の買い物月は結構過去まであり(2003年くらい)、ありえない程深いネストになってしまいます。。


どなたかお知恵をお貸し願います。

こんにちは


【テーブル概要】
テーブルA(会員番号テーブル)
・会員番号(PK)

テーブルB(買い物情報テーブル)
・会員番号(PK)
・買い物月(PK)
・商品番号(PK)
・請求金額


【したいこと】
上記のようなテーブルがあった場合に、以下のような出力を行いたいと思っています。

会員番号,商品番号,買い物月(7月)の請求金額,買い物月(8月)の請求金額,買い物月(9月)の請求金額
--------,--------,-----------------------,-----------------------,-----------------------
会員1  ,商品1  , ...続きを読む

Aベストアンサー

縦横変換の典型ですね。
サンプルを提示しますので、加工してください。

SELECT
会員番号,
商品番号,
'2008年' AS 年度,
SUM(CASE WHEN 買い物月 = 200801 THEN NVL(請求金額,0) END) 請求金額01月,
SUM(CASE WHEN 買い物月 = 200802 THEN NVL(請求金額,0) END) 請求金額02月,
SUM(CASE WHEN 買い物月 = 200803 THEN NVL(請求金額,0) END) 請求金額03月,
SUM(CASE WHEN 買い物月 = 200804 THEN NVL(請求金額,0) END) 請求金額04月,
SUM(CASE WHEN 買い物月 = 200805 THEN NVL(請求金額,0) END) 請求金額05月,
SUM(CASE WHEN 買い物月 = 200806 THEN NVL(請求金額,0) END) 請求金額06月,
SUM(CASE WHEN 買い物月 = 200807 THEN NVL(請求金額,0) END) 請求金額07月,
SUM(CASE WHEN 買い物月 = 200808 THEN NVL(請求金額,0) END) 請求金額08月,
SUM(CASE WHEN 買い物月 = 200809 THEN NVL(請求金額,0) END) 請求金額09月,
SUM(CASE WHEN 買い物月 = 200810 THEN NVL(請求金額,0) END) 請求金額10月,
SUM(CASE WHEN 買い物月 = 200811 THEN NVL(請求金額,0) END) 請求金額11月,
SUM(CASE WHEN 買い物月 = 200812 THEN NVL(請求金額,0) END) 請求金額12月
FROM テーブルB
where 買い物月 between 200801 and 200812
GROUP BY
会員番号,
商品番号;

ポイント
・CASEで該当するデータを横展開する。
・Group Byで横展開した値を集計し1列に集約する。

応用
 ・各カラムのデータ型にあわせてSQLを変更する。
  ※date型、number型、char型。
 ・上記は2008年に限定しているので、実際に欲しい形に変更する。
  ※年を意識しない作りにするなど。

縦横変換の典型ですね。
サンプルを提示しますので、加工してください。

SELECT
会員番号,
商品番号,
'2008年' AS 年度,
SUM(CASE WHEN 買い物月 = 200801 THEN NVL(請求金額,0) END) 請求金額01月,
SUM(CASE WHEN 買い物月 = 200802 THEN NVL(請求金額,0) END) 請求金額02月,
SUM(CASE WHEN 買い物月 = 200803 THEN NVL(請求金額,0) END) 請求金額03月,
SUM(CASE WHEN 買い物月 = 200804 THEN NVL(請求金額,0) END) 請求金額04月,
SUM(CASE WHEN 買い物月 = 200805 THEN NVL(請求金額,0) END) 請求金...続きを読む

QSQLにて縦を横へ展開

[注文表]
注文番号 注文商品 注文金額
=======================
111   あああ  5500
222   いいい  3000

[注文オプション表]
注文番号 オプション
================
111   P1
111   P2
222   D1

上記のようなテーブルがあります。
これを下記のようにSQLでまとめたいですが可能でしょうか。
もしありましたら、サンプルSQLをご教示いただけませんでしょうか。

[注文一覧]
注文番号 注文商品 注文金額 オプション1 オプション2
======================================================
111    あああ  5500   P1      P2
222    いいい  3000   D1

お手数をお掛けしますが、なにとぞよろしくお願いいたします。

Aベストアンサー

肝は「注文オプション表」ですよね。
「注文オプション表」を「tb1」で読み替えて
以下SQLを見てください。

uff-n さんが期待する
結果にはさらに副問い合わせを使って注文表と
結合する必要がありますが、まぁなんとか1SQLで書けると
思います。
同一注文番号のデータがあまりにも多い場合は以下SQLは少し苦しいですね。
あと、Oracleに限定したSQLであることも少し不満が残りますが参考にどうぞ。

---検証データ作成----
create table tb1(c1 number,c2 varchar2(10));
insert all
into tb1 (c1,c2) values(111,'aa3')
into tb1 (c1,c2) values(111,'aa2')
into tb1 (c1,c2) values(222,'aa1')
into tb1 (c1,c2) values(333,'aa1')
into tb1 (c1,c2) values(333,'aa2')
into tb1 (c1,c2) values(333,'aa3')
into tb1 (c1,c2) values(444,'aa5')
into tb1 (c1,c2) values(444,'aa5')
select * from dual
;

---縦を横に展開するSQL
select c1,max(c2_1) n01,max(c2_2) n02,max(c2_3) n03,max(c2_4) n04
from
(
select t.c1,
decode(rk,1,c2,null) c2_1,
decode(rk,2,c2,null) c2_2,
decode(rk,3,c2,null) c2_3,
decode(rk,4,c2,null) c2_4
from
(
select c1,c2,row_number() over(partition by c1 order by c2 ) rk from tb1
)t
) group by c1


--結果
C1  N01 N02 N03 N04
---  --- --- --- ---
111  aa2 aa3  
222  aa1    
333  aa1 aa2 aa3
444  aa5 aa5  



どうでしょうか?期待した結果でしたか?

肝は「注文オプション表」ですよね。
「注文オプション表」を「tb1」で読み替えて
以下SQLを見てください。

uff-n さんが期待する
結果にはさらに副問い合わせを使って注文表と
結合する必要がありますが、まぁなんとか1SQLで書けると
思います。
同一注文番号のデータがあまりにも多い場合は以下SQLは少し苦しいですね。
あと、Oracleに限定したSQLであることも少し不満が残りますが参考にどうぞ。

---検証データ作成----
create table tb1(c1 number,c2 varchar2(10));
insert all
into tb1 (c1,c2) values(...続きを読む

Q縦持ちになっているデータを横持ちにしたいのですが

SQLServser2012で縦持ちになっているデータを横持ちにしたいのですが、どうしたらよいでしょうか?

〇以下のように現在、テーブル設計されております。
請求TBL
---------------------------------------------
請求ID 請求先名
001 田中 太郎
002 鈴木 一郎

注文TBL
------------------------------------------------
請求ID 注文ID 発送先 商品ID 個数
001 001 田中 太郎 A 10
001 002 佐藤 次郎 A 5
002 003 鈴木 一郎 B 3

商品TBL
-----------------------------
商品ID 商品名
A みかん
B りんご

このデータを以下のような結果として得たいのですが、どのようにしたらSQLを作成したらよろしいでしょうか?
請求ID 請求先 商品内容
-------------------------------------------------
001 田中 太郎 みかん*10、みかん*5
002 鈴木 一郎 りんご*3

ご教授の程、宜しくお願い致します。

SQLServser2012で縦持ちになっているデータを横持ちにしたいのですが、どうしたらよいでしょうか?

〇以下のように現在、テーブル設計されております。
請求TBL
---------------------------------------------
請求ID 請求先名
001 田中 太郎
002 鈴木 一郎

注文TBL
------------------------------------------------
請求ID 注文ID 発送先 商品ID 個数
001 001 田中 太郎 A 10
001 002 佐藤 次郎 A 5
002 003 鈴木 一郎 B 3

商品TBL
-----------------------------
商品ID 商品名
A みかん
B りんご...続きを読む

Aベストアンサー

SELECT
tbl_1."請求ID"
, tbl_1."請求先名 "
, (Select
      ' ' + tbl_3."商品名" + "*" + "個数 "
From "注文TBL" tbl_2
Left Join "商品TBL" tbl_3
On tbl_2."商品ID" = tbl_3."商品ID"
WHERE tbl_1."請求ID"= tbl_2."請求ID"
For_XML_Path('')) as "商品名"
FROM "請求TBL" tbl_1

こんな感じだったかな~。コード検証はしていません!
For_XML_Pathで言うならば文字列の結合GroupByになったはずです。
MySQLでいう、group_concat関数ですね。

QMAX値を条件にデータを取得するには?

SQL文で困っています。
ご教授下さい。


下記のようなデータがあった場合、それぞれの区分毎に
年月が最大(最新)のデータを取得したいです。
(実際には1レコードにその他項目があり、それらも取得します。)
<検索対象データ>
区分 年月   金額
-----------------------------
A   200412  600
A   200503  560
B   200311  600
B   200508  1000
B   200504  560
C   200508  400
C   200301  1100


<取得したいデータ>

区分 年月   金額
-----------------------------
A   200503  560
B   200508  1000
C   200508  400

よろしくお願いします。

Aベストアンサー

テーブル名をXXXとすると次のようなSQLでよいと思います。(最善の方法かどうかは自信がないですが)

select B.* from (select 区分, max(年月) as 年月 from XXX group by 区分) As A
inner join XXX as B on A.区分 = B.区分 and A.年月 = B.年月
order by B.区分

Q文字列をsplitするSQL文を教えて下さい

SQLServer2000上で指定した文字で文字列をsplitような関数があれば教えて頂けますか?

例えば"12345.6789"という文字列を"."(ピリオド)を指定して、"12345"と"6789"という2つの文字列を取得したいと思っています。

Transact-SQLの関数リファレンスを見てみたのですが、適当なものが見つかりませんでした。
自作するしかないのでしょうか・・

Aベストアンサー

見当たりませんね~。

declare @str1 nvarchar(1),@str2 nvarchar(50)
select @str1 =N'.',@str2 = N'12345.6789'
select
substring(@str2,1,charindex(@str1,@str2)-1) as 前,
substring(@str2,charindex(@str1,@str2)+1,len(@str2)-charindex(@str1,@str2)) as 後

こんな感じにしかできないみたいです

Q行列の変換

SQL SERVER 2005

Aのテーブルから
A1というビューを作成しました。

そのビューの内容というのは

AのテーブルのTOP4を
列に変換したいというものです。


例)

Aテーブル
(列名 AA AB AC)

AA1 AB1 AC1
AA2 AB2 AC2
AA3 AB3 AC3



AAビュー

AA_1 AB_1 AC_1 AA_2 AB_2 AC_2 AA_3 AB_3 AC_3
AA1 AB1 AC1 AA2 AB2 AC2 AA3 AB3 AC3


となるような、ビューのSQLがあれば、教えて頂きたく思います。
よろしくお願いします。

Aベストアンサー

>SELECT ROW_NUMBER() OVER (ORDER BY ID) SEQ,* FROM TABLE

ROW_NUMBERは分析関数と呼ばれますが、
「ROW_NUMBER () OVER (ORDER BY ID)」と書くことで、戻りのレコードをID別に並び替えた場合の連番を返してくれます。

これをメインのクエリで使うために、私が勝手に「SEQ」という名前をつけているとご理解ください。

Qクロス集計で、列を動的に

SQLserverでのクロス集計についてご質問ですが、
クロス集計後の結果、列を動的に変更させる必要がある場合、ストアドプロシージャで処理しなければならないかとは思うのですが、
例えば
T_売上
番号,日付,数
1,2004/09/10,10
1,2004/09/14,20
1,2004/09/18,10
2,2004/09/10,20
2,2004/09/14,20
3,2004/09/18,10

番号,2004/09/10,2004/09/14,2004/09/18
1,10,20,10
2,20,20,0
3,0,0,10
という形に出力したいときは、具体的にどういう手順でSSQLを作成していけばよろしいでしょうか。
(日付部分が動的に増えたり減ったりします)

Aベストアンサー

declare @sql1 varchar(8000)
declare @sql2 varchar(8000)
declare @i int
declare @fld1 varchar(20)
declare @fld2 varchar(20)
declare @fldx varchar(20)
select @sql1='select T売上.番号'
select @sql2=''
select @fld1='日付'
select @fld2='数'
select @i=0

declare fld_lst cursor for
select distinct convert(varchar,日付,111) as 日付 from T売上 order by 日付

open fld_lst

while @i<100
begin
fetch next from fld_lst into @fldx
if @@fetch_status<>0 break

set @sql1=@sql1+',sum(case when '+convert(varchar,@fld1,111)+'='''+@fldx
+''' then '+@fld2+' else 0 end) as ['+@fldx+']'

set @i=@i+1
end

if @@fetch_status=0
while 1=1
begin
fetch next from fld_lst into @fldx
if @@fetch_status<>0 break
set @sql2=@sql2+',sum(case when '+convert(varchar,@fld1,111)+'='''+@fldx
+''' then '+@fld2+' else 0 end) as ['+@fldx+']'
end

close fld_lst
deallocate fld_lst

exec(@sql1+@sql2+' from T売上'
+' group by T売上.番号 order by T売上.番号')
return

以上でできるはずでは。

declare @sql1 varchar(8000)
declare @sql2 varchar(8000)
declare @i int
declare @fld1 varchar(20)
declare @fld2 varchar(20)
declare @fldx varchar(20)
select @sql1='select T売上.番号'
select @sql2=''
select @fld1='日付'
select @fld2='数'
select @i=0

declare fld_lst cursor for
select distinct convert(varchar,日付,111) as 日付 from T売上 order by 日付

open fld_lst

while @i<100
begin
fetch next from fld_lst into @fldx
if @@fetch_status<>0 bre...続きを読む

QSQL文について(片方のテーブルに存在しないレコード抽出)

以下のような2つのテーブルがあったとして、
2つともに存在する「店コード」を抽出するのはSQLは分かるのですが、
片方に存在しない「店コード」(以下の例の場合、「2」)を抽出するSQLを
一文で書くにはどうすればいいのでしょうか?

<店テーブル>
店コード住所・・・(その他、基本情報)
1aaa
2bbb
3ccc

<販売テーブル>
店コード販売品目・・・(その他、販売数など)
1xxx
3zzz

Aベストアンサー

オプティマイザ次第だけど、NOT-INは、あまりお勧めでない。
外部結合も索引があっても有効に使われないので、お勧めでない。

select * from A where not exists(select 1 from B where A.店コード=B.店コード);

Q2つのテーブルから条件に一致しないデータ抽出

2つのテーブルから条件に一致しない行のみ抜き出しCSV
か他のテーブルに抜き出す処理を作ろうと思っています。

そこで質問なのですが、以下の2つのテーブルから
条件に一致しないコードのみ抜き出すSQL文などありますか?

-----------------------------------------
マスターテーブル
コード、ネーム1、ネーム2、店コード
1、あ、い、6
2、う、え、8
3、お、か、4
4、き、く、2


店コードテーブル
店コード、店区分
1、スーパー
2、コンビニ
3、デパート
4、ホームセンター
------------------------------------------

条件は、マスターテーブルの店コードが店コードテーブルに存在
しないデータのみ抽出です。

抽出結果は、以下の様にしたいです。
1、あ、い、6
2、う、え、8

このような考え大丈夫でしょうか?
SELECT *
FROM マスターテーブル AS M INNER JOIN 店コードテーブル AS S
ON M.店コード = S.店コード

わかりずらい質問ですがよろしくお願いします。

2つのテーブルから条件に一致しない行のみ抜き出しCSV
か他のテーブルに抜き出す処理を作ろうと思っています。

そこで質問なのですが、以下の2つのテーブルから
条件に一致しないコードのみ抜き出すSQL文などありますか?

-----------------------------------------
マスターテーブル
コード、ネーム1、ネーム2、店コード
1、あ、い、6
2、う、え、8
3、お、か、4
4、き、く、2


店コードテーブル
店コード、店区分
1、スーパー
2、コンビニ
3、デパート
4、ホームセンタ...続きを読む

Aベストアンサー

このような考え大丈夫でしょうか?
SELECT *
FROM マスターテーブル AS M INNER JOIN 店コードテーブル AS S
ON M.店コード = S.店コード

店コードが一致するレコードを抽出しているのですね。
はい、大丈夫です。
もっとも単純に、分かりやすい式を書くのであれば

この条件否定なので
left outer join で結合して、結合できなかったデータ、
つまり、店がNullのデータを取れば抽出できます。

SELECT *
FROM マスターテーブル AS M left outer join 店コードテーブル AS S
ON M.店コード = S.店コード
where S.店コード is null

または、
SELECT *
FROM マスターテーブル as M
where M.店コード not in ( select 店コード from 店コードテーブル)

等でいけるでしょう。

ただ、参考URLにもありますように、
is null とか not in は遅いです。(Indexで見れなくなるので)
大量データを扱うような場合や、速度が求められるような場合は
別途クエリを検討しましょう。

参考URL:http://www.geocities.jp/mickindex/database/db_optimize.html

このような考え大丈夫でしょうか?
SELECT *
FROM マスターテーブル AS M INNER JOIN 店コードテーブル AS S
ON M.店コード = S.店コード

店コードが一致するレコードを抽出しているのですね。
はい、大丈夫です。
もっとも単純に、分かりやすい式を書くのであれば

この条件否定なので
left outer join で結合して、結合できなかったデータ、
つまり、店がNullのデータを取れば抽出できます。

SELECT *
FROM マスターテーブル AS M left outer join 店コードテーブル AS S
ON M.店コード = S....続きを読む

QSQLServer2005のストアドプロシージャで変数を配列で使用する方法をご教示ください

タイトルの件。調査したのですが、変数を配列でとる方法(宣言の仕方や使用方法)が見つけられませんでした。
ご存知の方、ご教示いただけませんでしょうか?
できれば使用方法が書かれた本やサイトをご紹介いただけると助かります。
それともSQLServer2005のストアドプロシージャでは変数を配列で使用することはできないのでしょうか?
(そんなことはないと思うのですが、探しても出てこないのが不思議です。私の検索方法が悪いからだと思いますが)

以上です。よろしくお願いします。

Aベストアンサー

SQL Serverのストアドプロシージャでは
昔からの仕様で変数には配列が存在しません。

しかしながら、SQL Server 2005はXMLを使用して
配列的に引数を渡すことができます。

http://blogs.sqlpassj.org/yoneda/archive/2005/05/14/9008.aspx

また、内部変数を配列的に使用したければ代わりに、
Table型変数やCursorまたはCursor型変数の使用ができるので、
こちらで配列的な処理が可能でしょう。
(ただし膨大なデータ量だとパフォーマンス劣化の恐れがあります)


人気Q&Aランキング