C# と VB.NET の質問掲示板

ASP.NET、C++/CLI、Java 何でもどうぞ

C# と VB.NET の入門サイト

SQLServer でバインドできません

[トピック内 4 記事 (1 - 4 表示)]  << 0 >>

■89070 / inTopicNo.1)  SQLServer でバインドできません
  
□投稿者/ 夜叉丸 (137回)-(2018/10/30(Tue) 10:54:41)

分類:[.NET 全般] 

2018/10/30(Tue) 12:41:16 編集(投稿者)
日付のデータ:CAL
DATE      PH LABEL
    :
20181001, 1  J1   (平日)
20181002, 1  J2
20181003, 1  J3
20181004, 1  J4
20181005, 1  J5
20181006, 1  J6
20181007, 2  null (休日)
20181008, 2  null
20181009, 1  J7
20181010, 1  J8
    :

予定のデータ:PLN
NAME DAY DATE
    :
A,   5,  20181009
B,   3,  20181005
C,   3,  20181004
    :

があります。
PLNデータのDATEを基準にDAY日分前日の日付とラベルをとりたいので
以下のようにしました。

SELECT	PLN.NAME, CAL.DATE, CAL.LABEL
FROM	PLN
LEFT	OUTER JOIN (
	SELECT	DATE, LABEL, ROW_NUMBER() OVER(ORDER BY DATE DESC) AS RNUM
	FROM	CAL
	WHERE	PH = 1
		AND DATE <= PLN.DATE  ←バインドできない
	) AS CAL
		ON CAL.RNUM = PLN.DAY

バインドさせる方法はないのでしょうか?
また、このような場合はどうするのがいいのでしょうか?

引用返信 編集キー/
■89073 / inTopicNo.2)  Re[1]: SQLServer でバインドできません
□投稿者/ 魔界の仮面弁士 (1902回)-(2018/10/30(Tue) 11:26:27)
2018/10/30(Tue) 11:34:17 編集(投稿者)

No89070 (夜叉丸 さん) に返信
> NAME DAY DATE

SQL Server において、DAY や DATE といった列名を使うことは推奨されません。
別の名前に変更することをお奨めします。

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/reserved-keywords-transact-sql?view=sql-server-2017
>> 将来の SQL Server のリリースで新しい機能が実装されたときに予約される可能性があります。
>> これらの語はできるだけ識別子として使用しないでください。

今からの名前の変更が難しい場合には、
 SELECT [CAL].[DATE]
のように、識別子の両端をブラケットで囲って指定するようにします。



> AND DATE <= PLN.DATE  ←バインドできない
左辺の DATE が、CAL.DATE と PLN.DATE の間で曖昧になりますが、
それ以前の問題として、提示いただいたデータを見る限りでは、
 20181005 … CAL.DATE は 8 桁
 2018105  … PLN.DATE は 7 桁
という点にも問題がありそう。


> このような場合はどうするのがいいのでしょうか?
PLN.DATE の絞り込み方法を、PLN.DAY と同様の構文に置き換えるとか。


LEFT OUTER JOIN (SELECT 〜 FROM [CAL] WHERE [CAL].[PH]=1) AS [W]
ON [W].[RNUM] = [PLN].[DAY] AND [W].[DATE] <= [PLN].[DATE]
引用返信 編集キー/
■89076 / inTopicNo.3)  Re[2]: SQLServer でバインドできません
□投稿者/ 夜叉丸 (138回)-(2018/10/30(Tue) 13:02:49)
No89073 (魔界の仮面弁士 さん) に返信

>>AND DATE <= PLN.DATE  ←バインドできない
> 左辺の DATE が、CAL.DATE と PLN.DATE の間で曖昧になりますが、
> それ以前の問題として、提示いただいたデータを見る限りでは、
>  20181005 … CAL.DATE は 8 桁
>  2018105  … PLN.DATE は 7 桁
> という点にも問題がありそう。
すみません。表記間違いました。

> PLN.DATE の絞り込み方法を、PLN.DAY と同様の構文に置き換えるとか。
> LEFT OUTER JOIN (SELECT 〜 FROM [CAL] WHERE [CAL].[PH]=1) AS [W]
> ON [W].[RNUM] = [PLN].[DAY] AND [W].[DATE] <= [PLN].[DATE]

結果が変わってしまいます。

A 20181004 J4
B NULL   NULL
C NULL   NULL

