mysql - 未将值插入到正确的记录/ s MySQL
问题描述
将值插入表时,不仅仅是将值分配给单个记录,而是将其分配给所有记录:
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。但是在插入数据时,它会给他所有的奖励
解决方案
你在这里有一个意外的交叉连接:
JOIN rewards
ON promos.rewardID = promos.rewardID
这当然适用于所有促销记录。你可能的意思是:
JOIN rewards
ON rewards.rewardID = promos.rewardID
如果你想要“rw10 和 rw30 的数量必须仍然为 0”,你应该切换到LEFT JOIN
而不是(INNER) JOIN
.
推荐阅读
- tomcat - Bitnami Tomcat 版本 8.5.16-0(非常旧的版本),如何通过服务器名进行代理
- angular - 更新已安装的 PWA 目标 URL(或使用新的目标 URL 重新安装应用程序)
- python - 其他数组修改的numpy数组
- javascript - 将函数及其参数作为参数传递给另一个
- java - Apache-POI/ Java/ leaving out rows when writing to an Excel file
- java - Experiencing Code Coverage issue only when using Stream
- python - Can't print class fucntion return with .format()
- topology - continuous onto function from (0,1)x(0,1) to [0,1]x[0,1]
- visual-studio - Copy dependency assemblies into nuget package
- java - How to open a document on an alert dialog using PDFView on the app without using Intents