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

■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
		)

犬夜叉さんの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)

こんな感じでどうです？
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