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

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

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

SQL

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

■93196 / inTopicNo.1)  SQL
  
□投稿者/ 犬夜叉 (31回)-(2019/11/27(Wed) 17:01:11)

分類:[.NET 全般] 

以下のようなテーブル[T1],[T2]があり、

[T1]
NO NAME
2, a
1.5, b

[T2]
ID, NAME, NO2
1, a, 1
1, b, 2
2, c, 1
2, d, 2
2, e, 3
2, f, 4
3, g, 1
:
:

以下を求めるにはどうすればよいのでしょうか?
NO=2の場合はIDが2のデータを取り、
NO=1.5の場合はIDが1の後半とIDが2の前半を取ります。


NO NAME ID NAME NO2
2, a, 2, c, 1
2, a, 2, d, 2
1.5, b, 1, b, 2
1.5, b, 2, c, 1
1.5, b, 2, d, 2



引用返信 編集キー/
■93197 / inTopicNo.2)  Re[1]: SQL
□投稿者/ kiku (147回)-(2019/11/27(Wed) 17:19:36)

> NO=2の場合はIDが2のデータを取り、

NOが2のデータをすべて取ると下記のようになります。

[T1の結果]
NO NAME
2, a

IDが2のデータをすべて取ると下記にようになります。

[T2の結果]
ID, NAME, NO2
2, c, 1
2, d, 2
2, e, 3
2, f, 4

[T2]の結果をベースに、[T1]の結果を結合すると、下記のようになります。

[結合結果]
NO, NAME, ID, NAME, NO2
2, a, 2, c, 1
2, a, 2, d, 2
2, a, 2, e, 3
2, a, 2, f, 4

提示頂いている下記情報と比較すると、一致しない。

> NO NAME ID NAME NO2
> 2, a, 2, c, 1
> 2, a, 2, d, 2

何か説明不足な点があると思われます。

>NO=1.5の場合はIDが1の後半とIDが2の前半を取ります。

前半と後半の定義がわからないです。
何か説明不足な点があると思われます。


引用返信 編集キー/
■93199 / inTopicNo.3)  Re[2]: SQL
□投稿者/ kaina (60回)-(2019/11/27(Wed) 19:44:14)
NOに1.5という値が入る設計自体を見直したほうが良いと思います。
引用返信 編集キー/
■93210 / inTopicNo.4)  Re[2]: SQL
□投稿者/ 犬夜叉 (32回)-(2019/11/28(Thu) 09:10:13)
2019/11/28(Thu) 09:12:52 編集(投稿者)
No93197 (kiku さん) に返信

すみません結果は以下のようになります。

NO	NAME	ID	NAME2	NO2
1.5	b	1	b	2
1.5	b	2	c	1
1.5	b	2	d	2
2.0	a	2	c	1
2.0	a	2	d	2
2.0	a	2	e	3
2.0	a	2	f	4


力ずくでやってみると以下のようになりました。
もっと簡単にできればいいのですが方法はありますでしょうか?

WITH
TB1	(NO, NAME)	AS	(
	SELECT	2, 'a'
	UNION SELECT 1.5, 'b'
	),
TB2	(ID, NAME, NO2)	AS	(
	SELECT	1, 'a', 1
	UNION SELECT	1, 'b', 2
	UNION SELECT	2, 'c', 1
	UNION SELECT	2, 'd', 2
	UNION SELECT	2, 'e', 3
	UNION SELECT	2, 'f', 4
	),
TB	AS	(
	SELECT	TB1.NO, TB1.NAME, TB2.ID, TB2.NAME AS NAME2, TB2.NO2
	FROM	TB1
	INNER	JOIN TB2
			ON CEILING(TB1.NO) = TB1.NO AND TB1.NO = TB2.ID
			OR CEILING(TB1.NO) <> TB1.NO AND (TB2.ID = CEILING(TB1.NO) OR TB2.ID = FLOOR(TB1.NO))
	),
TBM	AS	(
	SELECT	*,
			(
			SELECT	MIN(ID)
			FROM	TB AS TB1
			WHERE	TB1.NO = TB.NO
			) AS MINID
			,
			(
			SELECT	MAX(ID)
			FROM	TB AS TB1
			WHERE	TB1.NO = TB.NO
			) AS MAXID
	FROM	TB
	),
