mysql - 值没有被插入到正确的记录中 MySQL

Question

当向表中插入一个值时，不是仅将值分配给单个记录，而是将其分配给所有记录:

CREATE TABLE IF NOT EXISTS user
(
    userID VARCHAR(50) NOT NULL,
    userName VARCHAR(40) NOT NULL,        
    PRIMARY KEY (userID)
);
    CREATE TABLE IF NOT EXISTS rewards
(
    rewardID VARCHAR(5) NOT NULL,
    rewardDescription VARCHAR(20) NOT NULL,
    PRIMARY KEY (rewardID)
);

CREATE TABLE IF NOT EXISTS promos
(   
    issueDate DATETIME DEFAULT CURRENT_TIMESTAMP,
    expiryDateOfReward DATETIME GENERATED ALWAYS AS(issueDate + INTERVAL 6 MONTH),  
    quantity BIT DEFAULT 0,
    userID VARCHAR(50) REFERENCES user(userID),
    rewardID VARCHAR(5) REFERENCES rewards(rewardsID),
    PRIMARY KEY(userID, rewardID)
);

    INSERT INTO user
    VALUES('DV2015', 'Bob'),
          ('DV2016', 'Mary'),
          ('DV2017', 'Megan');

INSERT INTO rewards
VALUES('rw10', '10% Off'),
      ('rw20', '20% Off'),
      ('rw30', '30% Off');

INSERT INTO promos (issueDate, quantity, userID, rewardID)
            VALUES(DEFAULT, DEFAULT, 'DV2016', 'rw10'),
                  (DEFAULT, 1, 'DV2015', 'rw20'),
                  (DEFAULT, DEFAULT, 'DV2017', 'rw30'); 

然后我选择它:

SELECT userName, 
       rewards.rewardID, 
       rewards.rewardDescription, 
       promos.quantity 
  FROM user
  JOIN promos
    ON user.userID = promos.userID
  JOIN rewards
    ON promos.rewardID = promos.rewardID
 WHERE user.userID = 'DV2015';

不知道是建表错误还是插入记录错误，还是select语句错误。这是它的输出方式:

我只希望用户 ID 为“DV2015”的 Bob 在“rw20”上的“数量”为 1。 rw10 和 rw30 必须仍然有数量为 0。但是在插入数据时它给了他所有的奖励

Answer 1

你在这里有一个意外的交叉连接:

JOIN rewards
    ON promos.rewardID = promos.rewardID

这当然适用于所有促销记录。你的意思可能是:

JOIN rewards
    ON rewards.rewardID = promos.rewardID

如果您希望“rw10 和 rw30 的数量仍必须为 0”，您应该切换到 LEFT JOIN 而不是 (INNER) JOIN。