我试着列出所有的销售额,这些销售额与客户ID有关,所以我希望看到

sale ID    customerID    customerfirstname    customerlastname
1          1             J                    Bloggs
2          2             G                    Green
3          1             J                    Bloggs

这是我所没有的一切,它不完全正确。。。
SELECT  sale.sale_id
        customer.customer_id ,
        customer.customer_first_name ,
        customer.customer_last_name ,
FROM mydb.customer , mydb.sale
WHERE sale.sale_id = customer_id

这是我的结构
CREATE TABLE `sale` (
  `sale_id` int(11) NOT NULL,
  `sale_items` int(3) NOT NULL,
  `sale_paid` tinyint(1) DEFAULT NULL,
  `customer_customer_id` int(11) NOT NULL,
  PRIMARY KEY (`sale_id`,`customer_customer_id`),
  KEY `fk_sale_customer1_idx` (`customer_customer_id`),
  CONSTRAINT `fk_sale_customer1` FOREIGN KEY (`customer_customer_id`) REFERENCES `customer` (`customer_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `customer` (
  `customer_id` int(11) NOT NULL,
  `customer_first_name` varchar(45) DEFAULT NULL,
  `customer_last_name` varchar(45) DEFAULT NULL,
  `customer_address1` varchar(45) DEFAULT NULL,
  `customer_address2` varchar(45) DEFAULT NULL,
  `customer_address3` varchar(45) DEFAULT NULL,
  `customer_post_code` varchar(45) DEFAULT NULL,
  `customer_debit` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

最佳答案

您在错误的字段上连接-两个表都有一个引用客户id的列,您应该使用该列。此外,在“选择”列表中的最后一项后面有一个多余的逗号,并且在第一项之后缺少一个逗号:

SELECT  sale.sale_id,
        customer.customer_id,
        customer.customer_first_name,
        customer.customer_last_name
FROM    customer, sale
WHERE   sale.customer_customer_id = customer.customer_id

但是,请注意,不赞成使用隐式联接(即from子句中有多个表),也不鼓励使用隐式联接,您可能应该改用显式联接:
SELECT  sale.sale_id,
        customer.customer_id,
        customer.customer_first_name,
        customer.customer_last_name
FROM    customer
JOIN    sale ON sale.customer_customer_id = customer.customer_id

关于mysql - 查找每个客户的所有销售额,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/34213366/

10-11 12:47
查看更多