抱歉发了这么长的帖子，但涉及的table相当大。
当我运行下面的查询时，通常需要大约1米才能运行。但是，当我删除相关的子查询时，我将查询缩短到15秒。所以我认为这才是真正的问题。
问题是，如果没有相关查询，我真的不知道如何获得求和结果。
我正在尝试运行的查询
SELECT cl_clients.vat as association_vat, cl_clients.name as association_name, cl_clients_with_regions.city_id, cl_clients_with_regions.nut_1, cl_clients_with_regions.nut_2, cl_clients_with_regions.nut_3,
company.vat as company_vat, company.name, company.section, company.division, company.cae,
SUM((
SELECT SUM((COALESCE((cl_finances.sales_community_market), 0) + COALESCE((cl_finances.sales_extra_market), 0)))
from cl_finances
where cl_finances.vat = company.vat
and cl_finances.year = 2018
)) as total_sum
FROM `cl_clients_with_regions`
JOIN `cl_client_intervention_areas` ON `cl_client_intervention_areas`.`city_id` = `cl_clients_with_regions`.`city_id`
JOIN `cl_clients` ON `cl_clients`.`vat` = `cl_client_intervention_areas`.`client_vat`
INNER JOIN cl_clients_with_regions company ON cl_clients_with_regions.vat = company.vat
WHERE `cl_clients_with_regions`.`encrypted_vat` IS NOT NULL
AND `cl_clients_with_regions`.`country` IS NOT NULL
AND `cl_clients_with_regions`.`cae` IS NOT NULL
AND `cl_clients_with_regions`.`cae` != ''
AND `cl_clients_with_regions`.`division` IS NOT NULL
AND `cl_clients_with_regions`.`section` = 'A'
AND `cl_clients_with_regions`.`nut_2` = 'Centro'
GROUP BY association_vat;cl客户端创建表语法
create table cl_clients
(
vat varchar(20) default '' not null,
encrypted_vat varchar(32) null,
temporary_vat enum ('0', '1', '') default '0' null,
sig_id int null,
phc_id int null,
client_manager int null,
name varchar(300) null,
brand varchar(255) null,
`group` varchar(200) null,
class_internacional varchar(45) null,
logoimage varchar(400) null,
social_capital varchar(100) null,
address varchar(300) null,
gps varchar(25) null,
parish varchar(70) null,
zip_code varchar(10) null,
city int null,
district int null,
country int default 75 not null,
headquarter int null,
person_title varchar(7) null,
person_contact varchar(350) null,
person_phone varchar(20) null,
person_cell varchar(15) null,
person_email varchar(150) null,
person_function varchar(150) null,
language varchar(10) null,
phone varchar(20) null,
mobile_phone varchar(20) null,
fax varchar(20) null,
email varchar(50) null,
email_alternative varchar(50) null,
website varchar(300) null,
pme varchar(500) null,
pme_lider varchar(500) null,
cae varchar(15) null,
cae_2 varchar(15) null,
certified enum ('1', '0', '') null,
sector varchar(350) null,
workers int null,
foundation_date date null,
size enum ('Micro Empresa', 'Pequena empresa', 'Média empresa', 'Grande empresa', 'Não aplicável', 'PME', '') default '' null,
company_type varchar(455) default 'Empresa' null,
type enum ('novo', 'antigo', 'analizar', '') default 'analizar' null,
entity enum ('client', 'prospect', 'suplier', 'potential', '') default 'prospect' null,
client_to_country int null,
partner enum ('1', '0', '') default '0' not null,
partner_dp enum ('0', '1', '') default '0' null,
lucrative enum ('1', '0', '') default '1' not null,
dun varchar(100) null,
debt enum ('50k', '100k', '500k', '1m', '5m', '+5m', '') null,
bankruptcy enum ('1', '0', '') default '0' null,
competitors enum ('1', '0', '') default '0' null,
dun_date date null,
bank_1 varchar(30) null,
bank_2 varchar(30) null,
bank_3 varchar(30) null,
status enum ('0', '1', '') default '1' null,
followup enum ('0', '1', '') default '1' null,
classification enum ('Prestige', 'Premium', 'Current', '') default 'Current' null,
associated enum ('0', '1', '') default '0' null,
industry_id int null,
valid enum ('0', '1', '') default '1' null,
imported_at timestamp null,
modified_by int default 0 null,
responsible_id int null,
partner_type int null,
iberinform_id int null,
iberinform_date timestamp null,
legal_form text null,
going_concern text null,
score int null,
sector_score int null,
tax_status text null,
irc_debtors_ledger enum ('0', '1') null,
ss_debtors_ledger enum ('0', '1') null,
association_class_id int null,
nature enum ('business', 'commercial', 'sectorial', 'other') null,
geographic_scope enum ('national', 'regional', 'local') null,
intervention_nut enum ('nut_1', 'nut_2', 'nut_3') null,
ch_is_associated enum ('0', '1') null,
bi enum ('0', '1') default '0' null,
updated_at timestamp null,
constraint encrypted_vat_UNIQUE
unique (encrypted_vat),
constraint vat
unique (vat),
constraint cl_clients_cl_caes_cae_fk
foreign key (cae) references cl_caes (cae)
on update cascade,
constraint clients_industry_id_fk
foreign key (industry_id) references cl_industries (id),
constraint cltns_asso_clss_id_fk
foreign key (association_class_id) references cl_association_classifications (id)
on update cascade on delete set null,
constraint country_fk_id
foreign key (country) references cl_countries (id)
on update cascade on delete cascade
);
create index cl_client_name_idx
on cl_clients (name);
create index cl_clients_bi_idx
on cl_clients (bi);
create index cl_clients_brand_idx
on cl_clients (brand);
create index cl_clients_company_type_idx
on cl_clients (company_type);
create index cl_clients_geo_index
on cl_clients (city, district);
create index cl_clients_geographic_scope_idx
on cl_clients (geographic_scope);
create index cl_clients_group_idx
on cl_clients (`group`);
create index cl_clients_nature_idx
on cl_clients (nature);
create index cltns_asso_clss_id_fk_idx
on cl_clients (association_class_id);
create index country_idx
on cl_clients (country);
create index fk_clients_industries1_idx
on cl_clients (industry_id);
alter table cl_clients
add primary key (vat);查询中使用的视图
create view cl_clients_with_regions as
select `grupoch`.`cl_clients`.`vat` AS `vat`,
`grupoch`.`cl_clients`.`encrypted_vat` AS `encrypted_vat`,
`grupoch`.`cl_clients`.`name` AS `name`,
`grupoch`.`cl_clients`.`brand` AS `brand`,
`grupoch`.`cl_clients`.`country` AS `country`,
`grupoch`.`cl_clients`.`district` AS `district`,
`grupoch`.`cl_clients`.`city` AS `city`,
`grupoch`.`cl_cities`.`id` AS `city_id`,
`grupoch`.`cl_cities`.`name` AS `city_name`,
`grupoch`.`cl_cities`.`nut_1` AS `nut_1`,
`grupoch`.`cl_cities`.`nut_2` AS `nut_2`,
`grupoch`.`cl_cities`.`nut_3` AS `nut_3`,
`grupoch`.`cl_clients`.`cae` AS `cae`,
`grupoch`.`cl_caes`.`description` AS `cae_designation`,
`grupoch`.`cl_caes_divisions`.`division` AS `division`,
`grupoch`.`cl_caes_divisions`.`division_designation` AS `division_designation`,
`grupoch`.`cl_caes_divisions`.`section` AS `section`,
`grupoch`.`cl_caes_divisions`.`section_designation` AS `section_designation`
from (((`grupoch`.`cl_clients` join `grupoch`.`cl_cities` on ((
(`grupoch`.`cl_clients`.`city` = `grupoch`.`cl_cities`.`city_cod`) and (`grupoch`.`cl_clients`.`district` =
`grupoch`.`cl_cities`.`district_id`)))) join `grupoch`.`cl_caes` on ((`grupoch`.`cl_caes`.`cae` = `grupoch`.`cl_clients`.`cae`)))
join `grupoch`.`cl_caes_divisions`
on ((`grupoch`.`cl_caes_divisions`.`division` = `grupoch`.`cl_caes`.`division_id`)))
where ((`grupoch`.`cl_clients`.`country` in (75, 185, 186)) and (`grupoch`.`cl_clients`.`vat` is not null));cl创建表语法
create table cl_finances
(
vat varchar(20) not null,
year int not null,
workers int null,
sells varchar(300) null,
sells_variation varchar(300) null,
international_sells varchar(300) null,
international_sells_variation varchar(300) null,
liquid_results varchar(300) null,
liquid_results_variation varchar(300) null,
capital varchar(300) null,
active_liquid varchar(300) null,
financial_autonomy varchar(300) null,
modified_by int default 0 null,
sales_profitability decimal(13, 2) null,
return_on_capital decimal(13, 2) null,
sales_community_market decimal(13, 2) null,
sales_extra_market decimal(13, 2) null,
created_at timestamp null,
updated_at timestamp null,
primary key (vat, year),
constraint clients_finances_vat
foreign key (vat) references cl_clients (vat)
on update cascade on delete cascade
);
create index cl_finances_year_idx
on cl_finances (year);
create index vat
on cl_finances (vat);
## Create table syntax for cl_client_intervention_areas
create table cl_client_intervention_areas
(
client_vat varchar(20) not null,
city_id int not null,
constraint fk_cl_client_intervention_areas_cl_cities1
foreign key (city_id) references cl_cities (id)
on update cascade on delete cascade,
constraint fk_cl_client_intervention_areas_cl_clients1
foreign key (client_vat) references cl_clients (vat)
on update cascade on delete cascade
);
create index fk_cl_client_intervention_areas_cl_cities1_idx
on cl_client_intervention_areas (city_id);
create index fk_cl_client_intervention_areas_cl_clients1_idx
on cl_client_intervention_areas (client_vat);这个查询的解释结果很大
2条答案
按热度按时间svgewumm1#
我相信GROUPBY子句中的每行都会执行一次子查询。你能试试下面的方法吗。
我所做的更改是将相关查询作为内联视图执行，然后与主表/视图-company.vat联接
klsxnrf12#
进一步解释上述答案：
子查询永远不应该在select子句中，因为它们对返回/或计算返回的每个记录执行一次。这会导致查询随着数据库的增大而变得越来越慢。
相反，子查询应该在from子句中，子查询的结果应该连接到并被选中。