グループ単位で、ある値の存在有無を判別し、重複しないIDを取得したい。
投稿者 魔界の仮面弁士 (社会人)
投稿日時
2017/2/17 10:34:18
> ・テーブル名：Aテーブル
> ID：nchar(6)
> 実施日：nchar(10)
> 業務内容：nvarchar(20)
主キーはどれとどれの組み合わせですか?
> ★以下のようにIDのグループ単位で「訪問」が含まれるIDと「訪問」が含まれないIDを取得したい。
「訪問」限定なら、結果に業務内容の列は不要な気が。
> SQL文でどのよに書けば良いかご教示頂けますと大変たすかります。
【案１】
【案２】
なお上記案は、元データに
INSERT INTO [Aテーブル] VALUES (N'27-001', N'2016/04/07', N'訪問')
が追加された場合、両者の結果が異なることに注意して下さい。
> ID：nchar(6)
> 実施日：nchar(10)
> 業務内容：nvarchar(20)
主キーはどれとどれの組み合わせですか?
> ★以下のようにIDのグループ単位で「訪問」が含まれるIDと「訪問」が含まれないIDを取得したい。
「訪問」限定なら、結果に業務内容の列は不要な気が。
> SQL文でどのよに書けば良いかご教示頂けますと大変たすかります。
【案１】
WITH
[ID_LIST] AS (SELECT DISTINCT [ID] FROM [Aテーブル]),
[DT_LIST] AS (SELECT [ID], [実施日] /*, [業務内容] */ FROM [Aテーブル] WHERE [業務内容] = N'訪問')
SELECT
[ID_LIST].[ID], [実施日] /* , [業務内容] */
FROM
[ID_LIST] LEFT JOIN [DT_LIST] ON [ID_LIST].[ID] = [DT_LIST].[ID]
ORDER BY
[ID_LIST].[ID]
[ID_LIST] AS (SELECT DISTINCT [ID] FROM [Aテーブル]),
[DT_LIST] AS (SELECT [ID], [実施日] /*, [業務内容] */ FROM [Aテーブル] WHERE [業務内容] = N'訪問')
SELECT
[ID_LIST].[ID], [実施日] /* , [業務内容] */
FROM
[ID_LIST] LEFT JOIN [DT_LIST] ON [ID_LIST].[ID] = [DT_LIST].[ID]
ORDER BY
[ID_LIST].[ID]
【案２】
SELECT
[ID]
, MAX(CASE [業務内容] WHEN N'訪問' THEN [実施日] END) AS [実施日]
--, MAX(CASE [業務内容] WHEN N'訪問' THEN [業務内容] END) AS [業務内容]
FROM
[Aテーブル]
GROUP BY
[ID]
ORDER BY
[ID]
[ID]
, MAX(CASE [業務内容] WHEN N'訪問' THEN [実施日] END) AS [実施日]
--, MAX(CASE [業務内容] WHEN N'訪問' THEN [業務内容] END) AS [業務内容]
FROM
[Aテーブル]
GROUP BY
[ID]
ORDER BY
[ID]
なお上記案は、元データに
INSERT INTO [Aテーブル] VALUES (N'27-001', N'2016/04/07', N'訪問')
が追加された場合、両者の結果が異なることに注意して下さい。
投稿者 魔界の仮面弁士 (社会人)
投稿日時
2017/2/17 11:09:05
【案３】
【案４】
同一 ID で複数日の訪問があった場合、結果の ID が重複しないよう、
案４では下記のような結果を返すようにしてみました。
WITH [CTE] AS (
SELECT DISTINCT [ID], [実施日], [業務内容] FROM [Aテーブル] WHERE [業務内容] = N'訪問')
SELECT [ID], [実施日], [業務内容] FROM CTE UNION
SELECT [ID], NULL, NULL FROM [Aテーブル] WHERE NOT EXISTS(
SELECT * FROM [CTE] WHERE [CTE].[ID] = [Aテーブル].[ID])
ORDER BY [ID]
SELECT DISTINCT [ID], [実施日], [業務内容] FROM [Aテーブル] WHERE [業務内容] = N'訪問')
SELECT [ID], [実施日], [業務内容] FROM CTE UNION
SELECT [ID], NULL, NULL FROM [Aテーブル] WHERE NOT EXISTS(
SELECT * FROM [CTE] WHERE [CTE].[ID] = [Aテーブル].[ID])
ORDER BY [ID]
【案４】
SELECT DISTINCT
[Y].[ID], (
SELECT [X].[実施日] + ' ' FROM [Aテーブル] [X]
WHERE [X].[ID] = [Y].[ID]
AND [X].[業務内容] = N'訪問'
ORDER BY [X].[実施日]
FOR XML PATH('')
) AS [実施日]
FROM [Aテーブル] Y
[Y].[ID], (
SELECT [X].[実施日] + ' ' FROM [Aテーブル] [X]
WHERE [X].[ID] = [Y].[ID]
AND [X].[業務内容] = N'訪問'
ORDER BY [X].[実施日]
FOR XML PATH('')
) AS [実施日]
FROM [Aテーブル] Y
同一 ID で複数日の訪問があった場合、結果の ID が重複しないよう、
案４では下記のような結果を返すようにしてみました。
┏━━━┯━━━━━━━━━━━┓
┃ID │実施日 ┃
┣━━━┿━━━━━━━━━━━┫
┃27-001│2016/04/05 2016/04/07 ┃
┠───┼───────────┨
┃27-002│ ┃
┠───┼───────────┨
┃27-003│2016/06/05 ┃
┠───┼───────────┨
┃27-004│ ┃
┗━━━┷━━━━━━━━━━━┛
┃ID │実施日 ┃
┣━━━┿━━━━━━━━━━━┫
┃27-001│2016/04/05 2016/04/07 ┃
┠───┼───────────┨
┃27-002│ ┃
┠───┼───────────┨
┃27-003│2016/06/05 ┃
┠───┼───────────┨
┃27-004│ ┃
┗━━━┷━━━━━━━━━━━┛
投稿者 魔界の仮面弁士 (社会人)
投稿日時
2017/2/17 11:22:01
【案４改】 スペース区切りだと読みにくいので、全角カンマ区切りにしてみました。
SELECT DISTINCT
[ID], REPLACE(
( SELECT [X].[実施日] AS [data()] FROM [Aテーブル] [X]
WHERE [X].[ID] = [Y].[ID]
AND [X].[業務内容] = N'訪問'
ORDER BY [X].[実施日]
FOR XML PATH('')
), ' ', '，') AS [実施日]
FROM [Aテーブル] Y
ORDER BY [ID]
[ID], REPLACE(
( SELECT [X].[実施日] AS [data()] FROM [Aテーブル] [X]
WHERE [X].[ID] = [Y].[ID]
AND [X].[業務内容] = N'訪問'
ORDER BY [X].[実施日]
FOR XML PATH('')
), ' ', '，') AS [実施日]
FROM [Aテーブル] Y
ORDER BY [ID]
『'，'』のところを『CHAR(13)+CHAR(10)』に差し替えれば、改行区切りにもできます。
投稿者 Yamato (社会人)
投稿日時
2017/2/17 17:23:31
魔界の仮面弁士様
お世話になっております。
このようなSQL文が有るとは、勉強になりました！有難うございます。
全て試した結果、【案４改】が素晴らしく、訪問は１回と想定していたところ、実際は複数回
実施している事が判明しました。思慮が足りませんでした。
まさしく以下のような結果となりました。
┏━━━┯━━━━━━━━━━━━━━━━━
┃ID │実施日
┣━━━┿━━━━━━━━━━━━━━━━━
┃27-001│2016/04/07，2016/04/05，2016/03/10
┠───┼─────────────────
┃27-002│
┠───┼─────────────────
┃27-003│2016/06/05
┠───┼─────────────────
┃27-004│
┗━━━┷━━━━━━━━━━━━━━━━━
そこで、Aテーブルから取得した結果をBテーブルにInsertしたいと思います。
訪問日が３回存在したとしても、直近から２回分のみ取得したいです。
厚かましいお願いですが、引き続きご教示いただけますでしょうか。
どうぞ宜しくお願い致します。
【Bテーブル】
------------------------------------
ID 実施日１ 実施日２
------------------------------------
27-001 2016/04/07 2016/04/05
------------------------------------
27-002
------------------------------------
27-003 2016/06/05
------------------------------------
27-004
------------------------------------
お世話になっております。
このようなSQL文が有るとは、勉強になりました！有難うございます。
全て試した結果、【案４改】が素晴らしく、訪問は１回と想定していたところ、実際は複数回
実施している事が判明しました。思慮が足りませんでした。
まさしく以下のような結果となりました。
┏━━━┯━━━━━━━━━━━━━━━━━
┃ID │実施日
┣━━━┿━━━━━━━━━━━━━━━━━
┃27-001│2016/04/07，2016/04/05，2016/03/10
┠───┼─────────────────
┃27-002│
┠───┼─────────────────
┃27-003│2016/06/05
┠───┼─────────────────
┃27-004│
┗━━━┷━━━━━━━━━━━━━━━━━
そこで、Aテーブルから取得した結果をBテーブルにInsertしたいと思います。
訪問日が３回存在したとしても、直近から２回分のみ取得したいです。
厚かましいお願いですが、引き続きご教示いただけますでしょうか。
どうぞ宜しくお願い致します。
【Bテーブル】
------------------------------------
ID 実施日１ 実施日２
------------------------------------
27-001 2016/04/07 2016/04/05
------------------------------------
27-002
------------------------------------
27-003 2016/06/05
------------------------------------
27-004
------------------------------------
投稿者 魔界の仮面弁士 (社会人)
投稿日時
2017/2/17 17:57:36
> 直近から２回分のみ取得したいです。
『直近』の定義は何ですか？
登録されている日付を、ID ごとの降順で上位 2 件拾えばよいのか、
それとも、特定の日付より前（または後）という条件が付くのか…。
> 結果をBテーブルにInsertしたいと思います。
たとえばこんな感じ。細かいところは適宜調整して下さい。
CTE 式(WITH ブロック)の代わりに、副問い合わせを使っても良いかな。
【B テーブルを新規作成】
【既存の B テーブルに追加】
『直近』の定義は何ですか？
登録されている日付を、ID ごとの降順で上位 2 件拾えばよいのか、
それとも、特定の日付より前（または後）という条件が付くのか…。
> 結果をBテーブルにInsertしたいと思います。
たとえばこんな感じ。細かいところは適宜調整して下さい。
CTE 式(WITH ブロック)の代わりに、副問い合わせを使っても良いかな。
【B テーブルを新規作成】
WITH
[ID_LIST] AS (SELECT DISTINCT [ID] FROM [Aテーブル]),
[DT_LIST] AS (
SELECT
[ID]
, [実施日]
, [行番号] = ROW_NUMBER() OVER (PARTITION BY [ID] ORDER BY [実施日])
FROM [Aテーブル]
WHERE [業務内容] = N'訪問'
)
SELECT
[ID_LIST].[ID]
, MAX(CASE [行番号] WHEN 1 THEN [実施日] END) AS [実施日１]
, MAX(CASE [行番号] WHEN 2 THEN [実施日] END) AS [実施日２]
INTO
[Bテーブル]
FROM
[ID_LIST] LEFT JOIN [DT_LIST]
ON [ID_LIST].[ID] = [DT_LIST].[ID]
AND [DT_LIST].[行番号] <= 2
GROUP BY
[ID_LIST].[ID]
[ID_LIST] AS (SELECT DISTINCT [ID] FROM [Aテーブル]),
[DT_LIST] AS (
SELECT
[ID]
, [実施日]
, [行番号] = ROW_NUMBER() OVER (PARTITION BY [ID] ORDER BY [実施日])
FROM [Aテーブル]
WHERE [業務内容] = N'訪問'
)
SELECT
[ID_LIST].[ID]
, MAX(CASE [行番号] WHEN 1 THEN [実施日] END) AS [実施日１]
, MAX(CASE [行番号] WHEN 2 THEN [実施日] END) AS [実施日２]
INTO
[Bテーブル]
FROM
[ID_LIST] LEFT JOIN [DT_LIST]
ON [ID_LIST].[ID] = [DT_LIST].[ID]
AND [DT_LIST].[行番号] <= 2
GROUP BY
[ID_LIST].[ID]
【既存の B テーブルに追加】
WITH
[ID_LIST] AS (SELECT DISTINCT [ID] FROM [Aテーブル]),
[DT_LIST] AS (
SELECT
[ID]
, [実施日]
, [行番号] = ROW_NUMBER() OVER (PARTITION BY [ID] ORDER BY [実施日])
FROM [Aテーブル]
WHERE [業務内容] = N'訪問'
)
INSERT INTO [Bテーブル] ([ID], [実施日１], [実施日２])
SELECT
[ID_LIST].[ID]
, MAX(CASE [行番号] WHEN 1 THEN [実施日] END)
, MAX(CASE [行番号] WHEN 2 THEN [実施日] END)
FROM
[ID_LIST] LEFT JOIN [DT_LIST]
ON [ID_LIST].[ID] = [DT_LIST].[ID]
AND [DT_LIST].[行番号] <= 2
GROUP BY
[ID_LIST].[ID]
[ID_LIST] AS (SELECT DISTINCT [ID] FROM [Aテーブル]),
[DT_LIST] AS (
SELECT
[ID]
, [実施日]
, [行番号] = ROW_NUMBER() OVER (PARTITION BY [ID] ORDER BY [実施日])
FROM [Aテーブル]
WHERE [業務内容] = N'訪問'
)
INSERT INTO [Bテーブル] ([ID], [実施日１], [実施日２])
SELECT
[ID_LIST].[ID]
, MAX(CASE [行番号] WHEN 1 THEN [実施日] END)
, MAX(CASE [行番号] WHEN 2 THEN [実施日] END)
FROM
[ID_LIST] LEFT JOIN [DT_LIST]
ON [ID_LIST].[ID] = [DT_LIST].[ID]
AND [DT_LIST].[行番号] <= 2
GROUP BY
[ID_LIST].[ID]
投稿者 魔界の仮面弁士 (社会人)
投稿日時
2017/2/17 18:57:13
訂正：
今回は日付の降順なので、ROW_NUMBER() のパーティションを
ORDER BY [実施日] から ORDER BY [実施日] DESC に
変更しておいてください。
また、先の SQL では、同日に 2 回の訪問があった場合を考慮していません。
たとえば '27-001' の ID に対して
2016/04/07
2016/04/05
2016/04/07
といった多重訪問の記録があった場合に
『DENSE_RANK() 関数』を使ってみてください。
―――ということで、DENSE_RANK を使ったパターンも組んでみました。
案５が CREATE TABLE 、案６が INSERT だったので、
ついでに今回は INSERT & UPDATE にしてみます。
【案７】
今回は日付の降順なので、ROW_NUMBER() のパーティションを
ORDER BY [実施日] から ORDER BY [実施日] DESC に
変更しておいてください。
また、先の SQL では、同日に 2 回の訪問があった場合を考慮していません。
たとえば '27-001' の ID に対して
2016/04/07
2016/04/05
2016/04/07
といった多重訪問の記録があった場合に
27-001 2016/04/07 2016/04/0727-001 2016/04/07 2016/04/05『DENSE_RANK() 関数』を使ってみてください。
―――ということで、DENSE_RANK を使ったパターンも組んでみました。
案５が CREATE TABLE 、案６が INSERT だったので、
ついでに今回は INSERT & UPDATE にしてみます。
【案７】
WITH
[ID_LIST] AS (SELECT DISTINCT [ID] FROM [Aテーブル]),
[DT_LIST] AS (
SELECT
[ID]
, [実施日]
, [行番号] = DENSE_RANK() OVER (PARTITION BY [ID] ORDER BY [実施日] DESC)
FROM [Aテーブル]
WHERE [業務内容] = N'訪問'
),
[RESULT] AS (
SELECT
[ID_LIST].[ID]
, MAX(CASE [行番号] WHEN 1 THEN [実施日] END) AS [実施日１]
, MAX(CASE [行番号] WHEN 2 THEN [実施日] END) AS [実施日２]
FROM
[ID_LIST] LEFT JOIN [DT_LIST]
ON [ID_LIST].[ID] = [DT_LIST].[ID]
AND [DT_LIST].[行番号] <= 2
GROUP BY
[ID_LIST].[ID]
)
MERGE INTO [Bテーブル]
USING [RESULT]
ON ([Bテーブル].[ID] = [RESULT].[ID])
WHEN MATCHED THEN
UPDATE SET
[実施日１] = [RESULT].[実施日１]
, [実施日２] = [RESULT].[実施日２]
WHEN NOT MATCHED THEN
INSERT ([ID], [実施日１], [実施日２])
VALUES (
[RESULT].[ID]
, [RESULT].[実施日１]
, [RESULT].[実施日２]
)
;
[ID_LIST] AS (SELECT DISTINCT [ID] FROM [Aテーブル]),
[DT_LIST] AS (
SELECT
[ID]
, [実施日]
, [行番号] = DENSE_RANK() OVER (PARTITION BY [ID] ORDER BY [実施日] DESC)
FROM [Aテーブル]
WHERE [業務内容] = N'訪問'
),
[RESULT] AS (
SELECT
[ID_LIST].[ID]
, MAX(CASE [行番号] WHEN 1 THEN [実施日] END) AS [実施日１]
, MAX(CASE [行番号] WHEN 2 THEN [実施日] END) AS [実施日２]
FROM
[ID_LIST] LEFT JOIN [DT_LIST]
ON [ID_LIST].[ID] = [DT_LIST].[ID]
AND [DT_LIST].[行番号] <= 2
GROUP BY
[ID_LIST].[ID]
)
MERGE INTO [Bテーブル]
USING [RESULT]
ON ([Bテーブル].[ID] = [RESULT].[ID])
WHEN MATCHED THEN
UPDATE SET
[実施日１] = [RESULT].[実施日１]
, [実施日２] = [RESULT].[実施日２]
WHEN NOT MATCHED THEN
INSERT ([ID], [実施日１], [実施日２])
VALUES (
[RESULT].[ID]
, [RESULT].[実施日１]
, [RESULT].[実施日２]
)
;
投稿者 Yamato (社会人)
投稿日時
2017/2/19 01:08:06
魔界の仮面弁士様
ご回答有難うございました。
今、検証中ですが、希望している値を取得できそうです。
まずは御礼のみにて失礼いたします。
追伸、「直近」の定義があいまいでしたが、
実施日の降順から1回目、2回目の実施日のみ取得するという意味でした。
ご回答有難うございました。
今、検証中ですが、希望している値を取得できそうです。
まずは御礼のみにて失礼いたします。
追伸、「直近」の定義があいまいでしたが、
実施日の降順から1回目、2回目の実施日のみ取得するという意味でした。
投稿者 Yamato (社会人)
投稿日時
2017/2/19 12:33:04
魔界の仮面弁士様
此度は有難うございました。
お蔭さまで希望する結果を得る事ができました。
SQL文の奥の深さに驚きました。本当に勉強になりました。
今後ともどうぞ宜しくお願い致します。
此度は有難うございました。
お蔭さまで希望する結果を得る事ができました。
SQL文の奥の深さに驚きました。本当に勉強になりました。
今後ともどうぞ宜しくお願い致します。
VB.Net
SQL Server2008
いつも勉強させていただいております。
宜しくお願いいたします。
SQL Server2008に以下のテーブルがあります。
・テーブル名：Aテーブル
ID：nchar(6)
実施日：nchar(10)
業務内容：nvarchar(20)
---------------------------------
ID 実施日 業務内容
---------------------------------
27-001 2016/04/05 訪問
27-001 2016/04/06 電話
27-001 2016/05/01 その他
-----------------------------
27-002 2016/04/06 電話
27-002 2016/05/01 その他
-----------------------------
27-003 2016/06/05 訪問
27-003 2016/06/10 電話
-----------------------------
27-004 2016/04/10 診断
27-004 2016/05/15 電話
-----------------------------
（実現したい事）
★以下のようにIDのグループ単位で「訪問」が含まれるIDと「訪問」が含まれないIDを取得したい。
IDの人のところに、いつ訪問したのか。
IDの人のところに、まだ訪問していないのかを集計したい。
IDは重複しない。
---------------------------------
ID 実施日 業務内容
---------------------------------
27-001 2016/04/05 訪問
27-002
27-003 2016/06/05 訪問
27-004
SQL文でどのよに書けば良いかご教示頂けますと大変たすかります。
宜しくお願い致します。