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))
