mysql - SQL 包和 channel 关系

Question

我在计算包和 channel 之间的多对多关系时遇到了问题。每个 channel 都属于一个包，但一次有多个包。

IE: Channel 1 属于 Package 1,2,3,4,5 Channel 2属于Package 2,5

等等等等。我将如何编写表格来创建这种关系？到目前为止，我有:

CREATE TABLE Channel
(
ChannelID        NUMBER(10) CONSTRAINT PK_Channel PRIMARY KEY,
ChannelName      VARCHAR2(12) NOT NULL,
ChannelDesc      VARCHAR2(20) NOT NULL,
ChannelNumber    NUMBER(3) NOT NULL,
SupID            NUMBER(2) CONSTRAINT FK_Channel_Supplier REFERENCES Supplier (SupID),
PackageID        NUMBER(5) CONSTRAINT FK_Channel_Package REFERENCES Package (PackageID)
);

INSERT INTO CHANNEL
VALUES(0001, 'Channel 1', '1st Channel', 01, 10001);
INSERT INTO CHANNEL
VALUES(0002,'Channel 2', '2nd Channel', 02, 10001);
INSERT INTO CHANNEL
VALUES(0003, 'Channel 3', '3rd Channel', 03, 10002);
INSERT INTO CHANNEL
VALUES(0004, 'Channel 4', '4th Channel', 04, 10003);
INSERT INTO CHANNEL
VALUES(0005, 'Channel 5', '5th Channel', 05, 10004);
INSERT INTO CHANNEL
VALUES(0006, 'Channel 6', '6th Channel', 06, 10001);
INSERT INTO CHANNEL
VALUES(0007, 'Channel 7', '7th Channel', 07, 10004);
INSERT INTO CHANNEL
VALUES(0008, 'Channel 8', '8th Channel', 08, 20005);

CREATE TABLE Package
(
PackageID            NUMBER(10) CONSTRAINT PK_Package PRIMARY KEY,
PackageDescrip       VARCHAR2(25) NOT NULL,
PackageFee           NUMBER(3) DEFAULT '0',
);

INSERT INTO PACKAGE
VALUES(01, 'Movies Galore', 30);
INSERT INTO PACKAGE
VALUES(02, 'News Globe', 30);
INSERT INTO PACKAGE
VALUES(03, 'Total Watcher', 40);
INSERT INTO PACKAGE
VALUES(04, 'Couch Potato', 50);

Answer 1

我会添加一个额外的表来处理 channel 与包的关系，并提取 channel 表中的 PackageID 列。

CREATE TABLE PackageChannel
(
    PackageChannelID            NUMBER(10) CONSTRAINT PK_PackageChannel PRIMARY KEY,
    PackageID       NUMBER(10),
    ChannelID       NUMBER(10)
);

然后在select语句中处理关系

SELECT PackageID FROM PackageChannel WHERE ChannelID = 0001;