我试着列出所有的销售额,这些销售额与客户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/