人に聞けない痔の悩み、これでスッキリ >>

こんにちは。

mysqlのselect文なのですが、LEFT OUTER JOIN した右側をgroup byし、そのなかで最大値を持つものを結び付けたいのですが、四苦八苦やってみたところ2日ほど解決できず・・・。

どなたかご教授いただけますと助かります。
テーブルなどは以下の通りです。

---tableA----(テーブル名)
user / supplier(カラム名、以下レコード)
1 / 1
1 / 2
1 / 3
2 / 1
2 / 3

---tableB---
skuno / supplier / update_at
1 / 1 / 2011-12-12
2 / 1 / 2011-12-13
3 / 1 / 2010-11-10

検索の目的は、特定のカラムuserに対応する(例えばuser = 1)supplierの値(1,2,3)を取り出し、tableBにおいて、それぞれの値に結びつくskunoのうち最新のupdate_atを持つskunoを取り出したいというものです。

上記の例の場合、希望する検索結果は以下のような想定です。
supplier / skuno
1 / 2
2 / null
3 / null

私が検討してみたsql文は以下の通りなのですが、

select supplier,skuno from tableA LEFT JOIN tableB ON tableA.supplier = tableB.supplier where tableA.user = 1 and tableB.update_at IN (select max(update_at) from tableB group by supplier) group by tableA.supplier;

この場合だと、右側がnullだとsupplierの値が表示されず、

supplier / skuno
1 / 2

となってしまいます。

select supplier,skuno from tableA LEFT JOIN tableB ON tableA.supplier = tableB.supplier where user = 1 group by supplier;

だと右側のテーブルの値が指定できず・・・

ここからどのように条件付けをしたらよいのか、お力添えをいただけますと幸いです。

よろしくお願い申し上げます。

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

A 回答 (3件)

こうすれば希望する検索結果になるんじゃないでしょうか


SELECT a.supplier,b.skuno,max(update_at) from a
LEFT JOIN b ON a.supplier = b.supplier
WHERE a.user = 1 GROUP BY a.supplier;

問題はupdate_atフィールドで、「update_at IN (select max」の条件が適用される時、結合テーブルからsupplier2,3が消えるのが困るわけですよね。
    • good
    • 0
この回答へのお礼

まさにその通りです!

なんだかややこしい方に考えてしまっていたみたいで・・・。

お力添えの程、ありがとうございましたm(_ _)m

お礼日時:2011/12/19 15:33

こうですね



select a.supplier,b.skuno
from tableA as a
left join tableB as b on b.supplier=a.supplier
and (b.supplier,b.update_at) IN (select supplier,max(update_at) as update_at from tableB group by supplier)
where user=1
    • good
    • 0

こんな感じでしょうか・


MySQLの環境が無く動作確認はしていないので、文法エラーなどがあったら適宜修正してください。

select A.supplier, B.skuno
from tableA as A
left join tableB as B on (
A.supplier = B.supplier
and B.update_at IN (
select max(update_at)
from tableB as B2
where B.supplier = B2.supplier
group by supplier))
where A.userid = 1;
    • good
    • 0

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

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

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

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

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

QGROUP BYを行った後に結合したい。

oracle8iを使用しています。
2つのテーブルを結合したいと思っていますが、
テーブル構成が、N対Nのテーブルのため、
片方をグループ化したあとに結合させようと思っています。

ところが、単純にGroup byを使うと、結合した後に
グループ化してしまうために、期待した値がひょうじされません。

なんとか、1回のSQLで正しく情報をとることはできないものでしょうか。

Aベストアンサー

>テーブル構成が、N対Nのテーブルのため、
>片方をグループ化したあとに結合させようと思っています。

多分こんな形ではどうですか?

SELECT A.KEY,B.KEY,
FROM TABLE_A A
,(SELECT KEY FROM TABLE_B
GROUP BY KEY) B
WHERE A.KEY=B.KEY;

要はFROM句にテーブル名ではなく、SELECT~GROUP BYを書く。

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

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

QSQL*Loaderでのsysdate使用

SQL*Loaderを使用してデータロードする際に、「更新日時」項目にsysdateでシステム日付を返すようにしているのですが、更新日時がTIMSTAMP型の場合に時間まで設定されません。
時間まで設定するにはどのように指定したらよいでしょうか?よろしくお願いします。

Aベストアンサー

なるほどDATE型だと時分秒は入りますがTIMSTAMP型の場合は自動で入らないのですね。
下記のようにすれば大丈夫なようです。

HOGE_TEST1.sql
--
CREATE TABLE HOGE_TEST1
( KEY1 CHAR(10) NOT NULL,
DATE1 TIMESTAMP(6) NOT NULL
)
--

HOGE_TEST1.ctl
--
LOAD DATA
INFILE 'HOGE_TEST1.dat'
APPEND
INTO TABLE HOGE_TEST1
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
"KEY1",
"DATE1" DATE 'yyyy/MM/dd hh24:mi:ss' "to_char(SYSDATE,'yyyy/MM/dd hh24:mi:ss')"
)
--

