mysql - 通过唯一关键字并在列中添加总计来连接两个 Mysql 表

Question

我正在尝试将每个位置的每日总计放入另一个表中。

结构如下:

报告位置温度:

Table_Name:           Date:                              Total_Count:
London                2013-05-26 10:49:53                5000
London                2013-05-26 10:49:53                2000
Birmingham            2013-05-26 10:49:53                1000
London                2013-05-26 10:49:53                5000
Manchester            2013-05-26 10:49:53                50
Birmingham            2013-05-26 10:49:53                500

每日报告位置总数:

Table_Name:           Date:                              Total_Count:
London                2013-05-26 23:55:00                12000
Manchester            2013-05-26 23:55:00                50
Birmingham            2013-05-26 23:55:00                1500

我仍在学习 Mysql。

这是我尝试过的查询，但它只为每个唯一的 Table_Name 选择一列:

UPDATE reports_Location_total_daily j1 INNER JOIN reports_location_temp l1 ON j1.Table_Name = l1.Table_Name SET j1.Total_Count = l1.Total_Count    

感谢您对此的帮助。

Answer 1

CREATE TABLE sales
(id INT NOT NULL AUTO_INCREMENT,
location VARCHAR(40), 
today DATETIME NOT NULL,
sales INT NOT NULL,
PRIMARY KEY (id)
)
;


INSERT sales (location,today,sales) VALUES ('London','2013-05-26',2000);
INSERT sales (location,today,sales) VALUES ('Birm','2013-05-26',1000);
INSERT sales (location,today,sales) VALUES ('London','2013-05-26',1500);
INSERT sales (location,today,sales) VALUES ('London','2013-05-24',100);
INSERT sales (location,today,sales) VALUES ('Birm','2013-05-24',200);
INSERT sales (location,today,sales) VALUES ('London','2013-05-24',300);

CREATE TABLE daily_totals
(id INT NOT NULL AUTO_INCREMENT,
location VARCHAR(40), 
today DATETIME NOT NULL,
totalsales INT NOT NULL,
PRIMARY KEY (id)
)
;

DELETE FROM daily_totals;

INSERT INTO daily_totals (location,today,totalsales)
SELECT location,
DATE(today),
SUM(sales)
FROM sales
GROUP BY location,DATE(today)