目的:查询结果字段名,根据内容时间维度进行相应变更;举例如下:
我补充一下:所用数据库是Mysql;现在用查询结果将日期字段列行转化后字段名只能手动输入,业务场景是自动根据系统日期调取数据,所以需要将字段名动态显示以当前日期为参考的动态字段名。另表只有一张,建表语句,表数据,查询结果如下代码:
##建表
CREATE TABLE uv (日期 date,省份 varchar(20),APP varchar(20));
##插入数据
INSERT INTO uv VALUES ('2017-11-03', '四川','12345'), ('2017-11-03', '广州','21345'),('2017-11-04', '四川','42345'), ('2017-11-04', '广州','21345'),('2017-11-05', '四川','2345'), ('2017-11-05', '广州','2145'),('2017-11-06', '四川','9345'), ('2017-11-06', '广州','41345'),('2017-11-07', '四川','1455'), ('2017-11-07', '广州','53211'),('2017-11-08', '四川','56772'), ('2017-11-08', '广州','1567'),('2017-11-09', '四川','56889'), ('2017-11-09', '广州','87654'),('2017-11-10', '四川','98643'), ('2017-11-10', '广州','210861');
##想实现查询结果表的临时展示
select 省份,sum(case when 日期=date_sub(curdate(),interval 7 day)then APP end)as '2017-11-04',sum(case when 日期=date_sub(curdate(),interval 6 day)then APP end)as '2017-11-05',sum(case when 日期=date_sub(curdate(),interval 5 day)then APP end)as '2017-11-06',sum(case when 日期=date_sub(curdate(),interval 4 day)then APP end)as '2017-11-07',sum(case when 日期=date_sub(curdate(),interval 3 day)then APP end)as '2017-11-08',sum(case when 日期=date_sub(curdate(),interval 2 day)then APP end)as '2017-11-09',sum(case when 日期=date_sub(curdate(),interval 1 day)then APP end)as '2017-11-10'from uv where 日期>=date_sub(curdate(),interval 7 day) group by 省份