HOGE_TEST1.dat
--
"AAAAA"
"BBBBB"
"CCCCC"
--

あとはsqlldrコマンドを発行
sqlldr USER/PASS control=HOGE_TEST1.ctl
試してみて下さい

なるほどDATE型だと時分秒は入りますがTIMSTAMP型の場合は自動で入らないのですね。
下記のようにすれば大丈夫なようです。

HOGE_TEST1.sql
--
CREATE TABLE HOGE_TEST1
( KEY1 CHAR(10) NOT NULL,
DATE1 TIMESTAMP(6) NOT NULL
)
--

HOGE_TEST1.ctl
--
LOAD DATA
INFILE 'HOGE_TEST1.dat'
APPEND
INTO TABLE HOGE_TEST1
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
"KEY1",
"DATE1" DATE 'yyyy/MM/dd hh24:mi:ss' "to_char(SYSDATE,'yyyy...続きを読む

Q各伝票に対して明細を1行目だけ表示したい

たびたびお世話になります。

仮に以下のようなテーブルを考えるとします。

○テーブルA (伝票)
伝票番号 (PK)
顧客コード

○テーブルB (伝票明細)
伝票番号 (PK)
伝票明細 (PK)
注文コード
大盛り区分

レストランなどでお客さんが注文した注文伝票を
想像していただければ良いかと思います。
一つの伝票番号に対して、複数の伝票明細があると。
DB上は上記のように格納するとします。

ここでテーブル結合を使って、各伝票の1行目の明細を表示しながら
SQL1発で抜いてきたいと思います。どういうSQLを考えたら良いでしょうか?

テーブルBの表示項目が1つだけなら、

SELECT A.伝票番号, A.顧客コード, (SELECT TOP 1 注文コード FROM テーブルB B WHERE A.伝票番号 = B.伝票番号)
FROM テーブルA A

で良いのですが、複数項目となるとどうやったら良いのか判りません。

Aベストアンサー

 テーブルBの1行目レコードというのを、どう定義するかによって、答えが変わると思います。
 
例えば、テーブルBの伝票明細が1から順にふられる数字であって、その伝票明細=1のものだけ取ってくれば良いというのであれば、SQL文はいたってシンプルになるかと思います。
 
 SELECT A.伝票番号,A.顧客コード,B.伝票明細,B.注文コード,B.大盛り区分
FROM テーブルA AS A
INNER JOIN テーブルB AS B
ON A.伝票番号=B.伝票番号
AND B.伝票明細=1

こんな感じでしょうか。

 伝票明細が1から始まらず、「TOP 1」を使うというのであれば

 SELECT A.伝票番号,A.顧客コード,B.伝票明細,B.注文コード,B.大盛り区分
FROM テーブルA AS A
INNER JOIN テーブルB AS B
ON A.伝票番号=B.伝票番号
WHERE B.伝票明細=(SELECT TOP 1 C.伝票明細 FROM テーブルB AS C WHERE B.伝票番号=C.伝票番号)

こんな感じになります。この場合EXIST使ったりIN使ったり、細かい事を言えば色々方法はあるかと思います。でも基本的な考えは同じようなものです。

 

 テーブルBの1行目レコードというのを、どう定義するかによって、答えが変わると思います。
 
例えば、テーブルBの伝票明細が1から順にふられる数字であって、その伝票明細=1のものだけ取ってくれば良いというのであれば、SQL文はいたってシンプルになるかと思います。
 
 SELECT A.伝票番号,A.顧客コード,B.伝票明細,B.注文コード,B.大盛り区分
FROM テーブルA AS A
INNER JOIN テーブルB AS B
ON A.伝票番号=B.伝票番号
AND B.伝票明細=1

こんな感じでしょうか。

 伝票明細が1か...続きを読む

QSELECT/別テーブルのレコード数も取得したい

■環境
・MySQL

■前提
・テーブルA … idカラム
・テーブルB … A_idカラム

