マンガでよめる痔のこと・薬のこと

こういうテーブル「tb」があるとします。

「tb」
bang nama tuki
A103 佐藤 4
A102 高橋 5
A104 鈴木 4
A101 中川 4
A103 渡辺 5
A101 西沢 5
A102 中村 6
A104 田中 5
A103 村井 6
A107 吉田 6
A101 小林 4

これを、実行後に、
bang 4月 5月 6月
A101 中川 西沢 NULL
A102 NULL 高橋 中村
A103 佐藤 渡辺 村井
A104 鈴木 田中 NULL
A107 NULL NULL 吉田

このように、共通するbangで並べ替えたいのです。
A101の4月は、「中川」と「小林」で重複していますが、
このときはひとりだけが選択されてほしいです。

SELECT
CONCAT(CASE
WHEN tuki='4' THEN name
ELSE IFNULL(null,'')
END) AS '4月',
~
GROUP BY bang
~

としても、
bang 4月 5月 6月
A101 中川     

となってしまい、5月と6月が上手く表示されません。

よろしくお願いします。

A 回答 (2件)

あ、よくよく考えたらこの程度ならピボットしなくてもかけますね


ピボットの場合もう少し複雑な集計をとるとき便利です。
もちろん今回のケースでも活用できます

//例
select bang
,max(if(tuki=4,name,null)) as 4月
,max(if(tuki=5,name,null)) as 5月
,max(if(tuki=6,name,null)) as 6月
from tb as t
group by bang
order by bang;

//ちなみに2名以上いるときの列記
select bang
,group_concat(if(tuki=4,name,null)) as 4月
,group_concat(if(tuki=5,name,null)) as 5月
,group_concat(if(tuki=6,name,null)) as 6月
from tb as t
group by bang
order by bang;

//各bangに月ごと何人いるかカウントする
select bang
,sum(tuki=4) as 4月
,sum(tuki=5) as 5月
,sum(tuki=6) as 6月
from tb as t
group by bang
order by bang;
    • good
    • 0
この回答へのお礼

おお!うまく表示されました!文字列にMAXって使えたんですね……。
2名以上や人数のカウントも勉強になりました!
ピボットテーブルもしっかり頭に入れておきます!

ありがとうございました!
助かりました!

お礼日時:2013/02/07 01:24

この手の処理にはピボットテーブルを使うと処理が楽です



//元データ
create table tb (bang varchar(10),name varchar(10),tuki int);
insert into tb values('A103','佐藤',4),('A102','高橋',5),('A104','鈴木',4),('A101','中川',4),('A103','渡辺',5),('A101','西沢',5),('A102','中村',6),('A104','田中',5),('A103','村井',6),('A107','吉田',6),('A101','小林',4);

//ピボットテーブル
create table pivot(month int,m4 int null,m5 int null,m6 int null);
insert into pivot(month,m4) values(4,1);
insert into pivot(month,m5) values(5,1);
insert into pivot(month,m6) values(6,1);

//結果
select bang
,max(if(m4,name,null)) as 4月
,max(if(m5,name,null)) as 5月
,max(if(m6,name,null)) as 6月
from tb as t
inner join pivot as p on t.tuki=p.month
group by bang
order by bang;
    • good
    • 0

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

関連するカテゴリから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) 請求金...続きを読む

Qカウント結果を1レコードの中で横に並べたい

カウント結果を1レコードの中で横に並べたい


以下のテーブルを、

test_table
id   group  name
1    100   テスト1
2    100   テスト1
3    100   テスト1
4    200   テスト1
5    200   テスト1
6    200   テスト2
7    200   テスト2
8    200   テスト2
9    200   テスト2


nameごとのカウント、更に100だけのカウント、200だけのカウントといったように、
以下のように横に並べることは可能でしょうか?

name   合計  100計   200計
テスト1  5    3      2
テスト2  4    0      4


まず、nameごとの合計を取得するクエリを作って、
それに、100計列、200計列の部分をサブクエリでもってきたのですが、
これで動くには動くんですが、このやり方しかないものでしょうか。

ご教示おねがいします。

