如何使用mysql完成excel中的数据生成

1
2
#按行提取
SELECT * FROM data1 WHERE city='beijing';

1
2
#按位置提取
SELECT * FROM data1 LIMIT 2,5;

1
2
#按条件提取并计算
SELECT AVG(price) FROM data1 WHERE city='beijing' AND age<25;

1
2
#数据筛选AND
SELECT * FROM data1 WHERE city='shanghai' AND age>30;

1
2
#数据筛选IN
SELECT * FROM data1 WHERE city IN ('shanghai','beijing');

1
2
#数据筛选OR
SELECT * FROM data1 WHERE city='shanghai' OR age>30;

1
2
#数据筛选(不等于)
SELECT * FROM data1 WHERE city !='beijing';

1
2
#数据筛选like(模糊筛选)
SELECT * FROM data1 WHERE city LIKE 'bei%';

1
2
#筛选后计数 countif
SELECT COUNT(id) AS id_count FROM data1 WHERE city='shanghai'AND age>30;

1
2
#筛选后求和 sumtif
SELECT SUM(price) AS price FROM data1 WHERE city='beijing' AND age<30;

1
2
#筛选后求均值 averageif
SELECT AVG(price) AS avg_price FROM data1 WHERE city !='beijing';

1
2
#单列分类汇总
SELECT city,COUNT(id) AS id_count FROM data1 GROUP BY city ORDER BY id_count;

1
2
#多列分类汇总
SELECT city,colour,ROUND(SUM(price),2) AS id_count FROM data1 GROUP BY city,colour;

1
2
#查看原始数据表
SELECT * FROM data1;

1
2
3
4
5
6
7
8
9
10
#使用CASE WHEN进行数据透视
CREATE VIEW data_Items AS (
 SELECT
 data1.city,
 CASE WHEN colour = "A" THEN price END AS A,
 CASE WHEN colour = "B" THEN price END AS B,
 CASE WHEN colour = "C" THEN price END AS C,
 CASE WHEN colour = "F" THEN price END AS F
 FROM data1
);

1
2
#查看结果
SELECT * FROM data_Items;

1
2
3
4
5
6
7
8
9
10
11
#对字段进行求和汇总
CREATE VIEW data1_Extended_Pivot AS (
 SELECT
 city,
 SUM(A) AS A,
 SUM(B) AS B,
 SUM(C) AS C,
 SUM(F) AS F
 FROM data_Items
 GROUP BY city
);

1
2
#查看结果
SELECT * FROM data1_Extended_Pivot;

1
2
3
4
5
6
7
8
9
10
#对空值进行处理
CREATE VIEW data1_Extended_Pivot_Pretty AS (
 SELECT 
 city, 
 COALESCE(A, 0) AS A, 
 COALESCE(B, 0) AS B, 
 COALESCE(C, 0) AS C,
 COALESCE(F, 0) AS F
 FROM data1_Extended_Pivot
);

1
2
#查看数据透视结果
SELECT * FROM data1_Extended_Pivot_Pretty;