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語句進行更新
效果圖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))