有2个表:

CREATE TABLE INVOICES
(
    ID INT auto_increment,
    `DATE` DATE,
    Company INT,
    Amount DECIMAL(7,2),
    PRIMARY KEY (ID)
);

INSERT INTO INVOICES(`DATE`,Company,Amount)
VALUES
('2014-01-02', 222, 19500.00),
('2014-01-02', 222, 29205),
('2014-01-07', 222, 152.50),
('2014-01-07', 223, 6590),
('2014-01-07', 223, 4999),
('2014-01-09', 224, 720.60),
('2014-01-09', 225, 1100),
('2014-02-05', 226, 885),
('2014-02-05', 222, 9678.7);

create table Companies
(
    ID  int,
    Name    varchar(100),
    City    varchar(100),
    c_size varchar(100),
    PRIMARY KEY (ID)
);

insert into Companies(ID, Name, city, c_size)
values
(222, 'Karma LLC','CITY2', 'big'),
(223, 'Manny Ind.','CITY1', 'medium'),
(224, 'Random PLC','CITY1', 'medium'),
(225, 'Hijack LLC','CITY1', 'medium'),
(226, 'Travels LLC','CITY1', 'small'),
(227, 'Mirana Ind.','CITY2', 'small'),
(228, 'Polla Ind.','CITY3', 'small'),
(229, 'Americano LLC','CITY3', 'small'),
(230, 'Macaroni LLC','CITY4', 'small');


我的目标是编写查询,说明有多少公司在1月份的发票> 10000中支付了总和(金额)。
我知道有两家公司的总发票金额超过10k,但是idk如何查询,因此我决定编写查询:

select sum(brutto) as sum_january,Name,`DATE` from INVOICES,COMPANIES where INVOICES.Company=Companies.ID and MONTH(`DATE`)=01 group by Company;


对于每个公司来说,它用一月的sum(Amount)组成一个很好的表,所以我很容易像这样查询该表:

SELECT count(1) FROM new_table where sum_january > 10000;


但是当我尝试使:

CREATE new_table as select sum(brutto) as sum_january,Name,`DATE` from INVOICES,COMPANIES where INVOICES.Company=Companies.ID and MONTH(`DATE`)=01 group by Company;


发生错误:
“错误1064(42000):您的SQL语法有错误;请查看与您的MySQL服务器版本相对应的手册,以获取正确的语法,以在'new_table附近使用select sum(Amount)作为sum_january,Name,DATE发票,公司第一行“

我不知道该怎么办。
还有其他想法让所有拥有1月发票的公司> 10000吗?为什么我的方法行不通?

编辑:在上一个查询中的“创建”之后没有“表”字了。

最佳答案

这是一个select语句,以获取一月月份所有发票金额超过10000的公司:

select
  COMPANIES.Name,
  sum(INVOICES.Amount) as sum_january
from
  INVOICES
inner join
  COMPANIES on INVOICES.Company=COMPANIES.ID and
  MONTH(INVOICES.DATE)=1
GROUP BY
  COMPANIES.Name
HAVING
  sum(INVOICES.Amount) > 10000


如果要将其保存到另一个表中,请编写:

CREATE TABLE new_table as (select...)


要么

CREATE TEMPORARY TABLE IF NOT EXISTS new_table AS (selec...)

关于mysql - 创建查询结果表,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/23954673/

10-12 12:50
查看更多