サブクエリの書き方

質問

受注T
----------------
製品CD　　得意先CD　　数量　　単価　　受注日
0001　　2001　　500　　3000　　2013/7/5
0002　　2002　　1000　　NULL　　2013/9/25
0003　　2003　　1800　　NULL　　2013/9/25

単価M
----------------
製品CD　　得意先CD　　数量FROM　　数量TO　　単価　　適用日
0001　　2001　　0　　99999　　3000　　2013/5/1

0002　　2002　　0　　　　500　　　800　　2013/5/1
0002　　2002　　501　　999999　　500　　2013/5/1

0003　　2003　　0　　　500　　　　2000　　2013/1/1
0003　　2003　　501　　999999　　1800　　　2013/1/1
0003　　2003　　0　　　500　　　　2500　　2013/9/20

というデータがあります。
受注Tの単価部分にデータがあるものとないものがあります。
受注時に単価Mから照合して単価を入れるのですが、受注時にまだ単価Mにない場合はNULLが
入っています。
そこで後日VIEWとして単価照合Vを作りたいです。
単価照合Vを作ったときにも単価MになければNULLのままでOKです。

て単価照合V
----------------
製品CD　　得意先CD　　数量　　単価　　受注日
0001　　2001　　500　　3000　　2013/9/27
0002　　2002　　1000　　500　　2013/9/25
0003　　2003　　2000　　1800　　2013/9/25
※2013/9/20に単価更新がありますが条件数量は0-500なので2013/1/1が適用

こんな感じです。

わからないのは受注数量、受注日と単価Mの照合です。
サブクエリを使えばSQLで１行に書けるのでしょうか？
お願いします。

回答No.2

root139 · ベストアンサー

受注Tの単価がNULLの場合だけ単価Mから単価を引っぱってきたいという事ですよね？

単価Mの抽出条件としては、製品CD・得意先CDが受注Tと同じで、数量FROMと数量TOの範囲が受注Tの数量を含んでおり、適用日が受注Tの受注日以前で最新のものとなります。

例) ------------------------------------------
SELECT
　製品CD,
　得意先CD,
　数量,
　COALESCE(
　　単価,
　　(SELECT TOP 1 単価 FROM 単価M TK WHERE
　　　JC.製品CD = TK.製品CD AND JC.得意先CD = TK.得意先CD AND
　　　JC.数量 BETWEEN TK.数量FROM AND TK.数量TO AND
　　　TK.適用日 <= JC.受注日
　　　ORDER BY TK.適用日 DESC
　　)
　) AS 単価,
　受注日
FROM 受注T JC;
----------------------------------------------

OLAP関数を使えばもう少しスッキリするかも知れません。

お礼

遅くなり申し訳ありません。
できました。
こんな書き方があったんですね。

回答No.4

innoya

こんにちは。

下記の私の回答に少し間違いがありました。
「root139」さんの回答が正しいですね。

大変失礼いたしました。

よろしくお願い致します。

回答No.3

innoya

こんにちは。
下記のSQLのようにしてみてください。
簡単にできると思います。

SELECT A.SIdx, A.TIdx, A.Cnt
, CASE WHEN A.Amount IS NULL THEN B.Amount ELSE A.Amount END AS Amount
, A.RegDate
FROM #TableT A WITH (NOLOCK)
LEFT OUTER JOIN #TableM B WITH (NOLOCK)
ON A.SIdx = B.SIdx AND A.TIdx = B.TIdx
AND A.Cnt >= B.CntFrom AND A.Cnt < B.CntTo

上記のSQLをテストするために質問内容を元に下記のSQLを準備しました。
実際に実行してご確認してください。

-- #### 一時テーブル作成
CREATE TABLE #TableT
(
SIdx varchar(10)
,TIdx varchar(10)
,Cnt int
,Amount int
,RegDate datetime
)
GO

CREATE TABLE #TableM
(
SIdx varchar(10)
,TIdx varchar(10)
,CntFrom int
,CntTo int
,Amount int
,RegDate datetime
)
GO

-- ####　データ登録
INSERT INTO #TableT
SELECT '0001', '2001', 500, 3000, '2013/7/5' UNION ALL
SELECT '0002', '2002', 1000, NULL, '2013/9/25' UNION ALL
SELECT '0003', '2003', 1800, NULL, '2013/9/25'
GO

INSERT INTO #TableM
SELECT '0001', '2001', 0, 99999, 3000, '2013/5/1' UNION ALL
SELECT '0002', '2002', 0, 500, 800, '2013/5/1' UNION ALL
SELECT '0002', '2002', 501, 999999, 500, '2013/5/1' UNION ALL
SELECT '0003', '2003', 0, 500, 2000, '2013/1/1' UNION ALL
SELECT '0003', '2003', 501, 999999, 1800, '2013/1/1' UNION ALL
SELECT '0003', '2003', 0, 500, 2500, '2013/9/20'
GO

-- ####　結果
SELECT A.SIdx, A.TIdx, A.Cnt
, CASE WHEN A.Amount IS NULL THEN B.Amount ELSE A.Amount END AS Amount
, A.RegDate
FROM #TableT A WITH (NOLOCK)
LEFT OUTER JOIN #TableM B WITH (NOLOCK)
ON A.SIdx = B.SIdx AND A.TIdx = B.TIdx
AND A.Cnt >= B.CntFrom AND A.Cnt < B.CntTo

お礼

ありがとうございました。
ODBC経由読み取り専用アカウントなのでテーブル作成ができません。
でもこんなやりかたもあるんですね。

テンポラリテーブルは、削除しなくても勝手に消えるんでしょうか？

回答No.1

めとろいと（@naktak）

SELECT 受注T.製品CD, 受注T.得意先CD, 受注T.数量, NULL, 受注T.受注日
FROM 受注T
WHERE NOT EXISTS (
SELECT *
FROM 単価M
WHERE 受注T.製品CD = 単価M.製品CD
AND 受注T.得意先CD = 単価M.得意先CD
AND 受注T.数量 >= 単価M.数量FROM
AND 受注T.数量 <= 単価M.数量TO
AND 受注T.受注日 >= 単価M.適用日
)
UNION ALL
SELECT 受注T.製品CD, 受注T.得意先CD, 受注T.数量, 単価M.単価, 受注T.受注日
FROM 受注T, 単価M
WHERE 受注T.製品CD = 単価M.製品CD
AND 受注T.得意先CD = 単価M.得意先CD
AND 受注T.数量 >= 単価M.数量FROM
AND 受注T.数量 <= 単価M.数量TO
AND 受注T.受注日 >= 単価.適用日
AND (EXISTS (
SELECT *
FROM 単価M M2
WHERE 受注T.製品CD = M2.製品CD
AND 受注T.得意先CD = M2.得意先CD
AND 受注T.数量 >= M2.数量FROM
AND 受注T.数量 <= M2.数量TO
AND 受注T.受注日 < M2.適用日
) OR
NOT EXISTS (
SELECT *
FROM 単価M M2
WHERE 単価M.製品CD = M2.製品CD
AND 単価M.得意先CD = M2.得意先CD
AND 単価M.数量 >= M2.数量FROM
AND 単価M.数量 <= M2.数量TO
AND 単価M.適用日 < M2.適用日
)
)

こんな感じ?
すごい脊髄反射的な回答になりましたが。

お礼

遅くなり申し訳ございません。
ありがとうございました。