未将值插入到正确的记录中

5m1hhzi4 于 2021-06-20 发布在 Mysql

关注(0)|答案(4)|浏览(287)

在表中插入值时，不只是将值赋给单个记录，而是将其赋给所有记录：

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。然而，当插入数据时，它给了他所有的回报

sql mysql

来源：https://stackoverflow.com/questions/52114648/values-not-being-inserted-into-the-right-record-s-mysql

4条答案

按热度按时间

9ceoxa921#

这里有一个意外的交叉连接：

JOIN rewards
    ON promos.rewardID = promos.rewardID

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

JOIN rewards
    ON rewards.rewardID = promos.rewardID

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

赞(0）回复(0）举报 2021-06-21

tkclm6bt2#

您在表中插入数据时出错了

insert into table_name values()//if you provide values all the column

否则

insert into table_name (col1,col2...coln) values('use1','use2'....n)

因此，在您的情况下，用户表中的值将如下所示插入

INSERT INTO user values(1,'user_name1'),(2,'user_name2');

其他两个表（奖励、促销）数据插入过程有效

赞(0）回复(0）举报 2021-06-20

cxfofazt3#

改为从promos表加入

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

赞(0）回复(0）举报 2021-06-20

n3ipq98p4#

从你的预期结果来看，你可以试试这个。 CROSS JOIN 由 user 以及 rewards 笛卡尔产品结果表。
那么 promos table OUTER JOIN 基于 CROSS JOIN 结果集。

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

CREATE TABLE promos
(   
    issueDate DATETIME DEFAULT CURRENT_TIMESTAMP,
    expiryDateOfReward DATETIME ,  
    quantity int DEFAULT 0,
    userID VARCHAR(50),
    rewardID VARCHAR(5)
);

    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(0, 0, 'DV2016', 'rw10'),
                  (0, 1, 'DV2015', 'rw20'),
                  (0, 0, 'DV2017', 'rw30');

查询1：

SELECT userName, 
       r.rewardID, 
       r.rewardDescription, 
       coalesce(p.quantity,0) quantity
  FROM user u CROSS JOIN rewards r
  LEFT JOIN promos p
    ON u.userID = p.userID and r.rewardID = p.rewardID
 WHERE u.userID = 'DV2015'

结果：

| userName | rewardID | rewardDescription | quantity |
|----------|----------|-------------------|----------|
|      Bob |     rw20 |           20% Off |        1 |
|      Bob |     rw10 |           10% Off |        0 |
|      Bob |     rw30 |           30% Off |        0 |

赞(0）回复(0）举报 2021-06-20

我来回答

未将值插入到正确的记录中

4条答案

相关问题

热门标签

最新问答