三表内连接

Question

我有三个桌子“用户”，“投标”和“项目”。我需要找到查询，以获得完成的项目拍卖为买方。如何在我的数据库中找到这一点的方法是: item.received=1和u.userid=X (这个X将从我的PHP中得到最高出价的userID )。(请注意，received=1意味着截止日期已经过了，所以不再需要检查了)。

该系统的简短说明:这是一个拍卖网站，用户在该网站上对物品和用户个人账户页面进行投标，我想显示他购买的拍卖数量(并已处理完毕)。

这三个表如下所示：

CREATE TABLE user (
    userid INT NOT NULL AUTO_INCREMENT,
    username CHAR(30)  NOT NULL UNIQUE,
    password CHAR(32)  NOT NULL,
    firstname CHAR(30)  NOT NULL,
    lastname CHAR(30)  NOT NULL,
    gender CHAR(1) NOT NULL,
    email CHAR(50)  NOT NULL UNIQUE,
    birthdate DATE  NOT NULL,
    addressid INT NOT NULL,
    picture CHAR(50),
    lastlogin TIMESTAMP NOT NULL,
    role CHAR(30),
    paymentid INT NOT NULL,

    PRIMARY KEY (userid),
    FOREIGN KEY (addressid)
     REFERENCES address(addressid),
    FOREIGN KEY (paymentid)
     REFERENCES payment(paymentid)
);

CREATE TABLE item (
    itemid INT NOT NULL AUTO_INCREMENT,
    name CHAR(40) NOT NULL,
    description CHAR(255) NOT NULL,
    originalpurchasedate DATE,
    deadline TIMESTAMP NOT NULL,
    minprice DOUBLE,
    received BOOLEAN NOT NULL,
    dateadded TIMESTAMP NOT NULL,
    openbidding BOOLEAN NOT NULL,
    categoryid INT NOT NULL,
    ownerid INT NOT NULL,

    PRIMARY KEY (itemid),
    FOREIGN KEY (categoryid)
     REFERENCES category(categoryid),
    FOREIGN KEY (ownerid)
     REFERENCES user(userid)
);

CREATE TABLE bidding (
    userid INT NOT NULL,
    itemid INT NOT NULL,
    amount DOUBLE,
    bidtime TIMESTAMP NOT NULL,

    FOREIGN KEY (userid)
     REFERENCES user(userid),
    FOREIGN KEY (itemid)
     REFERENCES item(itemid)
);

我已经拥有的故障解决方案是:结果是3行，结果是:3，1，5。我希望得到的解决方案只需要一行，包含不同项目的数量。

SELECT DISTINCT COUNT(u.userid) FROM `item` i 
    INNER JOIN `bidding` b ON i.itemid = b.itemid
    INNER JOIN `user` u ON b.userid = u.userid
WHERE i.received=1 AND u.userid=2
GROUP BY i.itemid

Answer 1

您需要将查询更改为在userid上分组，而不是item id，并计算不同的项而不是不同的用户。

SELECT DISTINCT COUNT(i.itemid) FROM `item` i 
    INNER JOIN `bidding` b ON i.itemid = b.itemid
    INNER JOIN `user` u ON b.userid = u.userid
WHERE i.received=1 AND u.userid=2
GROUP BY u.userid