TBD	AS	(
	SELECT	*,
			(
			SELECT	COUNT(*)
			FROM	TBM AS TBM1
			WHERE	TBM1.ID = MINID
					AND TBM1.NO = TBM.NO
			) AS MINCNT,
			(
			SELECT	COUNT(*)
			FROM	TBM AS TBM1
			WHERE	TBM1.ID = MAXID
					AND TBM1.NO = TBM.NO
			) AS MAXCNT
	FROM	TBM
	)

SELECT	NO, NAME, ID, NAME2, NO2
FROM	TBD
WHERE	CEILING(TBD.NO) = TBD.NO
		OR CEILING(TBD.NO) <> TBD.NO
		AND	(
		TBD.ID = MINID AND TBD.NO2 >= TBD.MAXCNT / 2
		OR TBD.ID = MAXID AND TBD.NO2 <= TBD.MAXCNT / 2
		)

引用返信 編集キー/
■93214 / inTopicNo.5)  Re[3]: SQL
□投稿者/ たーん (1回)-(2019/11/28(Thu) 11:29:34)
犬夜叉さんのSQLの場合、TB1.NO が 2.5 のときに 4 行取得されますが合っていますか?

パズルのつもりで解いてみましたがいかがでしょう。
(以下のSQLでは TB1.NO が 2.5 のときは 2 行取得です)

WITH
TB1 (NO, NAME) AS (
    SELECT      2  , 'a'
    UNION SELECT 1.5, 'b'
    ),
TB2 (ID, NAME, NO2) AS (
    SELECT          1, 'a', 1
    UNION SELECT    1, 'b', 2
    UNION SELECT    2, 'c', 1
    UNION SELECT    2, 'd', 2
    UNION SELECT    2, 'e', 3
    UNION SELECT    2, 'f', 4
    )

select
    NO,NAME,ID,NAME2,NO2
from
(
    select
        TB1.NO
        ,TB1.NAME
        ,TB2.ID
        ,TB2.NAME AS NAME2
        ,TB2.NO2
        ,MAX(TB2.NO2)OVER(PARTITION BY TB1.NO,TB2.ID) / 2 as BORDER
    from
        TB1
    inner join
        TB2
    on  TB1.NO-1 < TB2.ID and TB2.ID < TB1.NO+1
) T
where
    NO = ID
or  (NO > ID and NO2 >  BORDER)
or  (NO < ID and NO2 <= BORDER)


引用返信 編集キー/
■93232 / inTopicNo.6)  Re[4]: SQL
□投稿者/ 氏 (1回)-(2019/11/28(Thu) 20:19:23)
こんな感じでどうです?
TB2のサイズが大きい場合は真ん中のコメントアウトを戻してください。


WITH
TB1(NO, NAME) AS (
    SELECT 2 , 'a'
    UNION SELECT 1.5, 'b'
    ),
TB2 (ID, NAME, NO2) AS (
    SELECT          1, 'a', 1
    UNION SELECT    1, 'b', 2
    UNION SELECT    2, 'c', 1
    UNION SELECT    2, 'd', 2
    UNION SELECT    2, 'e', 3
    UNION SELECT    2, 'f', 4
    ),
TB2a (ID,ID2, NAME, NO2) AS (
    SELECT ID
         , ID + cume_dist() over (partition by ID order by NO2)
         , NAME
         , NO2
      FROM TB2
--     WHERE ID in (SELECT DISTINCT floor(t1.NO + sub.d) FROM TB1 as t1,(SELECT 0 as d UNION SELECT 0.5) as sub)
    )
SELECT TB1.NO
     , TB1.NAME
     , TB2a.ID
     , TB2a.NAME as NAME2
     , TB2a.NO2
  FROM TB1
 INNER JOIN TB2a
    on TB2a.ID2 > TB1.NO
   and TB2a.ID2 <= TB1.NO + 1

引用返信 編集キー/
■93242 / inTopicNo.7)  Re[4]: SQL
□投稿者/ 犬夜叉 (33回)-(2019/11/29(Fri) 09:58:00)
No93214 (たーん さん) に返信
> 犬夜叉さんのSQLの場合、TB1.NO が 2.5 のときに 4 行取得されますが合っていますか?

間違っていますね。

力ずくなので作ったので見直す気にもなれないです。(^^;)
引用返信 編集キー/

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


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

このトピックに書きこむ