001--MySQL报表查询

1.简单的Demo语句进行报表统计
2.基于简单的Demo语句进行升级

1.简单的Demo语句进行报表统计

1.sql语句

SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for test
-- ----------------------------
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
  `fid` int(10) DEFAULT NULL,
  `fname` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of test
-- ----------------------------
INSERT INTO `test` VALUES ('1', '登录');
INSERT INTO `test` VALUES ('1', '注册');
INSERT INTO `test` VALUES ('1', '退出');
INSERT INTO `test` VALUES ('2', '登录');
INSERT INTO `test` VALUES ('2', '注册');
INSERT INTO `test` VALUES ('2', '退出');
INSERT INTO `test` VALUES ('2', '修改');
INSERT INTO `test` VALUES ('2', '百度');
INSERT INTO `test` VALUES ('3', '登录');
INSERT INTO `test` VALUES ('3', '注册');
INSERT INTO `test` VALUES ('3', '退出');
INSERT INTO `test` VALUES ('3', '修改');
INSERT INTO `test` VALUES ('3', '百度');
INSERT INTO `test` VALUES ('3', '新浪');
INSERT INTO `test` VALUES ('3', '司机');

2.效果图如下：

效果图.PNG

3.操作语句

select 
DISTINCT oo.fname "功能",
IF(ISNULL(aa.fname),null,'√') '免费版',
IF(ISNULL(bb.fname),null,'√') '标准版',
IF(ISNULL(cc.fname),null,'√') '旗舰版'
FROM
test oo
left JOIN
test aa
ON oo.fname = aa.fname and aa.fid = 1
left JOIN
test bb
ON oo.fname = bb.fname AND bb.fid = 2
LEFT  JOIN
test cc
ON oo.fname = cc.fname AND cc.fid = 3
ORDER BY aa.fname DESC,bb.fname DESC,cc.fname DESC

2.基于简单的Demo语句进行升级

1.sql语句：http://pan.baidu.com/s/1boBPEB9
2.效果图：

效果图2.PNG

3.操作语句

select 
DISTINCT menu.m_name "功能",
IF(ISNULL(menu1.m_name),null,'√') '免费版',
IF(ISNULL(menu2.m_name),null,'√') '标准版',
IF(ISNULL(menu3.m_name),null,'√') '旗舰版'
FROM
c_menu menu

left JOIN
(SELECT
c_menu.m_name
FROM
c_menu
WHERE
c_menu.m_sequence
in (SELECT
c_rm.m_sequence
FROM
c_rm
WHERE
c_rm.ro_sequence in (SELECT
c_role.ro_sequence
FROM
c_role
WHERE
c_role.tempVersion = 1))) menu1
ON menu.m_name = menu1.m_name

left JOIN
(SELECT
c_menu.m_name
FROM
c_menu
WHERE
c_menu.m_sequence
in (SELECT
c_rm.m_sequence
FROM
c_rm
WHERE
c_rm.ro_sequence in (SELECT
c_role.ro_sequence
FROM
c_role
WHERE
c_role.tempVersion = 2))) menu2
ON menu.m_name = menu2.m_name

left JOIN
(SELECT
c_menu.m_name
FROM
c_menu
WHERE
c_menu.m_sequence
in (SELECT
c_rm.m_sequence
FROM
c_rm
WHERE
c_rm.ro_sequence in (SELECT
c_role.ro_sequence
FROM
c_role
WHERE
c_role.tempVersion = 3))) menu3
ON menu.m_name = menu3.m_name


WHERE
menu.m_sequence
in (SELECT
c_rm.m_sequence
FROM
c_rm
WHERE
c_rm.ro_sequence in (SELECT
c_role.ro_sequence
FROM
c_role
WHERE
c_role.tempVersion IS NOT NULL)) 

4.分析：

创建第一个子查询：找到所有tempVersion=1的版本
SELECT
c_role.ro_sequence
FROM
c_role
WHERE
c_role.tempVersion = 1

创建第二个子查询：找到对应的m—sequence
SELECT
c_rm.m_sequence
FROM
c_rm
WHERE
c_rm.ro_sequence in (SELECT
c_role.ro_sequence
FROM
c_role
WHERE
c_role.tempVersion = 1)

创建第三个子查询：找到对应的m—name
SELECT
c_menu.m_name
FROM
c_menu
WHERE
c_menu.m_sequence
in (SELECT
c_rm.m_sequence
FROM
c_rm
WHERE
c_rm.ro_sequence in (SELECT
c_role.ro_sequence
FROM
c_role
WHERE
c_role.tempVersion = 1))