SELECT t.name, COUNT(*) as '合計',
(SELECT COUNT(*) FROM test_table as t1 WHERE t1.name = t.name AND t1.group = '100' ) as '100計',
(SELECT COUNT(*) FROM test_table as t1 WHERE t1.name = t.name AND t1.group = '200' ) as '200計'
FROM test_table as t
WHERE 1
GROUP BY t.name;

カウント結果を1レコードの中で横に並べたい


以下のテーブルを、

test_table
id   group  name
1    100   テスト1
2    100   テスト1
3    100   テスト1
4    200   テスト1
5    200   テスト1
6    200   テスト2
7    200   テスト2
8    200   テスト2
9    200   テスト2


nameごとのカウント、更に100だけのカウント、200だけのカウントといったように、
以下のように横に並べることは可能でしょうか?

name   ...続きを読む

Aベストアンサー

groupのカラムに入る値が分かっているのであれば
case文を使うとパフォーマンスにも優れると思います。
case文を使うと条件によって出力する値を変える事ができます。

SELECT
  name,
  COUNT(*) AS total,
  SUM(CASE
    WHEN group='100' THEN 1
    ELSE 0
    END) AS count_100,
  SUM(CASE
    WHEN group='200' THEN 1
    ELSE 0
    END) AS count_200
FROM
  test_table
GROUP BY
  name;
(※上記のSQLはインデントを付けるために全角のスペースを使っています。コピペをする際は削除して下さい。)

