SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE [m_option](
    [option_id] [int] NOT NULL,
    [option_name] [nchar](50) NULL,
 CONSTRAINT [PK_m_option] PRIMARY KEY CLUSTERED 
(
    [option_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
;
CREATE TABLE [m_shohin](
    [shohin_id] [int] NOT NULL,
    [shohin_name] [nchar](50) NULL,
 CONSTRAINT [PK_m_shohin] PRIMARY KEY CLUSTERED 
(
    [shohin_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
;
CREATE TABLE [m_shohin_shubetsu](
    [shohin_id] [int] NOT NULL,
    [shubetsu_id] [int] NOT NULL,
 CONSTRAINT [PK_m_shohin_syubetu] PRIMARY KEY CLUSTERED 
(
    [shohin_id] ASC,
    [shubetsu_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
;
CREATE TABLE [m_shubetsu](
    [shubetsu_id] [int] NOT NULL,
    [shubetsu_name] [nchar](50) NULL,
 CONSTRAINT [PK_m_syubetu] PRIMARY KEY CLUSTERED 
(
    [shubetsu_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
;
CREATE TABLE [m_waribiki](
    [waribiki_id] [int] NOT NULL,
    [waribiki_name] [nchar](50) NULL,
    [waribiki_ritsu] [int] NULL,
 CONSTRAINT [PK_m_waribiki] PRIMARY KEY CLUSTERED 
(
    [waribiki_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
;
CREATE TABLE [m_waribiki_option](
    [waribiki_id] [int] NOT NULL,
    [option_id] [int] NOT NULL,
 CONSTRAINT [PK_m_warbiki_option] PRIMARY KEY CLUSTERED 
(
    [waribiki_id] ASC,
    [option_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
;
CREATE TABLE [m_waribiki_shubetsu](
    [waribiki_id] [int] NOT NULL,
    [shubetsu_id] [int] NOT NULL,
 CONSTRAINT [PK_m_waribiki_syubetu] PRIMARY KEY CLUSTERED 
(
    [waribiki_id] ASC,
    [shubetsu_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
;
CREATE TABLE [t_uriage](
    [denp_no] [int] NOT NULL,
    [det_no] [int] NOT NULL,
    [shohin_id] [int] NULL,
    [su] [int] NULL,
 CONSTRAINT [PK_t_uriage] PRIMARY KEY CLUSTERED 
(
    [denp_no] ASC,
    [det_no] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
;
CREATE TABLE [t_uriage_option](
    [denp_no] [int] NOT NULL,
    [det_no] [int] NOT NULL,
    [option_id] [int] NOT NULL,
 CONSTRAINT [PK_t_uriage_option] PRIMARY KEY CLUSTERED 
(
    [denp_no] ASC,
    [det_no] ASC,
    [option_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
;
INSERT [m_option] ([option_id], [option_name]) VALUES (1, N'3営業日以降出荷                                          ')
;
INSERT [m_option] ([option_id], [option_name]) VALUES (2, N'包装紙無し                                             ')
;
INSERT [m_shohin] ([shohin_id], [shohin_name]) VALUES (1, N'明治チョコレート                                          ')
;
INSERT [m_shohin] ([shohin_id], [shohin_name]) VALUES (2, N'グリコチョコレート                                         ')
;
INSERT [m_shohin] ([shohin_id], [shohin_name]) VALUES (3, N'グリコクッキー                                           ')
;
INSERT [m_shohin] ([shohin_id], [shohin_name]) VALUES (4, N'明治チョコ&クッキーセット                                     ')
;
INSERT [m_shohin_shubetsu] ([shohin_id], [shubetsu_id]) VALUES (1, 1)
;
INSERT [m_shohin_shubetsu] ([shohin_id], [shubetsu_id]) VALUES (1, 3)
;
INSERT [m_shohin_shubetsu] ([shohin_id], [shubetsu_id]) VALUES (2, 2)
;
INSERT [m_shohin_shubetsu] ([shohin_id], [shubetsu_id]) VALUES (2, 3)
;
INSERT [m_shohin_shubetsu] ([shohin_id], [shubetsu_id]) VALUES (3, 2)
;
INSERT [m_shohin_shubetsu] ([shohin_id], [shubetsu_id]) VALUES (3, 4)
;
INSERT [m_shohin_shubetsu] ([shohin_id], [shubetsu_id]) VALUES (4, 1)
;
INSERT [m_shohin_shubetsu] ([shohin_id], [shubetsu_id]) VALUES (4, 3)
;
INSERT [m_shohin_shubetsu] ([shohin_id], [shubetsu_id]) VALUES (4, 4)
;
INSERT [m_shubetsu] ([shubetsu_id], [shubetsu_name]) VALUES (1, N'明治                                                ')
;
INSERT [m_shubetsu] ([shubetsu_id], [shubetsu_name]) VALUES (2, N'グリコ                                               ')
;
INSERT [m_shubetsu] ([shubetsu_id], [shubetsu_name]) VALUES (3, N'チョコレート                                            ')
;
INSERT [m_shubetsu] ([shubetsu_id], [shubetsu_name]) VALUES (4, N'クッキー                                              ')
;
INSERT [m_waribiki] ([waribiki_id], [waribiki_name], [waribiki_ritsu]) VALUES (1, N'チョコレート割                                           ', 10)
;
INSERT [m_waribiki] ([waribiki_id], [waribiki_name], [waribiki_ritsu]) VALUES (2, N'包装紙無し割引                                           ', 5)
;
INSERT [m_waribiki] ([waribiki_id], [waribiki_name], [waribiki_ritsu]) VALUES (3, N'3営業日以降出荷 割引(明治チョコ限定)                              ', 3)
;
INSERT [m_waribiki] ([waribiki_id], [waribiki_name], [waribiki_ritsu]) VALUES (4, N'3営業日以降出荷 割引+包装紙無し割引(チョコレート限定)                     ', 5)
;
INSERT [m_waribiki] ([waribiki_id], [waribiki_name], [waribiki_ritsu]) VALUES (5, N'3営業日以降出荷 割引+包装紙無し割引(グリコクッキー限定)                       ', 5)
;
INSERT [m_waribiki_option] ([waribiki_id], [option_id]) VALUES (2, 2)
;
INSERT [m_waribiki_option] ([waribiki_id], [option_id]) VALUES (3, 1)
;
INSERT [m_waribiki_option] ([waribiki_id], [option_id]) VALUES (4, 1)
;
INSERT [m_waribiki_option] ([waribiki_id], [option_id]) VALUES (4, 2)
;
INSERT [m_waribiki_option] ([waribiki_id], [option_id]) VALUES (5, 1)
;
INSERT [m_waribiki_option] ([waribiki_id], [option_id]) VALUES (5, 2)
;
INSERT [m_waribiki_shubetsu] ([waribiki_id], [shubetsu_id]) VALUES (1, 3)
;
INSERT [m_waribiki_shubetsu] ([waribiki_id], [shubetsu_id]) VALUES (3, 1)
;
INSERT [m_waribiki_shubetsu] ([waribiki_id], [shubetsu_id]) VALUES (3, 3)
;
INSERT [m_waribiki_shubetsu] ([waribiki_id], [shubetsu_id]) VALUES (4, 3)
;
INSERT [m_waribiki_shubetsu] ([waribiki_id], [shubetsu_id]) VALUES (5, 2)
;
INSERT [m_waribiki_shubetsu] ([waribiki_id], [shubetsu_id]) VALUES (5, 4)
;
INSERT [t_uriage] ([denp_no], [det_no], [shohin_id], [su]) VALUES (1, 1, 1, 5)
;
INSERT [t_uriage] ([denp_no], [det_no], [shohin_id], [su]) VALUES (2, 1, 3, 5)
;
INSERT [t_uriage] ([denp_no], [det_no], [shohin_id], [su]) VALUES (3, 1, 1, 5)
;
INSERT [t_uriage] ([denp_no], [det_no], [shohin_id], [su]) VALUES (4, 1, 3, 5)
;
INSERT [t_uriage] ([denp_no], [det_no], [shohin_id], [su]) VALUES (5, 1, 3, 5)
;
INSERT [t_uriage] ([denp_no], [det_no], [shohin_id], [su]) VALUES (5, 2, 1, 10)
;
INSERT [t_uriage_option] ([denp_no], [det_no], [option_id]) VALUES (2, 1, 2)
;
INSERT [t_uriage_option] ([denp_no], [det_no], [option_id]) VALUES (3, 1, 2)
;
INSERT [t_uriage_option] ([denp_no], [det_no], [option_id]) VALUES (4, 1, 1)
;
INSERT [t_uriage_option] ([denp_no], [det_no], [option_id]) VALUES (4, 1, 2)
;
INSERT [t_uriage_option] ([denp_no], [det_no], [option_id]) VALUES (5, 1, 1)
;
INSERT [t_uriage_option] ([denp_no], [det_no], [option_id]) VALUES (5, 1, 2)
;
INSERT [t_uriage_option] ([denp_no], [det_no], [option_id]) VALUES (5, 2, 1)
;
INSERT [t_uriage_option] ([denp_no], [det_no], [option_id]) VALUES (5, 2, 2)
;
50 rows affected

SELECT * FROM
    t_uriage u
    LEFT OUTER JOIN t_uriage_option uo
        ON u.denp_no=uo.denp_no AND u.det_no=uo.det_no
    INNER JOIN m_shohin_shubetsu ss
        ON u.shohin_id=ss.shohin_id


denp_no
det_no
shohin_id
su
denp_no
det_no
option_id
shohin_id
shubetsu_id


1
1
1
5
null
null
null
1
1

1
1
1
5
null
null
null
1
3

2
1
3
5
2
1
2
3
2

2
1
3
5
2
1
2
3
4

3
1
1
5
3
1
2
1
1

3
1
1
5
3
1
2
1
3

4
1
3
5
4
1
1
3
2

4
1
3
5
4
1
2
3
2

4
1
3
5
4
1
1
3
4

4
1
3
5
4
1
2
3
4

5
1
3
5
5
1
1
3
2

5
1
3
5
5
1
2
3
2

5
1
3
5
5
1
1
3
4

5
1
3
5
5
1
2
3
4

5
2
1
10
5
2
1
1
1

5
2
1
10
5
2
2
1
1

5
2
1
10
5
2
1
1
3

5
2
1
10
5
2
2
1
3


SELECT * FROM
    m_waribiki w
    LEFT OUTER JOIN m_waribiki_option wo
        ON w.waribiki_id=wo.waribiki_id
    LEFT OUTER JOIN m_waribiki_shubetsu ws 
        ON w.waribiki_id=ws.waribiki_id;


waribiki_id
waribiki_name
waribiki_ritsu
waribiki_id
option_id
waribiki_id
shubetsu_id


1
チョコレート割
10
null
null
1
3

2
包装紙無し割引
5
2
2
null
null

3
3営業日以降出荷 割引(明治チョコ限定)
3
3
1
3
1

3
3営業日以降出荷 割引(明治チョコ限定)
3
3
1
3
3

4
3営業日以降出荷 割引+包装紙無し割引(チョコレート限定)
5
4
1
4
3

4
3営業日以降出荷 割引+包装紙無し割引(チョコレート限定)
5
4
2
4
3

5
3営業日以降出荷 割引+包装紙無し割引(グリコクッキー限定)
5
5
1
5
2

5
3営業日以降出荷 割引+包装紙無し割引(グリコクッキー限定)
5
5
1
5
4

5
3営業日以降出荷 割引+包装紙無し割引(グリコクッキー限定)
5
5
2
5
2

5
3営業日以降出荷 割引+包装紙無し割引(グリコクッキー限定)
5
5
2
5
4


SELECT * FROM
(
SELECT
	u.denp_no,
	u.det_no,
	u.shohin_id,
	u.su,
	uo.option_id,
	ss.shubetsu_id
FROM
    t_uriage u
    LEFT OUTER JOIN t_uriage_option uo
        ON u.denp_no=uo.denp_no AND u.det_no=uo.det_no
    INNER JOIN m_shohin_shubetsu ss
        ON u.shohin_id=ss.shohin_id
) uu
LEFT OUTER JOIN
(
SELECT
	w.waribiki_id,
	w.waribiki_name,
	w.waribiki_ritsu,
	wo.option_id,
	ws.shubetsu_id
FROM
    m_waribiki w
    LEFT OUTER JOIN m_waribiki_option wo
        ON w.waribiki_id=wo.waribiki_id
    LEFT OUTER JOIN m_waribiki_shubetsu ws 
        ON w.waribiki_id=ws.waribiki_id
) ww
ON uu.option_id IS NULL AND ww.option_id IS NULL AND uu.shubetsu_id=ww.shubetsu_id OR
   uu.option_id=ww.option_id AND ww.shubetsu_id IS NULL OR
   uu.option_id=ww.option_id AND uu.shubetsu_id=ww.shubetsu_id
;


denp_no
det_no
shohin_id
su
option_id
shubetsu_id
waribiki_id
waribiki_name
waribiki_ritsu
option_id
shubetsu_id


1
1
1
5
null
1
null
null
null
null
null

1
1
1
5
null
3
1
チョコレート割
10
null
3

2
1
3
5
2
2
2
包装紙無し割引
5
2
null

2
1
3
5
2
2
5
3営業日以降出荷 割引+包装紙無し割引(グリコクッキー限定)
5
2
2

2
1
3
5
2
4
2
包装紙無し割引
5
2
null

2
1
3
5
2
4
5
3営業日以降出荷 割引+包装紙無し割引(グリコクッキー限定)
5
2
4

3
1
1
5
2
1
2
包装紙無し割引
5
2
null

3
1
1
5
2
3
2
包装紙無し割引
5
2
null

3
1
1
5
2
3
4
3営業日以降出荷 割引+包装紙無し割引(チョコレート限定)
5
2
3

4
1
3
5
1
2
5
3営業日以降出荷 割引+包装紙無し割引(グリコクッキー限定)
5
1
2

4
1
3
5
2
2
2
包装紙無し割引
5
2
null

4
1
3
5
2
2
5
3営業日以降出荷 割引+包装紙無し割引(グリコクッキー限定)
5
2
2

4
1
3
5
1
4
5
3営業日以降出荷 割引+包装紙無し割引(グリコクッキー限定)
5
1
4

4
1
3
5
2
4
2
包装紙無し割引
5
2
null

4
1
3
5
2
4
5
3営業日以降出荷 割引+包装紙無し割引(グリコクッキー限定)
5
2
4

5
1
3
5
1
2
5
3営業日以降出荷 割引+包装紙無し割引(グリコクッキー限定)
5
1
2

5
1
3
5
2
2
2
包装紙無し割引
5
2
null

5
1
3
5
2
2
5
3営業日以降出荷 割引+包装紙無し割引(グリコクッキー限定)
5
2
2

5
1
3
5
1
4
5
3営業日以降出荷 割引+包装紙無し割引(グリコクッキー限定)
5
1
4

5
1
3
5
2
4
2
包装紙無し割引
5
2
null

5
1
3
5
2
4
5
3営業日以降出荷 割引+包装紙無し割引(グリコクッキー限定)
5
2
4

5
2
1
10
1
1
3
3営業日以降出荷 割引(明治チョコ限定)
3
1
1

5
2
1
10
2
1
2
包装紙無し割引
5
2
null

5
2
1
10
1
3
3
3営業日以降出荷 割引(明治チョコ限定)
3
1
3

5
2
1
10
1
3
4
3営業日以降出荷 割引+包装紙無し割引(チョコレート限定)
5
1
3

5
2
1
10
2
3
2
包装紙無し割引
5
2
null

5
2
1
10
2
3
4
3営業日以降出荷 割引+包装紙無し割引(チョコレート限定)
5
2
3


SELECT
    u.denp_no,
    u.det_no,
    u.shohin_id,
    u.su,
    uo.option_id,
    us.shubetsu_id,
    w.waribiki_id,
    w.waribiki_name,
    w.waribiki_ritsu,
    wo.option_id,
    ws.shubetsu_id
FROM
    t_uriage u
    LEFT OUTER JOIN t_uriage_option uo
        ON u.denp_no=uo.denp_no AND u.det_no=uo.det_no
    INNER JOIN m_shohin_shubetsu us
        ON u.shohin_id=us.shohin_id
    LEFT OUTER JOIN m_waribiki w
        LEFT OUTER JOIN m_waribiki_option wo
            ON w.waribiki_id=wo.waribiki_id
        LEFT OUTER JOIN m_waribiki_shubetsu ws 
            ON w.waribiki_id=ws.waribiki_id
    ON wo.option_id IS NULL AND us.shubetsu_id=ws.shubetsu_id OR
       uo.option_id=wo.option_id AND ws.shubetsu_id IS NULL OR
       uo.option_id=wo.option_id AND us.shubetsu_id=ws.shubetsu_id
;


denp_no
det_no
shohin_id
su
option_id
shubetsu_id
waribiki_id
waribiki_name
waribiki_ritsu
option_id
shubetsu_id


1
1
1
5
null
1
null
null
null
null
null

1
1
1
5
null
3
1
チョコレート割
10
null
3

2
1
3
5
2
2
2
包装紙無し割引
5
2
null

2
1
3
5
2
2
5
3営業日以降出荷 割引+包装紙無し割引(グリコクッキー限定)
5
2
2

2
1
3
5
2
4
2
包装紙無し割引
5
2
null

2
1
3
5
2
4
5
3営業日以降出荷 割引+包装紙無し割引(グリコクッキー限定)
5
2
4

3
1
1
5
2
1
2
包装紙無し割引
5
2
null

3
1
1
5
2
3
1
チョコレート割
10
null
3

3
1
1
5
2
3
2
包装紙無し割引
5
2
null

3
1
1
5
2
3
4
3営業日以降出荷 割引+包装紙無し割引(チョコレート限定)
5
2
3

4
1
3
5
1
2
5
3営業日以降出荷 割引+包装紙無し割引(グリコクッキー限定)
5
1
2

4
1
3
5
2
2
2
包装紙無し割引
5
2
null

4
1
3
5
2
2
5
3営業日以降出荷 割引+包装紙無し割引(グリコクッキー限定)
5
2
2

4
1
3
5
1
4
5
3営業日以降出荷 割引+包装紙無し割引(グリコクッキー限定)
5
1
4

4
1
3
5
2
4
2
包装紙無し割引
5
2
null

4
1
3
5
2
4
5
3営業日以降出荷 割引+包装紙無し割引(グリコクッキー限定)
5
2
4

5
1
3
5
1
2
5
3営業日以降出荷 割引+包装紙無し割引(グリコクッキー限定)
5
1
2

5
1
3
5
2
2
2
包装紙無し割引
5
2
null

5
1
3
5
2
2
5
3営業日以降出荷 割引+包装紙無し割引(グリコクッキー限定)
5
2
2

5
1
3
5
1
4
5
3営業日以降出荷 割引+包装紙無し割引(グリコクッキー限定)
5
1
4

5
1
3
5
2
4
2
包装紙無し割引
5
2
null

5
1
3
5
2
4
5
3営業日以降出荷 割引+包装紙無し割引(グリコクッキー限定)
5
2
4

5
2
1
10
1
1
3
3営業日以降出荷 割引(明治チョコ限定)
3
1
1

5
2
1
10
2
1
2
包装紙無し割引
5
2
null

5
2
1
10
1
3
1
チョコレート割
10
null
3

5
2
1
10
1
3
3
3営業日以降出荷 割引(明治チョコ限定)
3
1
3

5
2
1
10
1
3
4
3営業日以降出荷 割引+包装紙無し割引(チョコレート限定)
5
1
3

5
2
1
10
2
3
1
チョコレート割
10
null
3

5
2
1
10
2
3
2
包装紙無し割引
5
2
null

5
2
1
10
2
3
4
3営業日以降出荷 割引+包装紙無し割引(チョコレート限定)
5
2
3


SELECT
    u.denp_no,
    u.det_no,
    w.waribiki_id,
    COUNT(DISTINCT uo.option_id) AS option_num,
    COUNT(DISTINCT us.shubetsu_id) AS shubetsu_num
FROM
    t_uriage u
    LEFT OUTER JOIN t_uriage_option uo
        ON u.denp_no=uo.denp_no AND u.det_no=uo.det_no
    INNER JOIN m_shohin_shubetsu us
        ON u.shohin_id=us.shohin_id
    LEFT OUTER JOIN m_waribiki w
        LEFT OUTER JOIN m_waribiki_option wo
            ON w.waribiki_id=wo.waribiki_id
        LEFT OUTER JOIN m_waribiki_shubetsu ws 
            ON w.waribiki_id=ws.waribiki_id
    ON wo.option_id IS NULL AND us.shubetsu_id=ws.shubetsu_id OR
       uo.option_id=wo.option_id AND ws.shubetsu_id IS NULL OR
       uo.option_id=wo.option_id AND us.shubetsu_id=ws.shubetsu_id
GROUP BY
    u.denp_no,
    u.det_no,
    w.waribiki_id
ORDER BY 1,2,3
;


denp_no
det_no
waribiki_id
option_num
shubetsu_num


1
1
null
0
1

1
1
1
0
1

2
1
2
1
2

2
1
5
1
2

3
1
1
1
1

3
1
2
1
2

3
1
4
1
1

4
1
2
1
2

4
1
5
2
2

5
1
2
1
2

5
1
5
2
2

5
2
1
2
1

5
2
2
1
2

5
2
3
1
2

5
2
4
2
1


Warning: Null value is eliminated by an aggregate or other SET operation.


SELECT
    w.waribiki_id,
    COUNT(DISTINCT wo.option_id) AS option_num,
    COUNT(DISTINCT ws.shubetsu_id) AS shubetsu_num
FROM
    m_waribiki w
    LEFT OUTER JOIN m_waribiki_option wo
        ON w.waribiki_id=wo.waribiki_id
    LEFT OUTER JOIN m_waribiki_shubetsu ws 
        ON w.waribiki_id=ws.waribiki_id
GROUP BY
    w.waribiki_id
ORDER BY 1
;


waribiki_id
option_num
shubetsu_num


1
0
1

2
1
0

3
1
2

4
2
1

5
2
2


Warning: Null value is eliminated by an aggregate or other SET operation.


SELECT DISTINCT
    uu.denp_no,
    ww.waribiki_id,
    ww.waribiki_name,
    ww.waribiki_ritsu
FROM
(
SELECT
    u.denp_no,
    u.det_no,
    w.waribiki_id,
    COUNT(DISTINCT uo.option_id) AS option_num,
    COUNT(DISTINCT us.shubetsu_id) AS shubetsu_num
FROM
    t_uriage u
    LEFT OUTER JOIN t_uriage_option uo
        ON u.denp_no=uo.denp_no AND u.det_no=uo.det_no
    INNER JOIN m_shohin_shubetsu us
        ON u.shohin_id=us.shohin_id
    LEFT OUTER JOIN m_waribiki w
        LEFT OUTER JOIN m_waribiki_option wo
            ON w.waribiki_id=wo.waribiki_id
        LEFT OUTER JOIN m_waribiki_shubetsu ws 
            ON w.waribiki_id=ws.waribiki_id
    ON wo.option_id IS NULL AND us.shubetsu_id=ws.shubetsu_id OR
       uo.option_id=wo.option_id AND ws.shubetsu_id IS NULL OR
       uo.option_id=wo.option_id AND us.shubetsu_id=ws.shubetsu_id
GROUP BY
    u.denp_no,
    u.det_no,
    w.waribiki_id
) uu
INNER JOIN
(
SELECT
    w.waribiki_id,
    MAX(w.waribiki_name) AS waribiki_name,
    MAX(w.waribiki_ritsu) AS waribiki_ritsu,
    COUNT(DISTINCT wo.option_id) AS option_num,
    COUNT(DISTINCT ws.shubetsu_id) AS shubetsu_num
FROM
    m_waribiki w
    LEFT OUTER JOIN m_waribiki_option wo
        ON w.waribiki_id=wo.waribiki_id
    LEFT OUTER JOIN m_waribiki_shubetsu ws 
        ON w.waribiki_id=ws.waribiki_id
GROUP BY
    w.waribiki_id
) ww
ON uu.waribiki_id=ww.waribiki_id AND
   (ww.option_num=0 OR
    uu.option_num>=ww.option_num) AND
   (ww.shubetsu_num=0 OR
    uu.shubetsu_num>=ww.shubetsu_num)
ORDER BY 1,2
;


denp_no
waribiki_id
waribiki_name
waribiki_ritsu


1
1
チョコレート割
10

2
2
包装紙無し割引
5

3
1
チョコレート割
10

3
2
包装紙無し割引
5

4
2
包装紙無し割引
5

4
5
3営業日以降出荷 割引+包装紙無し割引(グリコクッキー限定)
5

5
1
チョコレート割
10

5
2
包装紙無し割引
5

5
3
3営業日以降出荷 割引(明治チョコ限定)
3

5
4
3営業日以降出荷 割引+包装紙無し割引(チョコレート限定)
5

5
5
3営業日以降出荷 割引+包装紙無し割引(グリコクッキー限定)
5


Warning: Null value is eliminated by an aggregate or other SET operation.

waribiki_id	waribiki_name	waribiki_ritsu	waribiki_id	option_id	waribiki_id	shubetsu_id
1	チョコレート割	10	null	null	1	3
2	包装紙無し割引	5	2	2	null	null
3	3営業日以降出荷割引(明治チョコ限定)	3	3	1	3	1
3	3営業日以降出荷割引(明治チョコ限定)	3	3	1	3	3
4	3営業日以降出荷割引+包装紙無し割引(チョコレート限定)	5	4	1	4	3
4	3営業日以降出荷割引+包装紙無し割引(チョコレート限定)	5	4	2	4	3
5	3営業日以降出荷割引+包装紙無し割引(グリコクッキー限定)	5	5	1	5	2
5	3営業日以降出荷割引+包装紙無し割引(グリコクッキー限定)	5	5	1	5	4
5	3営業日以降出荷割引+包装紙無し割引(グリコクッキー限定)	5	5	2	5	2
5	3営業日以降出荷割引+包装紙無し割引(グリコクッキー限定)	5	5	2	5	4

denp_no	det_no	shohin_id	su	option_id	shubetsu_id	waribiki_id	waribiki_name	waribiki_ritsu	option_id	shubetsu_id
1	1	1	5	null	1	null	null	null	null	null
1	1	1	5	null	3	1	チョコレート割	10	null	3
2	1	3	5	2	2	2	包装紙無し割引	5	2	null
2	1	3	5	2	2	5	3営業日以降出荷割引+包装紙無し割引(グリコクッキー限定)	5	2	2
2	1	3	5	2	4	2	包装紙無し割引	5	2	null
2	1	3	5	2	4	5	3営業日以降出荷割引+包装紙無し割引(グリコクッキー限定)	5	2	4
3	1	1	5	2	1	2	包装紙無し割引	5	2	null
3	1	1	5	2	3	2	包装紙無し割引	5	2	null
3	1	1	5	2	3	4	3営業日以降出荷割引+包装紙無し割引(チョコレート限定)	5	2	3
4	1	3	5	1	2	5	3営業日以降出荷割引+包装紙無し割引(グリコクッキー限定)	5	1	2
4	1	3	5	2	2	2	包装紙無し割引	5	2	null
4	1	3	5	2	2	5	3営業日以降出荷割引+包装紙無し割引(グリコクッキー限定)	5	2	2
4	1	3	5	1	4	5	3営業日以降出荷割引+包装紙無し割引(グリコクッキー限定)	5	1	4
4	1	3	5	2	4	2	包装紙無し割引	5	2	null
4	1	3	5	2	4	5	3営業日以降出荷割引+包装紙無し割引(グリコクッキー限定)	5	2	4
5	1	3	5	1	2	5	3営業日以降出荷割引+包装紙無し割引(グリコクッキー限定)	5	1	2
5	1	3	5	2	2	2	包装紙無し割引	5	2	null
5	1	3	5	2	2	5	3営業日以降出荷割引+包装紙無し割引(グリコクッキー限定)	5	2	2
5	1	3	5	1	4	5	3営業日以降出荷割引+包装紙無し割引(グリコクッキー限定)	5	1	4
5	1	3	5	2	4	2	包装紙無し割引	5	2	null
5	1	3	5	2	4	5	3営業日以降出荷割引+包装紙無し割引(グリコクッキー限定)	5	2	4
5	2	1	10	1	1	3	3営業日以降出荷割引(明治チョコ限定)	3	1	1
5	2	1	10	2	1	2	包装紙無し割引	5	2	null
5	2	1	10	1	3	3	3営業日以降出荷割引(明治チョコ限定)	3	1	3
5	2	1	10	1	3	4	3営業日以降出荷割引+包装紙無し割引(チョコレート限定)	5	1	3
5	2	1	10	2	3	2	包装紙無し割引	5	2	null
5	2	1	10	2	3	4	3営業日以降出荷割引+包装紙無し割引(チョコレート限定)	5	2	3