■やりたいこと
・テーブルAデータを取得する際、テーブルAレコードに応じて、テーブルB「A_idカラム」の数(レコード数)も取得したい

■取得イメージ例
・テーブルA「全カラム」、「count」カラム
※「count」カラム … テーブルBにある「A_idカラム」の数

■知りたいこと
・どこにも存在しないこの「count」カラムはどうやって作成したら良いでしょうか?
・全体のSQL文

Aベストアンサー

私が思いついたのは、結合して GROUP BY を使う方法とSELECT句内のサブクエリを使う方法でした。
version 5.5.8 で確認しました。

■ 結合して GROUP BY を使う方法

SELECT a.id, COUNT(b.A_id) AS count
FROM table_a a
LEFT OUTER JOIN table_b b ON(a.id = b.A_id)
GROUP BY a.id;


■ SELECT句内のスカラサブクエリを使う方法

SELECT
id,
(SELECT COUNT(*) FROM table_b b WHERE a.id = b.A_id) AS count
FROM table_a a;

参考URL:http://gihyo.jp/dev/serial/01/sql_academy2/001003

QSQL Left Join で重複を排除するには

Left Joinで左側のテーブルをもとに右側のテーブルを連結したいのですが、右側のテーブルに重複データが存在します。そこで、重複してしいる右側のデータの任意の一つだけを結合したいのですが、どのようにしたらよいでしょうか?
例えば以下の様な内容です。
テーブルA
商品 数量
-----------------------
商品1 10
商品2 20
商品3 30

テーブルB
商品  単価  日付
-----------------------
商品1 100 8/10
商品1 110 8/11
商品3 200 8/12

これらのテーブルを結合して以下のようなデータを取得したいのですが、同のようなSQL文を作成したらよいのでしょうか?

検索結果
商品 数量 単価 日付
-----------------------
商品1 10 100 8/10
商品2 20 NULL NULL
商品3 30 200 8/12

商品1はテーブルBに重複したデータがありますが、いずれか一つを選択できればOKです。
商品2はテーブルBにデータがありませんので、単価と日付はNULLでOKです。

Left Joinで左側のテーブルをもとに右側のテーブルを連結したいのですが、右側のテーブルに重複データが存在します。そこで、重複してしいる右側のデータの任意の一つだけを結合したいのですが、どのようにしたらよいでしょうか?
例えば以下の様な内容です。
テーブルA
商品 数量
-----------------------
商品1 10
商品2 20
商品3 30

テーブルB
商品  単価  日付
-----------------------
商品1 100 8/10
商品1 110 8/11
商品3 200 8/12

これらのテーブル...続きを読む

Aベストアンサー

SQLSERVERということであれば、LEFT JOINの代わりにOUTER APPLYを使ってクエリを簡単にする手もあります。
速度はデータにもよりますが、LEFT JOINより早いようですね。
相対コスト(LEFT JOIN 82:OUTER APPLY 18)

DECLARE @TA TABLE (id INT NOT NULL PRIMARY KEY,item VARCHAR(30),quantity INT)
INSERT INTO @TA VALUES(1,'商品1',10),(2,'商品2',20),(3,'商品3',30)
DECLARE @TB TABLE (id INT NOT NULL PRIMARY KEY,item VARCHAR(30),price INT,saledate DATE)
INSERT INTO @TB VALUES(100,'商品1',100,'2016-08-10'),(101,'商品1',110,'2016-08-11'),(102,'商品3',200,'2016-08-12')

SELECT TA.item,TA.quantity,TB.price,TB.saledate FROM @TA as TA
OUTER APPLY(
SELECT TOP 1 * FROM @TB as TB
WHERE TA.item = TB.item
ORDER BY TB.id
) as TB

SQLSERVERということであれば、LEFT JOINの代わりにOUTER APPLYを使ってクエリを簡単にする手もあります。
速度はデータにもよりますが、LEFT JOINより早いようですね。
相対コスト(LEFT JOIN 82:OUTER APPLY 18)

DECLARE @TA TABLE (id INT NOT NULL PRIMARY KEY,item VARCHAR(30),quantity INT)
INSERT INTO @TA VALUES(1,'商品1',10),(2,'商品2',20),(3,'商品3',30)
DECLARE @TB TABLE (id INT NOT NULL PRIMARY KEY,item VARCHAR(30),price INT,saledate DATE)
INSERT INTO @TB VALUES(100,'商品1',100,'2...続きを読む


人気Q&Aランキング