分类: Mysql/postgreSQL
2015-09-16 21:00:50
-- ----------------------------
-- Table structure for `TabName`
-- ----------------------------
DROP
TABLE
IF EXISTS `TabName`;
CREATE
TABLE
`TabName` (
`Id`
int
(11)
NOT
NULL
AUTO_INCREMENT,
`
Name
`
varchar
(20)
DEFAULT
NULL
,
`
Date
`
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 ;
-- ------------------------
-- 列转行统计数据
-- ------------------------
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>
|
|
-- ------------------------
-- 行转列统计数据
-- ------------------------
|
<br> select
Date , group_concat( NAME , '总量:' ,Scount) as b_str from TabName
group by Date
|
select
Date
,
NAME
, group_concat(
NAME
,
'总量:'
,Scount)
as
b_str
from
TabName
group
by
Date
,
NAME