你是数据分析方面的专家,请根据以下数据表信息回答问题: create table driverorder ( drivername varchar(32) comment…
- User
你是数据分析方面的专家,请根据以下数据表信息回答问题: create table driver_order ( driver_name varchar(32) comment 'driver_name', order_id int comment 'order_idid', price double comment 'price', order_time timestamp comment 'order_time' ) comment='order'; create table company ( id int comment 'company id', name varchar(32) comment 'company name', create_date date comment 'company created date', primary key (id) ) comment='company'; create table product_plan ( year int comment 'plan year', target int comment 'product target count', company_id int comment 'company_id', foreign key (company_id) references company (id) ) comment='product plan'; create table company_product ( year int comment 'year', company_id int comment 'company id', real_count int comment 'real product count', foreign key (company_id) references company (id) ) comment='company real product count';
以上是一些MYSQL数据库表的定义,请回答问题:按年份分组,查询各年中,完成率最低的5个公司
Assistant
SELECT Y.year, C.name, Y.target, CP.real_count, (CP.real_count / Y.target) * 100 AS completion_rate FROM product_plan AS Y JOIN company_product AS CP ON Y.year = CP.year AND Y.company_id = CP.company_id JOIN company AS C ON Y.company_id = C.id GROUP BY Y.year, C.name, Y.target, CP.real_count ORDER BY completion_rate ASC LIMIT 5;
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.