1.簡單的Demo語句進(jìn)行報(bào)表統(tǒng)計(jì)
2.基于簡單的Demo語句進(jìn)行升級(jí)

1.簡單的Demo語句進(jìn)行報(bào)表統(tǒng)計(jì)

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', '注冊(cè)');
INSERT INTO `test` VALUES ('1', '退出');
INSERT INTO `test` VALUES ('2', '登錄');
INSERT INTO `test` VALUES ('2', '注冊(cè)');
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', '注冊(cè)');
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', '司機(jī)');

2.效果圖如下：

效果圖.PNG

3.操作語句

select 
DISTINCT oo.fname "功能",
IF(ISNULL(aa.fname),null,'√') '免費(fèi)版',
IF(ISNULL(bb.fname),null,'√') '標(biāo)準(zhǔn)版',
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語句進(jìn)行升級(jí)

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

效果圖2.PNG

3.操作語句

select 
DISTINCT menu.m_name "功能",
IF(ISNULL(menu1.m_name),null,'√') '免費(fèi)版',
IF(ISNULL(menu2.m_name),null,'√') '標(biāo)準(zhǔn)版',
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.分析：

創(chuàng)建第一個(gè)子查詢：找到所有tempVersion=1的版本
SELECT
c_role.ro_sequence
FROM
c_role
WHERE
c_role.tempVersion = 1

創(chuàng)建第二個(gè)子查詢：找到對(duì)應(yīng)的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)

創(chuàng)建第三個(gè)子查詢：找到對(duì)應(yīng)的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))