Mysql 列转行统计查询 、行转列统计查询

Bertha 。 2022-06-17 02:29 225阅读 0赞









— ——————————————


— Table structure for TabName


— ——————————————


DROP 
TABLE 
IF EXISTS TabName;


CREATE 
TABLE 
TabName (


  
Id
int
(11)
NOT 
NULL 
AUTO_INCREMENT,


  
</code> <code style="white-space:pre-wrap; margin:0px!important; padding:0px!important; border:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.8em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-family:Consolas,'Bitstream Vera Sans Mono','Courier New',Courier,monospace!important; min-height:auto!important; color:rgb(0,0,255)!important; background:none!important">Name</code> <code style="white-space:pre-wrap; margin:0px!important; padding:0px!important; border:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.8em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-family:Consolas,'Bitstream Vera Sans Mono','Courier New',Courier,monospace!important; min-height:auto!important; background:none!important">
varchar
(20)
DEFAULT 
NULL
,


  
</code> <code style="white-space:pre-wrap; margin:0px!important; padding:0px!important; border:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.8em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-family:Consolas,'Bitstream Vera Sans Mono','Courier New',Courier,monospace!important; min-height:auto!important; color:rgb(0,0,255)!important; background:none!important">Date</code> <code style="white-space:pre-wrap; margin:0px!important; padding:0px!important; border:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.8em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-family:Consolas,'Bitstream Vera Sans Mono','Courier New',Courier,monospace!important; min-height:auto!important; background:none!important">
date 
DEFAULT 
NULL
,


  
Scount
int
(11)
DEFAULT 
NULL
,


  
PRIMARY 
KEY 
(Id)


) ENGINE=InnoDB AUTO_INCREMENT=9
DEFAULT 
CHARSET=utf8;


 


— ——————————————


— Records of TabName


— ——————————————


INSERT 
INTO 
TabName
VALUES 
(
‘1’
,
‘小说’
,
‘2013-09-01’
,
‘10000’
);


INSERT 
INTO 
TabName
VALUES 
(
‘2’
,
‘微信’
,
‘2013-09-01’
,
‘20000’
);


INSERT 
INTO 
TabName
VALUES 
(
‘3’
,
‘小说’
,
‘2013-09-02’
,
‘30000’
);


INSERT 
INTO 
TabName
VALUES 
(
‘4’
,
‘微信’
,
‘2013-09-02’
,
‘35000’
);


INSERT 
INTO 
TabName
VALUES 
(
‘5’
,
‘小说’
,
‘2013-09-03’
,
‘31000’
);


INSERT 
INTO 
TabName
VALUES 
(
‘6’
,
‘微信’
,
‘2013-09-03’
,
‘36000’
);


INSERT 
INTO 
TabName
VALUES 
(
‘7’
,
‘小说’
,
‘2013-09-04’
,
‘35000’
);


INSERT 
INTO 
TabName
VALUES 
(
‘8’
,
‘微信’
,
‘2013-09-04’
,
‘38000’
);


 


 


— ————————————


— 查看数据


— ————————————


SELECT  
*
from 
TabName ;

11224535-204bdbb1598048a9ae0ead597cab8fbc.jpg










— ————————————


— 列转行统计数据


— ————————————


SELECT 
Date 
,


MAX
(
CASE 
NAME 
WHEN 
‘小说’ 
THEN 
Scount
ELSE 
0
END 
) 小说,


MAX
(
CASE 
NAME 
WHEN 
‘微信’ 
THEN 
Scount
ELSE 
0
END 
) 微信


FROM 
TabName 


GROUP 
BY 
Date  
<br><br>

11224555-a8db9bd5e7ab4915b6b2dfd6b17f549b.jpg










 

　　










— ————————————


— 行转列统计数据


— ————————————

11225212-c4936a07df7f4577967e433a3d55c87d.jpg










<br>
select


    
Date
, group_concat(
NAME
,
‘总量:’
,Scount)
as 
b_str
from   
TabName


 
group 
by 
Date

11225221-fd2103f3bfc44c03be515c345bc6801f.jpg










select 
Date
,
NAME
, group_concat(
NAME
,
‘总量:’
,Scount)
as 
b_str
from   
TabName


 
group 
by 
Date 
,
NAME

发表评论

表情：
评论列表 （有 0 条评论，225人围观）

还没有评论，来说两句吧...

相关阅读