teratail header banner
teratail header banner
質問するログイン新規登録

質問編集履歴

2

ヘボン式に修正

2021/02/16 08:41

投稿

shinaka
shinaka

スコア18

title CHANGED
File without changes
body CHANGED
@@ -12,7 +12,7 @@
12
12
  ・商品には種別が存在しており、1商品に付き複数の種別を設定できる
13
13
  ・「商品種別紐付けマスタ」を元に「商品」と「種別」の紐付けを行っている
14
14
 
15
- ![イメージ説明](2302920871a28c559541c711ee0c3683.png)
15
+ ![イメージ説明](7b1c0e351baaab6159587bc6c480152e.png)
16
16
 
17
17
  ---
18
18
 
@@ -34,7 +34,7 @@
34
34
 
35
35
  ---
36
36
 
37
- ![イメージ説明](91891dfeac68da00dcc11d58446031ff.png)
37
+ ![イメージ説明](6ecd491c19956a160b96529c29b01c20.png)
38
38
 
39
39
  ---
40
40
 
@@ -82,13 +82,13 @@
82
82
  GO
83
83
  SET QUOTED_IDENTIFIER ON
84
84
  GO
85
- CREATE TABLE [dbo].[m_shohin_syubetu](
85
+ CREATE TABLE [dbo].[m_shohin_shubetsu](
86
86
  [shohin_id] [int] NOT NULL,
87
- [syubetu_id] [int] NOT NULL,
87
+ [shubetsu_id] [int] NOT NULL,
88
88
  CONSTRAINT [PK_m_shohin_syubetu] PRIMARY KEY CLUSTERED
89
89
  (
90
90
  [shohin_id] ASC,
91
- [syubetu_id] ASC
91
+ [shubetsu_id] ASC
92
92
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
93
93
  ) ON [PRIMARY]
94
94
  GO
@@ -96,12 +96,12 @@
96
96
  GO
97
97
  SET QUOTED_IDENTIFIER ON
98
98
  GO
99
- CREATE TABLE [dbo].[m_syubetu](
99
+ CREATE TABLE [dbo].[m_shubetsu](
100
- [syubetu_id] [int] NOT NULL,
100
+ [shubetsu_id] [int] NOT NULL,
101
- [syubetu_name] [nchar](50) NULL,
101
+ [shubetsu_name] [nchar](50) NULL,
102
102
  CONSTRAINT [PK_m_syubetu] PRIMARY KEY CLUSTERED
103
103
  (
104
- [syubetu_id] ASC
104
+ [shubetsu_id] ASC
105
105
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
106
106
  ) ON [PRIMARY]
107
107
  GO
@@ -112,7 +112,7 @@
112
112
  CREATE TABLE [dbo].[m_waribiki](
113
113
  [waribiki_id] [int] NOT NULL,
114
114
  [waribiki_name] [nchar](50) NULL,
115
- [waribiki_ritu] [int] NULL,
115
+ [waribiki_ritsu] [int] NULL,
116
116
  CONSTRAINT [PK_m_waribiki] PRIMARY KEY CLUSTERED
117
117
  (
118
118
  [waribiki_id] ASC
@@ -137,13 +137,13 @@
137
137
  GO
138
138
  SET QUOTED_IDENTIFIER ON
139
139
  GO
140
- CREATE TABLE [dbo].[m_waribiki_syubetu](
140
+ CREATE TABLE [dbo].[m_waribiki_shubetsu](
141
141
  [waribiki_id] [int] NOT NULL,
142
- [syubetu_id] [int] NOT NULL,
142
+ [shubetsu_id] [int] NOT NULL,
143
143
  CONSTRAINT [PK_m_waribiki_syubetu] PRIMARY KEY CLUSTERED
144
144
  (
145
145
  [waribiki_id] ASC,
146
- [syubetu_id] ASC
146
+ [shubetsu_id] ASC
147
147
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
148
148
  ) ON [PRIMARY]
149
149
  GO
@@ -191,41 +191,41 @@
191
191
  GO
192
192
  INSERT [dbo].[m_shohin] ([shohin_id], [shohin_name]) VALUES (4, N'明治チョコ&クッキーセット ')
193
193
  GO
194
- INSERT [dbo].[m_shohin_syubetu] ([shohin_id], [syubetu_id]) VALUES (1, 1)
194
+ INSERT [dbo].[m_shohin_shubetsu] ([shohin_id], [shubetsu_id]) VALUES (1, 1)
195
195
  GO
196
- INSERT [dbo].[m_shohin_syubetu] ([shohin_id], [syubetu_id]) VALUES (1, 3)
196
+ INSERT [dbo].[m_shohin_shubetsu] ([shohin_id], [shubetsu_id]) VALUES (1, 3)
197
197
  GO
198
- INSERT [dbo].[m_shohin_syubetu] ([shohin_id], [syubetu_id]) VALUES (2, 2)
198
+ INSERT [dbo].[m_shohin_shubetsu] ([shohin_id], [shubetsu_id]) VALUES (2, 2)
199
199
  GO
200
- INSERT [dbo].[m_shohin_syubetu] ([shohin_id], [syubetu_id]) VALUES (2, 3)
200
+ INSERT [dbo].[m_shohin_shubetsu] ([shohin_id], [shubetsu_id]) VALUES (2, 3)
201
201
  GO
202
- INSERT [dbo].[m_shohin_syubetu] ([shohin_id], [syubetu_id]) VALUES (3, 2)
202
+ INSERT [dbo].[m_shohin_shubetsu] ([shohin_id], [shubetsu_id]) VALUES (3, 2)
203
203
  GO
204
- INSERT [dbo].[m_shohin_syubetu] ([shohin_id], [syubetu_id]) VALUES (3, 4)
204
+ INSERT [dbo].[m_shohin_shubetsu] ([shohin_id], [shubetsu_id]) VALUES (3, 4)
205
205
  GO
206
- INSERT [dbo].[m_shohin_syubetu] ([shohin_id], [syubetu_id]) VALUES (4, 1)
206
+ INSERT [dbo].[m_shohin_shubetsu] ([shohin_id], [shubetsu_id]) VALUES (4, 1)
207
207
  GO
208
- INSERT [dbo].[m_shohin_syubetu] ([shohin_id], [syubetu_id]) VALUES (4, 3)
208
+ INSERT [dbo].[m_shohin_shubetsu] ([shohin_id], [shubetsu_id]) VALUES (4, 3)
209
209
  GO
210
- INSERT [dbo].[m_shohin_syubetu] ([shohin_id], [syubetu_id]) VALUES (4, 4)
210
+ INSERT [dbo].[m_shohin_shubetsu] ([shohin_id], [shubetsu_id]) VALUES (4, 4)
211
211
  GO
212
- INSERT [dbo].[m_syubetu] ([syubetu_id], [syubetu_name]) VALUES (1, N'明治 ')
212
+ INSERT [dbo].[m_shubetsu] ([shubetsu_id], [shubetsu_name]) VALUES (1, N'明治 ')
213
213
  GO
214
- INSERT [dbo].[m_syubetu] ([syubetu_id], [syubetu_name]) VALUES (2, N'グリコ ')
214
+ INSERT [dbo].[m_shubetsu] ([shubetsu_id], [shubetsu_name]) VALUES (2, N'グリコ ')
215
215
  GO
216
- INSERT [dbo].[m_syubetu] ([syubetu_id], [syubetu_name]) VALUES (3, N'チョコレート ')
216
+ INSERT [dbo].[m_shubetsu] ([shubetsu_id], [shubetsu_name]) VALUES (3, N'チョコレート ')
217
217
  GO
218
- INSERT [dbo].[m_syubetu] ([syubetu_id], [syubetu_name]) VALUES (4, N'クッキー ')
218
+ INSERT [dbo].[m_shubetsu] ([shubetsu_id], [shubetsu_name]) VALUES (4, N'クッキー ')
219
219
  GO
220
- INSERT [dbo].[m_waribiki] ([waribiki_id], [waribiki_name], [waribiki_ritu]) VALUES (1, N'チョコレート割 ', 10)
220
+ INSERT [dbo].[m_waribiki] ([waribiki_id], [waribiki_name], [waribiki_ritsu]) VALUES (1, N'チョコレート割 ', 10)
221
221
  GO
222
- INSERT [dbo].[m_waribiki] ([waribiki_id], [waribiki_name], [waribiki_ritu]) VALUES (2, N'包装紙無し割引 ', 5)
222
+ INSERT [dbo].[m_waribiki] ([waribiki_id], [waribiki_name], [waribiki_ritsu]) VALUES (2, N'包装紙無し割引 ', 5)
223
223
  GO
224
- INSERT [dbo].[m_waribiki] ([waribiki_id], [waribiki_name], [waribiki_ritu]) VALUES (3, N'3営業日以降出荷 割引(明治チョコ限定) ', 3)
224
+ INSERT [dbo].[m_waribiki] ([waribiki_id], [waribiki_name], [waribiki_ritsu]) VALUES (3, N'3営業日以降出荷 割引(明治チョコ限定) ', 3)
225
225
  GO
226
- INSERT [dbo].[m_waribiki] ([waribiki_id], [waribiki_name], [waribiki_ritu]) VALUES (4, N'3営業日以降出荷 割引+包装紙無し割引(チョコレート限定) ', 5)
226
+ INSERT [dbo].[m_waribiki] ([waribiki_id], [waribiki_name], [waribiki_ritsu]) VALUES (4, N'3営業日以降出荷 割引+包装紙無し割引(チョコレート限定) ', 5)
227
227
  GO
228
- INSERT [dbo].[m_waribiki] ([waribiki_id], [waribiki_name], [waribiki_ritu]) VALUES (5, N'3営業日以降出荷 割引+包装紙無し割引(グリコクッキー限定) ', 5)
228
+ INSERT [dbo].[m_waribiki] ([waribiki_id], [waribiki_name], [waribiki_ritsu]) VALUES (5, N'3営業日以降出荷 割引+包装紙無し割引(グリコクッキー限定) ', 5)
229
229
  GO
230
230
  INSERT [dbo].[m_waribiki_option] ([waribiki_id], [option_id]) VALUES (2, 2)
231
231
  GO
@@ -239,17 +239,17 @@
239
239
  GO
240
240
  INSERT [dbo].[m_waribiki_option] ([waribiki_id], [option_id]) VALUES (5, 2)
241
241
  GO
242
- INSERT [dbo].[m_waribiki_syubetu] ([waribiki_id], [syubetu_id]) VALUES (1, 3)
242
+ INSERT [dbo].[m_waribiki_shubetsu] ([waribiki_id], [shubetsu_id]) VALUES (1, 3)
243
243
  GO
244
- INSERT [dbo].[m_waribiki_syubetu] ([waribiki_id], [syubetu_id]) VALUES (3, 1)
244
+ INSERT [dbo].[m_waribiki_shubetsu] ([waribiki_id], [shubetsu_id]) VALUES (3, 1)
245
245
  GO
246
- INSERT [dbo].[m_waribiki_syubetu] ([waribiki_id], [syubetu_id]) VALUES (3, 3)
246
+ INSERT [dbo].[m_waribiki_shubetsu] ([waribiki_id], [shubetsu_id]) VALUES (3, 3)
247
247
  GO
248
- INSERT [dbo].[m_waribiki_syubetu] ([waribiki_id], [syubetu_id]) VALUES (4, 3)
248
+ INSERT [dbo].[m_waribiki_shubetsu] ([waribiki_id], [shubetsu_id]) VALUES (4, 3)
249
249
  GO
250
- INSERT [dbo].[m_waribiki_syubetu] ([waribiki_id], [syubetu_id]) VALUES (5, 2)
250
+ INSERT [dbo].[m_waribiki_shubetsu] ([waribiki_id], [shubetsu_id]) VALUES (5, 2)
251
251
  GO
252
- INSERT [dbo].[m_waribiki_syubetu] ([waribiki_id], [syubetu_id]) VALUES (5, 3)
252
+ INSERT [dbo].[m_waribiki_shubetsu] ([waribiki_id], [shubetsu_id]) VALUES (5, 3)
253
253
  GO
254
254
  INSERT [dbo].[t_uriage] ([denp_no], [det_no], [shohin_id], [su]) VALUES (1, 1, 1, 5)
255
255
  GO

1

文字数制限

2021/02/16 08:41

投稿

shinaka
shinaka

スコア18

title CHANGED
File without changes
body CHANGED
@@ -12,53 +12,16 @@
12
12
  ・商品には種別が存在しており、1商品に付き複数の種別を設定できる
13
13
  ・「商品種別紐付けマスタ」を元に「商品」と「種別」の紐付けを行っている
14
14
 
15
- **商品マスタ(m_shohin)**
16
- |商品ID|商品名|
17
- |:--|:--:|
18
- |1|明治チョコレート|
19
- |2|グリコチョコレート|
20
- |3|グリコクッキー|
21
- |4|明治チョコ&クッキセット|
15
+ ![イメジ説明](2302920871a28c559541c711ee0c3683.png)
22
16
 
23
17
  ---
24
18
 
25
- **商品種別マスタ(m_syubetu)**
26
- |種別ID|種別名|
27
- |:--|:--:|
28
- |1|明治|
29
- |2|グリコ|
30
- |3|チョコレート|
31
- |4|クッキー|
32
-
33
- ---
34
-
35
- **商品種別紐付けマスタ(m_shohin_syubetu)**
36
- |商品ID|種別ID|
37
- |:--|:--|
38
- |1|1(明治)|
39
- |1|3(チョコレート)|
40
- |2|2(グリコ)|
41
- |2|3(チョコレート)|
42
- |3|2(グリコ)|
43
- |3|4(クッキー)|
44
- |4|1(明治)|
45
- |4|3(チョコレート)|
46
- |4|4(クッキー)|
47
-
48
- ---
49
-
50
19
  ### オプション関連
51
20
 
52
21
  ・商品種別は別に「オプション」が存在している
53
22
  ・オプションは商品マスタに紐づくのではなく、注文トランに紐づく
54
23
  ・1注文に付き複数オプションが選択できる
55
-
56
- **配送オプションマスタ(m_option)**
24
+ ![イメージ説明](5a9b53bfc1cbc4c80cf3b2960575025a.png)
57
- |オプションID|オプション名|
58
- |:--|:--:|
59
- |1|3営業日以降出荷|
60
- |2|包装紙無し|
61
-
62
25
  ---
63
26
 
64
27
  ### 割引マスタ関連
@@ -67,149 +30,254 @@
67
30
  ・商品種別のみで判断する場合もあれば、オプションのみで判断する場合もある
68
31
  ・条件に一致する割引マスタが取得されればOK(=複数行が取得される場合もあるということ)
69
32
 
70
- **割引ヘッダマスタ(m_waribiki)**
71
- |割引ID|割引名|割引率|
72
- |:--|:--|--:|
73
- |1|チョコレート割|10|
74
- |2|包装紙無し割引|5|
75
- |3|3営業日以降出荷 割引(明治チョコ限定)|3|
76
- |4|3営業日以降出荷 割引+包装紙無し割引(チョコレート限定)|5|
77
- |5|3営業日以降出荷 割引+包装紙無し割引(グリコクッキ限定)|5|
33
+ ![イメジ説明](bf519d5e174665a4d37daa1e17b213e0.png)
78
34
 
79
- (補足)
80
- 割引ID:1 → 商品種別のみで判断
81
- 割引ID:2 → 配送オプションのみで判断
82
- 割引ID:3 → 商品種別(複数) + 配送オプションで判断
83
- 割引ID:4 → 商品種別 + 配送オプション(複数)で判断
84
- 割引ID:5 → 商品種別(複数) + 配送オプション(複数)で判断
85
-
86
35
  ---
87
36
 
88
- **配送オプション割引条件マスタ(m_warbiki_option)**
37
+ ![イメージ説明](91891dfeac68da00dcc11d58446031ff.png)
89
- |割引ID|オプションID|
90
- |:--|:--|
91
- |2|2(包装紙無し)|
92
- |3|1(3営業日以降出荷)|
93
- |4|1(3営業日以降出荷)|
94
- |4|2(包装紙無し)|
95
- |5|1(3営業日以降出荷)|
96
- |5|2(包装紙無し)|
97
38
 
98
39
  ---
99
40
 
100
- **商品種別 割引条件マスタ(m_waribiki_syubetu)**
101
- |割引ID|種別ID|
102
- |:--|:--|
103
- |1|3(チョコレート)|
104
- |3|1(明治)|
105
- |3|3(チョコレート)|
106
- |4|3(チョコレート)|
107
- |5|2(グリコ)|
108
- |5|3(チョコレート)|
109
-
110
- ---
111
-
112
41
  ### 望まれる結果
113
42
 
114
- 【パタン① 商品種別のみが一致する場合】
43
+ ![イメジ説明](80e587a6b73ee93d96090baac0621dff.png)
115
44
 
116
- **売上伝票(t_uriage)**
117
- |伝票番号|明細番号|商品ID|数量|
118
- |:--|:--:|:--|--:|
119
- |1|1|1(明治チョコレ)|5|
45
+ ![イメジ説明](7598ddacf81619920ecf34d765257212.png)
120
46
 
121
- **取得したい割引ヘッダマスタ**
47
+ ![イメージ説明](d7f5f6761203d26eefecd56d46d25b7b.png)
122
- |割引ID|割引名|割引率|
123
- |:--|:--:|--:|
124
- |1|チョコレート割|10|
125
48
 
126
49
  ---
127
50
 
51
+ ### CREATE文
52
+ ```SQL
53
+ USE [test]
54
+ GO
55
+ SET ANSI_NULLS ON
56
+ GO
128
- 【パターン② 配送オプションのみが一致する場合】
57
+ SET QUOTED_IDENTIFIER ON
58
+ GO
59
+ CREATE TABLE [dbo].[m_option](
60
+ [option_id] [int] NOT NULL,
61
+ [option_name] [nchar](50) NULL,
62
+ CONSTRAINT [PK_m_option] PRIMARY KEY CLUSTERED
63
+ (
64
+ [option_id] ASC
65
+ )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
66
+ ) ON [PRIMARY]
67
+ GO
68
+ SET ANSI_NULLS ON
69
+ GO
70
+ SET QUOTED_IDENTIFIER ON
71
+ GO
72
+ CREATE TABLE [dbo].[m_shohin](
73
+ [shohin_id] [int] NOT NULL,
74
+ [shohin_name] [nchar](50) NULL,
75
+ CONSTRAINT [PK_m_shohin] PRIMARY KEY CLUSTERED
76
+ (
77
+ [shohin_id] ASC
78
+ )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
79
+ ) ON [PRIMARY]
80
+ GO
81
+ SET ANSI_NULLS ON
82
+ GO
83
+ SET QUOTED_IDENTIFIER ON
84
+ GO
85
+ CREATE TABLE [dbo].[m_shohin_syubetu](
86
+ [shohin_id] [int] NOT NULL,
87
+ [syubetu_id] [int] NOT NULL,
88
+ CONSTRAINT [PK_m_shohin_syubetu] PRIMARY KEY CLUSTERED
89
+ (
90
+ [shohin_id] ASC,
91
+ [syubetu_id] ASC
92
+ )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
93
+ ) ON [PRIMARY]
94
+ GO
95
+ SET ANSI_NULLS ON
96
+ GO
97
+ SET QUOTED_IDENTIFIER ON
98
+ GO
99
+ CREATE TABLE [dbo].[m_syubetu](
100
+ [syubetu_id] [int] NOT NULL,
101
+ [syubetu_name] [nchar](50) NULL,
102
+ CONSTRAINT [PK_m_syubetu] PRIMARY KEY CLUSTERED
103
+ (
104
+ [syubetu_id] ASC
105
+ )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
106
+ ) ON [PRIMARY]
107
+ GO
108
+ SET ANSI_NULLS ON
109
+ GO
110
+ SET QUOTED_IDENTIFIER ON
111
+ GO
112
+ CREATE TABLE [dbo].[m_waribiki](
113
+ [waribiki_id] [int] NOT NULL,
114
+ [waribiki_name] [nchar](50) NULL,
115
+ [waribiki_ritu] [int] NULL,
116
+ CONSTRAINT [PK_m_waribiki] PRIMARY KEY CLUSTERED
117
+ (
118
+ [waribiki_id] ASC
119
+ )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
120
+ ) ON [PRIMARY]
121
+ GO
122
+ SET ANSI_NULLS ON
123
+ GO
124
+ SET QUOTED_IDENTIFIER ON
125
+ GO
126
+ CREATE TABLE [dbo].[m_waribiki_option](
127
+ [waribiki_id] [int] NOT NULL,
128
+ [option_id] [int] NOT NULL,
129
+ CONSTRAINT [PK_m_warbiki_option] PRIMARY KEY CLUSTERED
130
+ (
131
+ [waribiki_id] ASC,
132
+ [option_id] ASC
133
+ )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
134
+ ) ON [PRIMARY]
135
+ GO
136
+ SET ANSI_NULLS ON
137
+ GO
138
+ SET QUOTED_IDENTIFIER ON
139
+ GO
140
+ CREATE TABLE [dbo].[m_waribiki_syubetu](
141
+ [waribiki_id] [int] NOT NULL,
142
+ [syubetu_id] [int] NOT NULL,
143
+ CONSTRAINT [PK_m_waribiki_syubetu] PRIMARY KEY CLUSTERED
144
+ (
145
+ [waribiki_id] ASC,
146
+ [syubetu_id] ASC
147
+ )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
148
+ ) ON [PRIMARY]
149
+ GO
150
+ SET ANSI_NULLS ON
151
+ GO
152
+ SET QUOTED_IDENTIFIER ON
153
+ GO
154
+ CREATE TABLE [dbo].[t_uriage](
155
+ [denp_no] [int] NOT NULL,
156
+ [det_no] [int] NOT NULL,
157
+ [shohin_id] [int] NULL,
158
+ [su] [int] NULL,
159
+ CONSTRAINT [PK_t_uriage] PRIMARY KEY CLUSTERED
160
+ (
161
+ [denp_no] ASC,
162
+ [det_no] ASC
163
+ )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
164
+ ) ON [PRIMARY]
165
+ GO
166
+ SET ANSI_NULLS ON
167
+ GO
168
+ SET QUOTED_IDENTIFIER ON
169
+ GO
170
+ CREATE TABLE [dbo].[t_uriage_option](
171
+ [denp_no] [int] NOT NULL,
172
+ [det_no] [int] NOT NULL,
173
+ [option_id] [int] NOT NULL,
174
+ CONSTRAINT [PK_t_uriage_option] PRIMARY KEY CLUSTERED
175
+ (
176
+ [denp_no] ASC,
177
+ [det_no] ASC,
178
+ [option_id] ASC
179
+ )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
180
+ ) ON [PRIMARY]
181
+ GO
182
+ INSERT [dbo].[m_option] ([option_id], [option_name]) VALUES (1, N'3営業日以降出荷 ')
183
+ GO
184
+ INSERT [dbo].[m_option] ([option_id], [option_name]) VALUES (2, N'包装紙無し ')
185
+ GO
186
+ INSERT [dbo].[m_shohin] ([shohin_id], [shohin_name]) VALUES (1, N'明治チョコレート ')
187
+ GO
188
+ INSERT [dbo].[m_shohin] ([shohin_id], [shohin_name]) VALUES (2, N'グリコチョコレート ')
189
+ GO
190
+ INSERT [dbo].[m_shohin] ([shohin_id], [shohin_name]) VALUES (3, N'グリコクッキー ')
191
+ GO
192
+ INSERT [dbo].[m_shohin] ([shohin_id], [shohin_name]) VALUES (4, N'明治チョコ&クッキーセット ')
193
+ GO
194
+ INSERT [dbo].[m_shohin_syubetu] ([shohin_id], [syubetu_id]) VALUES (1, 1)
195
+ GO
196
+ INSERT [dbo].[m_shohin_syubetu] ([shohin_id], [syubetu_id]) VALUES (1, 3)
197
+ GO
198
+ INSERT [dbo].[m_shohin_syubetu] ([shohin_id], [syubetu_id]) VALUES (2, 2)
199
+ GO
200
+ INSERT [dbo].[m_shohin_syubetu] ([shohin_id], [syubetu_id]) VALUES (2, 3)
201
+ GO
202
+ INSERT [dbo].[m_shohin_syubetu] ([shohin_id], [syubetu_id]) VALUES (3, 2)
203
+ GO
204
+ INSERT [dbo].[m_shohin_syubetu] ([shohin_id], [syubetu_id]) VALUES (3, 4)
205
+ GO
206
+ INSERT [dbo].[m_shohin_syubetu] ([shohin_id], [syubetu_id]) VALUES (4, 1)
207
+ GO
208
+ INSERT [dbo].[m_shohin_syubetu] ([shohin_id], [syubetu_id]) VALUES (4, 3)
209
+ GO
210
+ INSERT [dbo].[m_shohin_syubetu] ([shohin_id], [syubetu_id]) VALUES (4, 4)
211
+ GO
212
+ INSERT [dbo].[m_syubetu] ([syubetu_id], [syubetu_name]) VALUES (1, N'明治 ')
213
+ GO
214
+ INSERT [dbo].[m_syubetu] ([syubetu_id], [syubetu_name]) VALUES (2, N'グリコ ')
215
+ GO
216
+ INSERT [dbo].[m_syubetu] ([syubetu_id], [syubetu_name]) VALUES (3, N'チョコレート ')
217
+ GO
218
+ INSERT [dbo].[m_syubetu] ([syubetu_id], [syubetu_name]) VALUES (4, N'クッキー ')
219
+ GO
220
+ INSERT [dbo].[m_waribiki] ([waribiki_id], [waribiki_name], [waribiki_ritu]) VALUES (1, N'チョコレート割 ', 10)
221
+ GO
222
+ INSERT [dbo].[m_waribiki] ([waribiki_id], [waribiki_name], [waribiki_ritu]) VALUES (2, N'包装紙無し割引 ', 5)
223
+ GO
224
+ INSERT [dbo].[m_waribiki] ([waribiki_id], [waribiki_name], [waribiki_ritu]) VALUES (3, N'3営業日以降出荷 割引(明治チョコ限定) ', 3)
225
+ GO
226
+ INSERT [dbo].[m_waribiki] ([waribiki_id], [waribiki_name], [waribiki_ritu]) VALUES (4, N'3営業日以降出荷 割引+包装紙無し割引(チョコレート限定) ', 5)
227
+ GO
228
+ INSERT [dbo].[m_waribiki] ([waribiki_id], [waribiki_name], [waribiki_ritu]) VALUES (5, N'3営業日以降出荷 割引+包装紙無し割引(グリコクッキー限定) ', 5)
229
+ GO
230
+ INSERT [dbo].[m_waribiki_option] ([waribiki_id], [option_id]) VALUES (2, 2)
231
+ GO
232
+ INSERT [dbo].[m_waribiki_option] ([waribiki_id], [option_id]) VALUES (3, 1)
233
+ GO
234
+ INSERT [dbo].[m_waribiki_option] ([waribiki_id], [option_id]) VALUES (4, 1)
235
+ GO
236
+ INSERT [dbo].[m_waribiki_option] ([waribiki_id], [option_id]) VALUES (4, 2)
237
+ GO
238
+ INSERT [dbo].[m_waribiki_option] ([waribiki_id], [option_id]) VALUES (5, 1)
239
+ GO
240
+ INSERT [dbo].[m_waribiki_option] ([waribiki_id], [option_id]) VALUES (5, 2)
241
+ GO
242
+ INSERT [dbo].[m_waribiki_syubetu] ([waribiki_id], [syubetu_id]) VALUES (1, 3)
243
+ GO
244
+ INSERT [dbo].[m_waribiki_syubetu] ([waribiki_id], [syubetu_id]) VALUES (3, 1)
245
+ GO
246
+ INSERT [dbo].[m_waribiki_syubetu] ([waribiki_id], [syubetu_id]) VALUES (3, 3)
247
+ GO
248
+ INSERT [dbo].[m_waribiki_syubetu] ([waribiki_id], [syubetu_id]) VALUES (4, 3)
249
+ GO
250
+ INSERT [dbo].[m_waribiki_syubetu] ([waribiki_id], [syubetu_id]) VALUES (5, 2)
251
+ GO
252
+ INSERT [dbo].[m_waribiki_syubetu] ([waribiki_id], [syubetu_id]) VALUES (5, 3)
253
+ GO
254
+ INSERT [dbo].[t_uriage] ([denp_no], [det_no], [shohin_id], [su]) VALUES (1, 1, 1, 5)
255
+ GO
256
+ INSERT [dbo].[t_uriage] ([denp_no], [det_no], [shohin_id], [su]) VALUES (2, 1, 3, 5)
257
+ GO
258
+ INSERT [dbo].[t_uriage] ([denp_no], [det_no], [shohin_id], [su]) VALUES (3, 1, 1, 5)
259
+ GO
260
+ INSERT [dbo].[t_uriage] ([denp_no], [det_no], [shohin_id], [su]) VALUES (4, 1, 3, 5)
261
+ GO
262
+ INSERT [dbo].[t_uriage] ([denp_no], [det_no], [shohin_id], [su]) VALUES (5, 1, 3, 5)
263
+ GO
264
+ INSERT [dbo].[t_uriage] ([denp_no], [det_no], [shohin_id], [su]) VALUES (5, 2, 1, 10)
265
+ GO
266
+ INSERT [dbo].[t_uriage_option] ([denp_no], [det_no], [option_id]) VALUES (2, 1, 2)
267
+ GO
268
+ INSERT [dbo].[t_uriage_option] ([denp_no], [det_no], [option_id]) VALUES (3, 1, 2)
269
+ GO
270
+ INSERT [dbo].[t_uriage_option] ([denp_no], [det_no], [option_id]) VALUES (4, 1, 1)
271
+ GO
272
+ INSERT [dbo].[t_uriage_option] ([denp_no], [det_no], [option_id]) VALUES (4, 1, 2)
273
+ GO
274
+ INSERT [dbo].[t_uriage_option] ([denp_no], [det_no], [option_id]) VALUES (5, 1, 1)
275
+ GO
276
+ INSERT [dbo].[t_uriage_option] ([denp_no], [det_no], [option_id]) VALUES (5, 1, 2)
277
+ GO
278
+ INSERT [dbo].[t_uriage_option] ([denp_no], [det_no], [option_id]) VALUES (5, 2, 1)
279
+ GO
280
+ INSERT [dbo].[t_uriage_option] ([denp_no], [det_no], [option_id]) VALUES (5, 2, 2)
281
+ GO
129
282
 
130
- **売上伝票(t_uriage)**
131
- |伝票番号|明細番号|商品ID|数量|
132
- |:--|:--:|:--|--:|
133
- |2|1|3(グリコクッキー)|5|
134
-
135
- **売上伝票 配送オプション管理(t_uriage_option)**
136
- |伝票番号|明細番号|オプションID|
137
- |:--|:--:|:--|
138
- |2|1|2(包装紙無し)|
139
-
140
- **取得したい割引ヘッダマスタ**
141
- |割引ID|割引名|割引率|
142
- |:--|:--:|--:|
143
- |2|包装紙無し割引|5|
144
-
145
- ---
283
+ ```
146
-
147
- 【パターン③ 商品種別 + 配送オプションが一致する場合】
148
-
149
- **売上伝票(t_uriage)**
150
- |伝票番号|明細番号|商品ID|数量|
151
- |:--|:--:|:--|--:|
152
- |3|1|1(明治チョコレート)|5|
153
-
154
- **売上伝票 配送オプション管理(t_uriage_option)**
155
- |伝票番号|明細番号|オプションID|
156
- |:--|:--:|:--|
157
- |3|1|2(包装紙無し)|
158
-
159
- **取得したい割引ヘッダマスタ**
160
- |割引ID|割引名|割引率|
161
- |:--|:--:|--:|
162
- |1|チョコレート割|10|
163
- |2|包装紙無し割引|5|
164
-
165
- ---
166
-
167
- 【パターン④ 商品種別(複数) + 配送オプション(複数)が一致する場合】
168
-
169
- **売上伝票(t_uriage)**
170
- |伝票番号|明細番号|商品ID|数量|
171
- |:--|:--:|:--|--:|
172
- |4|1|3(グリコクッキー)|5|
173
-
174
- **売上伝票 配送オプション管理(t_uriage_option)**
175
- |伝票番号|明細番号|オプションID|
176
- |:--|:--:|:--|
177
- |4|1|1(3営業日以降出荷)|
178
- |4|1|2(包装紙無し)|
179
-
180
-
181
- **取得したい割引ヘッダマスタ**
182
- |割引ID|割引名|割引率|
183
- |:--|:--|--:|
184
- |2|包装紙無し割引|5|
185
- |5|3営業日以降出荷 割引+包装紙無し割引(グリコクッキー限定)|5|
186
-
187
- ---
188
-
189
- 【パターン⑤ 明細が複数行かつ商品種別(複数) + 配送オプション(複数)が一致する場合】
190
-
191
- **売上伝票(t_uriage)**
192
- |伝票番号|明細番号|商品ID|数量|
193
- |:--|:--:|:--|--:|
194
- |5|1|3(グリコクッキー)|5|
195
- |5|2|1(明治チョコレート)|10|
196
-
197
- **売上伝票 配送オプション管理(t_uriage_option)**
198
- |伝票番号|明細番号|オプションID|
199
- |:--|:--:|:--|
200
- |5|1|1(3営業日以降出荷)|
201
- |5|1|2(包装紙無し)|
202
- |5|2|1(3営業日以降出荷)|
203
- |5|2|2(包装紙無し)|
204
-
205
-
206
- **取得したい割引ヘッダマスタ**
207
- |割引ID|割引名|割引率|
208
- |:--|:--|--:|
209
- |1|チョコレート割|10|
210
- |2|包装紙無し割引|5|
211
- |3|3営業日以降出荷 割引(明治チョコ限定)|3|
212
- |4|3営業日以降出荷 割引+包装紙無し割引(チョコレート限定)|5|
213
- |5|3営業日以降出荷 割引+包装紙無し割引(グリコクッキー限定)|5|
214
-
215
- ---