参考URL:http://homepage2.nifty.com/sak/w_sak3/doc/sysbrd/mysql_15.htm

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(...続きを読む

Qmysqlでクロス集計を取りたいが、sqlが分からない。

テーブル名は、収穫で下記のデータが入っている。
日付 id size
160201 1 S
160201 2 S
160201 3 M
160202 4 L
160202 5 L
160203 6 L
160204 7 S


これのクロス集計を下記の様に作りたい。
テーブル名は、crossとする。
日付 S M L 合計
160201 2 1  3
160202   2 2
160203   1 1
160204 1   1

済みません、sqlの回答をお願いします。

Aベストアンサー

>group byでユニークになっているのですね。

勘違いしてはいけないのは、データが単にユニークなだけで、ユニークな属性は
ついていないということです。
もしさらにそのテーブルからリレーションするのであれば、なんらかの
属性はつけた方がよいかもしれません

>もう一つ質問ですが。ここで作ったテーブルを別のテーブルを日付でjoin
>したいのですが。この時の日付はインデックスの指定が要るのでしょうか。

であれば「create table xt」するときに適正なインデックスをつくっておいた方がよいでしょう。
もちろん後付けでalter tableで追加することもできます

Qmysqlのcase文で複数の条件を指定する

mysql初心者です。以下のデータから勤務時間(end_time-start_time-break
_time)が8時間以上、かつ終了時間が22時以降の場合、(end_time-22:00)を深夜残業時間として、深夜残業時間を計算したいのですが、case文で場合分けしてAかつBの場合というような書き方はどのように書いたらいいでしょうか。よろしくお願いします。

name  start_time  end_time  break_time
田中   9:00      22:30    1:00
佐藤   9:00      21:00    1:00
渡辺   10:00     24:00    1:00
藤原   9:00      18:00    1:00

Aベストアンサー

AかつBならIFをつかってANDでつなげばいい気がしますが・・・
CASEにこだわるなら、WHENで条件外をしぼってELSEで実数を得ればよいかも
ざっくり今回のだとこんな感じ?

SELECT name,start_time,end_time,break_time
,CASE
WHEN end_time<'22:00:00' THEN NULL
WHEN TIMEDIFF(TIMEDIFF(end_time,start_time),break_time)<='08:00:00' THEN NULL
ELSE TIMEDIFF(end_time,'22:00:00')
END AS SHINYA
FROM schedule

Q行と列の入れ替え検索(行は可変で項目を固定にしたい)のSQL文が出来な

行と列の入れ替え検索(行は可変で項目を固定にしたい)のSQL文が出来ない

以下のテーブルがあります。

<テーブルA>
UID  name
---------------
1   伊藤
2   鈴木
3   高橋
4   佐藤

<テーブルB>
no UID key   value
-------------------
1  1  telno  001-01-0001
2  1  faxno  001-01-0002
3  1  nickname itochan
4  2  telno  111-11-1111
5  2  faxno  111-11-1112
6  3  nickname hashi

この2つのテーブルがあります。
・テーブルAにあるUIDはテーブルBにない場合もあります。
この場合、以下のように出力して、csvファイルにしたいのですが、可能でしょうか?

申し訳ありませんが、よろしくアドバイスお願いいたします。

UID  name  telno    faxno     nickname
-------------------------------------------------
1   伊藤  001-01-0001 001-01-0002  itochan
2   鈴木  111-11-1111 111-11-1112
3   高橋                  hashi
4   佐藤

(経緯:wordpressのユーザの一覧(users,usetmetaテーブル)をユーザ一覧としてCSV形式にして保管したいのです。抽出&登録できるプラグインがなかったので)

行と列の入れ替え検索(行は可変で項目を固定にしたい)のSQL文が出来ない

以下のテーブルがあります。

<テーブルA>
UID  name
---------------
1   伊藤
2   鈴木
3   高橋
4   佐藤

<テーブルB>
no UID key   value
-------------------
1  1  telno  001-01-0001
2  1  faxno  001-01-0002
3  1  nickname itochan
4  2  telno  111-11-1111
5  2  faxno  111-11-1112
6  3  nickname hashi

この2つのテーブルがあります。
・テーブルAにあるUID...続きを読む

Aベストアンサー

select
distinct a.uid UID
,a.name name
,(select value from table_B b2 where b2.uid=b.uid and `key`='telno') telno
,(select value from table_B b2 where b2.uid=b.uid and `key`='faxno') faxno
,(select value from table_B b2 where b2.uid=b.uid and `key`='nickname') nickname
from table_A a
left join table_B b
on a.uid=b.uid
;

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テーブルの最後(最新)のレコードを抽出したい

宜しくお願いします。
PHP MYSQL の組み合わせで使っています

以下のようにして、最後のレコードを取り出したいのですが
まったく違う事をしているのかもしれません。

$sql =" select * from テーブル where フィールド='max' " ;

フィールドはauto_incrementで番号を振っています。
これで最大のつもりなのですが・・・。

他にも、レコードを入れた時間も記録したフィールドがあるのですが
どうしてよいか?判りません。

テーブルの最新のレコードを出したいのです。
**その中の一つのフィールドを取り出すのですが、
  それはうまくいっているみたいです 
  (max の所に数字を入れると表示します)

お手数かけますが、どなたかご教授お願いいたします。

Aベストアンサー

#2回答者です。

MySQL 4.1以前(サブクエリを使えない)なら、以下のような方法が考えられます。

select * from 表名
order by 列名 desc limit 1

QInner join と Left joinの明確な違いは?

Inner join と Left joinの違いがよくわかりません。
教えてください。

Aベストアンサー

出てくる結果が違います。

テーブル1のフィールド1に、






が、

テーブル2のフィールド1に、






が入力されている場合、

SELECT [テーブル1].[フィールド1], [テーブル2].[フィールド1]
FROM テーブル1 LEFT JOIN テーブル2 ON [テーブル1].[フィールド1]=[テーブル2].[フィールド1];
では、結果は、
テーブル1.フィールド1 テーブル2.フィールド1
1               1
2               2
3               3
4               NULL
5               NULL
6               NULL
の6レコードが出力されますが、

SELECT [テーブル1].[フィールド1], [テーブル2].[フィールド1]
FROM テーブル1 INNER JOIN テーブル2 ON [テーブル1].[フィールド1]=[テーブル2].[フィールド1];
では、結果は、
テーブル1.フィールド1 テーブル2.フィールド1
1               1
2               2
3               3
の3レコードしか出力されません。

出てくる結果が違います。

テーブル1のフィールド1に、






が、

テーブル2のフィールド1に、






が入力されている場合、

SELECT [テーブル1].[フィールド1], [テーブル2].[フィールド1]
FROM テーブル1 LEFT JOIN テーブル2 ON [テーブル1].[フィールド1]=[テーブル2].[フィールド1];
では、結果は、
テーブル1.フィールド1 テーブル2.フィールド1
1               1
2               2
3           ...続きを読む

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.年月 = 任意の値

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


人気Q&Aランキング