欲しい結果は以下になります
A 20181002 J02
B 20181003 J03
C 20181002 J02

SQL文は以下のようにしています。

SELECT	*
INTO	#CAL
FROM	(
		SELECT 20181001 AS DATE, 1 AS PH, 'J1' AS LABEL
		UNION SELECT 20181002, 1,  'J2'
		UNION SELECT 20181003, 1,  'J3'
		UNION SELECT 20181004, 1,  'J4'
		UNION SELECT 20181005, 1,  'J5'
		UNION SELECT 20181006, 1,  'J6'
		UNION SELECT 20181007, 2,  null
		UNION SELECT 20181008, 2,  null
		UNION SELECT 20181009, 1,  'J7'
		UNION SELECT 20181010, 1,  'J8'
		) AS CAL

SELECT	*
INTO	#PLN
FROM	(
		SELECT 'A' AS NAME,   5 AS DAY,    20181009 AS DATE
		UNION SELECT 'B', 3, 20181005
		UNION SELECT 'C', 3, 20181004
		) AS PLN;

SELECT	#PLN.NAME, CAL.DATE, CAL.LABEL
FROM	#PLN
LEFT	OUTER JOIN (
		SELECT	DATE, LABEL, ROW_NUMBER() OVER(ORDER BY DATE DESC) AS RNUM
		FROM	#CAL
		WHERE	#CAL.PH = 1
--				AND DATE <= #PLN.DATE
		) AS CAL
			ON CAL.RNUM = #PLN.DAY
			AND CAL.DATE <= #PLN.DATE

DROP TABLE #CAL;
DROP TABLE #PLN;


引用返信 編集キー/
■89080 / inTopicNo.4)  Re[3]: SQLServer でバインドできません
□投稿者/ 魔界の仮面弁士 (1906回)-(2018/10/30(Tue) 16:01:04)
No89076 (夜叉丸 さん) に返信
> 予定のデータ:PLN
> A,   5,  20181009
> B,   3,  20181005
> C,   3,  20181004
>
> 欲しい結果は以下になります
> A 20181002 J02
> B 20181003 J03
> C 20181002 J02

えぇと…。PH=2 は除外して、
稼働日である PH=1 のみをカウントするのですよね。

結果が J2 ではなく J02 に変化している点には目をつぶるとしても、まだよくわかりません。

A (20181009) の 5 営業日前が、20181002 であるのは分かったのですが、
B (20181005) の 3 営業日前が、20181002 ではなく 20181003 なのは何故でしょうか?
C (20181004) の 3 営業日前が、20181001 ではなく 20181002 なのは何故でしょうか?

当日を DAY=0 ではなく、DAY=1 としてカウントする仕様だったと考えれば
B と C の結果が合致するのですが、その場合は A が 20181003 になるはず…。


> 欲しい結果は以下になります
> A 20181002 J02
> B 20181003 J03
> C 20181002 J02

LAG 関数だとどうでしょう。(手元に SQL Server 2012 以降が無いので未確認)


WITH [CTE] AS (
  SELECT 
    [PLN].[NAME]
  , [PLN].[DAY]
  , [PLN].[DATE] AS [PLN_DATE]
  , [CAL].[DATE] AS [CAL_DATE]
  , [CAL].[LABEL]
  , LAG([CAL].[DATE] , [PLN].[DAY]) OVER (PARTITION BY [PLN].[NAME] ORDER BY [CAL].[DATE]) AS [LAG_DATE]
  , LAG([CAL].[LABEL], [PLN].[DAY]) OVER (PARTITION BY [PLN].[NAME] ORDER BY [CAL].[DATE]) AS [LAG_LABEL]
FROM
  [CAL] LEFT JOIN [PLN] ON [CAL].[DATE] <= [PLN].[DATE]
WHERE
  [CAL].[PH] = 1 AND [PLN].[NAME] IS NOT NULL
)
SELECT [NAME], [LAG_DATE], [LAG_LABEL] FROM [CTE]
WHERE [PLN_DATE] = [CAL_DATE]
ORDER BY [NAME]


/*
『WITH CTE AS (SELECT〜FROM〜WHERE〜) SELECT〜FROM CTE』を
『SELECT〜FROM (SELECT〜FROM〜WHERE〜) CTE WHERE 〜』にしても動きそう
*/

引用返信 編集キー/

このトピックをツリーで一括表示


トピック内ページ移動 / << 0 >>

このトピックに書きこむ