11.25sql測試題目

建立資料庫demo，并建立相應的表：

CREATE DATABASE demo;
USE demo;
CREATE TABLE `allprovinces` (
  `name` varchar(255) NOT NULL,
  `abbr` varchar(255) NOT NULL
);
CREATE TABLE `products` (
  `name` varchar(255) NOT NULL,
  `price` float NOT  NULL,
  `craw_time` datetime NOT NULL,
  `market` varchar(255) NOT NULL,
  `province` varchar(255) not  NULL,
  `city` varchar(255) not  NULL
);      

load data local infile '/root/ml-1m/allprovinces.dat' into table allprovinces
CHARACTER SET utf8 
FIELDS TERMINATED BY '\t' 
  OPTIONALLY ENCLOSED BY '' 
  ESCAPED BY '\\' 
LINES TERMINATED BY '\n';


load data local infile '/root/ml-1m/products.dat' into table products
CHARACTER SET utf8 
FIELDS TERMINATED BY '\t' 
  OPTIONALLY ENCLOSED BY '' 
  ESCAPED BY '\\' 
LINES TERMINATED BY '\n';      

1.統計每個省份的農産品市場總數

SELECT province,COUNT(DISTINCT market)
 FROM products 
 GROUP BY province;      

2.根據農産品類型數量，統計排名前3名的省份

SELECT province,COUNT(DISTINCT NAME) num
 FROM products 
 GROUP BY province ORDER BY num DESC LIMIT 3 ;      

3.根據農産品類型數量，統計每個省份排名前3名的農産品市場

with t as (
select count(distinct name) product_count,market,province
from products
group by province,market
),
tt as(
select row_number()over(partition by province order by product_count desc) row_num,market,province
from t 
group by province,market
)
select province,market,row_num
from tt 
where row_num<=3;      

with t as (
select name,market,price
from products
where province='山西'
)
select name,
case when count(price)<=2 then round(sum(price)/count(price),3) else round((sum(price)-max(price)-min(price))/(count(price)-2),3) end as pavg
from t 
group by name;