我有三个表格,其中包含以下结构和信息：
CREATE TABLE customer ( customer_id mediumint(8) unsigned NOT NULL auto_increment,name varchar(50) NOT NULL,PRIMARY KEY (customer_id) ); INSERT INTO customer VALUES (1,'Dagmar'); INSERT INTO customer VALUES (2,'Dietmar'); INSERT INTO customer VALUES (3,'Sabine'); CREATE TABLE sales_cars ( sale_id mediumint(8) unsigned NOT NULL auto_increment,customer_id mediumint(8) unsigned NOT NULL,sale_amount decimal(10,2) NOT NULL,PRIMARY KEY (sale_id) ); INSERT INTO sales_cars VALUES (1,3,14.40); INSERT INTO sales_cars VALUES (2,1,28.30); INSERT INTO sales_cars VALUES (3,2,34.40); INSERT INTO sales_cars VALUES (4,25.60); CREATE TABLE sales_parts ( sale_id mediumint(8) unsigned NOT NULL auto_increment,PRIMARY KEY (sale_id) ); INSERT INTO sales_parts VALUES (1,68.20); INSERT INTO sales_parts VALUES (2,21.30); INSERT INTO sales_parts VALUES (3,54.40); INSERT INTO sales_parts VALUES (4,35.70);
sales_car和sales_parts保持客户的销售额.我们的想法是编写一个查询,该查询将特定客户的汽车和零件的“sale_amount”相加,并按结果对结果进行分组.
有人有一个建议我怎么能解决这个问题？
解决方法
您可能想尝试以下内容：
SELECT c.customer_id,tot_cars.total + tot_parts.total AS total_sales
FROM customer c
JOIN (
SELECT customer_id,SUM(sale_amount) total
FROM sales_cars
GROUP BY customer_id
) tot_cars ON (tot_cars.customer_id = c.customer_id)
JOIN (
SELECT customer_id,SUM(sale_amount) total
FROM sales_parts
GROUP BY customer_id
) tot_parts ON (tot_parts.customer_id = c.customer_id);
结果：
+-------------+-------------+ | customer_id | total_sales | +-------------+-------------+ | 1 | 64.00 | | 2 | 128.20 | | 3 | 90.10 | +-------------+-------------+ 3 rows in set (0.03 sec)
更新：继续下面的评论：
让我们从sale_date字段开始：
CREATE TABLE sales_cars ( sale_id mediumint(8) unsigned NOT NULL auto_increment,sale_date datetime NOT NULL,14.40,'2010-07-01 12:00:00'); INSERT INTO sales_cars VALUES (2,28.30,'2010-07-05 12:00:00'); INSERT INTO sales_cars VALUES (3,34.40,'2010-07-10 12:00:00'); INSERT INTO sales_cars VALUES (4,25.60,'2010-07-20 12:00:00');
要获取每个客户的最新销售日期,您可以将先前描述的查询与另一个派生表一起加入,如下所示：
SELECT c.customer_id,tot_cars.total + tot_parts.total AS total_sales,latest_sales.date AS latest_sale
FROM customer c
JOIN (
SELECT customer_id,SUM(sale_amount) total
FROM sales_parts
GROUP BY customer_id
) tot_parts ON (tot_parts.customer_id = c.customer_id)
JOIN (
SELECT customer_id,MAX(sale_date) date
FROM sales_cars
GROUP BY customer_id
) latest_sales ON (latest_sales.customer_id = c.customer_id);
结果：
+-------------+-------------+---------------------+ | customer_id | total_sales | latest_sale | +-------------+-------------+---------------------+ | 1 | 64.00 | 2010-07-05 12:00:00 | | 2 | 128.20 | 2010-07-20 12:00:00 | | 3 | 90.10 | 2010-07-01 12:00:00 | +-------------+-------------+---------------------+ 3 rows in set (0.07 sec)
你看到了这种模式吗？还有其他方法可以解决同样的问题,但加入派生表是一种非常简单直接的技术.
然后关于客户表中的更改,我假设您的意思是这样的：
CREATE TABLE customer ( customer_id mediumint(8) unsigned NOT NULL auto_increment,first_name varchar(50) NOT NULL,last_name varchar(50) NOT NULL,gender char(1) NOT NULL,'Joe','Doe','M'); INSERT INTO customer VALUES (2,'Jane','Smith','F'); INSERT INTO customer VALUES (3,'Peter','Brown','M');
要在MysqL中连接字符串字段,您只需使用CONCAT()函数：
SELECT CONCAT(c.first_name,' ',c.last_name) as full_name FROM customer c;
返回：
+-------------+ | full_name | +-------------+ | Jane Smith | | Peter Brown | | Joe Doe | +-------------+ 3 rows in set (0.01 sec)
要有条件地应用“先生”或“女士”,您可以使用CASE声明：
SELECT (CASE c.gender WHEN 'M' THEN 'Mr' WHEN 'F' THEN 'Ms' END) salutaiton,CONCAT(c.first_name,c.last_name) as full_name FROM customer c;
返回：
+------------+-------------+ | salutaiton | full_name | +------------+-------------+ | Ms | Jane Smith | | Mr | Peter Brown | | Mr | Joe Doe | +------------+-------------+ 3 rows in set (0.01 sec)
您还可以将两个字段连接在一起：
SELECT CONCAT((CASE c.gender WHEN 'M' THEN 'Mr' WHEN 'F' THEN 'Ms' END),c.first_name,c.last_name) as full_name FROM customer c;
返回：
+----------------+ | full_name | +----------------+ | Ms Jane Smith | | Mr Peter Brown | | Mr Joe Doe | +----------------+ 3 rows in set (0.00 sec)
最后,我们可以将其附加到我们的主查询中,CONCAT((CASE c.gender WHEN 'M' THEN 'Mr' WHEN 'F' THEN 'Ms' END),c.last_name) as full_name,MAX(sale_date) date FROM sales_cars GROUP BY customer_id ) latest_sales ON (latest_sales.customer_id = c.customer_id);
返回：
+-------------+----------------+-------------+---------------------+ | customer_id | full_name | total_sales | latest_sale | +-------------+----------------+-------------+---------------------+ | 1 | Mr Joe Doe | 64.00 | 2010-07-05 12:00:00 | | 2 | Ms Jane Smith | 128.20 | 2010-07-20 12:00:00 | | 3 | Mr Peter Brown | 90.10 | 2010-07-01 12:00:00 | +-------------+----------------+-------------+---------------------+ 3 rows in set (0.02 sec)
