新实现的一个四表行转列视图查询
先上个建库建表插入数据:
/*
SQLyog v10.2
MySQL - 5.1.73-community-log : Database - zj
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;
/*!40101 SET SQL_MODE=''*/;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`zj` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `zj`;
/*Table structure for table `roomprice` */
DROP TABLE IF EXISTS `roomprice`;
CREATE TABLE `roomprice` (
`rtID` int(11) DEFAULT NULL,
`tzID` int(11) DEFAULT NULL,
`price` decimal(10,0) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `roomprice` */
insert into `roomprice`(`rtID`,`tzID`,`price`) values (1,1,'10'),(1,2,'20'),(1,3,'30'),(1,4,'40'),(2,1,'20'),(2,2,'30'),(2,3,'40'),(2,4,'50'),(3,1,'30'),(3,2,'40'),(3,3,'50'),(3,4,'60'),(4,1,'40'),(4,2,'50'),(4,3,'60'),(4,4,'70'),(5,4,'80'),(6,4,'100'),(6,5,'200');
/*Table structure for table `roomtype` */
DROP TABLE IF EXISTS `roomtype`;
CREATE TABLE `roomtype` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(200) DEFAULT NULL,
`desc` varchar(200) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
/*Data for the table `roomtype` */
insert into `roomtype`(`id`,`name`,`desc`) values (1,'小包','aaa'),(2,'中包','aaa'),(3,'大包','aaa'),(4,'豪华包','aaa'),(5,'总统包','aaa'),(6,'超女包','aaa');
/*Table structure for table `timezonetype` */
DROP TABLE IF EXISTS `timezonetype`;
CREATE TABLE `timezonetype` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(200) DEFAULT NULL,
`desc` varchar(200) DEFAULT NULL,
`weekID` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
/*Data for the table `timezonetype` */
insert into `timezonetype`(`id`,`name`,`desc`,`weekID`) values (1,'上午','aaa',1),(2,'下午','aaa',1),(3,'黄金','aaa',1),(4,'夜场','aaa',1),(5,'上午','aaa',2),(6,'下午','aaa',2),(7,'黄金','aaa',2),(8,'夜场','aaa',2),(9,'絯还1','aaa',1);
/*Table structure for table `weeks` */
DROP TABLE IF EXISTS `weeks`;
CREATE TABLE `weeks` (
`weeks_ID` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(200) DEFAULT NULL,
PRIMARY KEY (`weeks_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
/*Data for the table `weeks` */
insert into `weeks`(`weeks_ID`,`name`) values (1,'星期一'),(2,'星期二'),(3,'星期三'),(4,'星期四'),(5,'星期五'),(6,'星期六'),(7,'星期日');
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
再上个原始查询:
SELECT * FROM weeks a LEFT JOIN (
SELECT * FROM
(
SELECT rp.`price`,rp.`rtID`,rp.`tzID`, rt.`name` roname ,tz.`name` zonename,tz.weekID
FROM roomprice rp
LEFT JOIN roomtype rt ON rp.`rtID` = rt.`id`
LEFT JOIN timezonetype tz ON rp.`tzID` = tz.`id`
) aa ) bb
ON a.weeks_ID = bb.weekID
表示了每天中，所有当天具有的场次，以及场次对应各个包房房型的房价:(4个表 weeks （星期一到星期日） roomtype （房型表）timezonetype （场次表） roomprice（房型场次房价的中间表）)
现在需求来了，我需要按照一周七天(星期一~星期日)分组，将房型名称做列，显示出每个包房类型在每天各个场次的开房价格，类似下面这种视图:
需求来了，思路也来了，就是行专列查询啊，轻车熟路的算法(但记性不好，所以参考了个行专列的文章，里面介绍了3种方法，用1分钟扫了一遍，果断采用方法2，另外两种太长果断不用，奥卡姆剃刀嘛，如无必要勿增实体):
经过两次调整和测试，最终写出如下实现，
SELECT * FROM weeks we
LEFT JOIN
(
SELECT DISTINCT a.name zonename,a.weekID,
(SELECT price FROM roomprice b WHERE a.id=b.tzID AND b.rtID = 1) AS '小包',
(SELECT price FROM roomprice b WHERE a.id=b.tzID AND b.rtID = 2) AS '中包',
(SELECT price FROM roomprice b WHERE a.id=b.tzID AND b.rtID = 3) AS '大包',
(SELECT price FROM roomprice b WHERE a.id=b.tzID AND b.rtID = 4) AS '豪华包',
(SELECT price FROM roomprice b WHERE a.id=b.tzID AND b.rtID = 5) AS '总统包',
(SELECT price FROM roomprice b WHERE a.id=b.tzID AND b.rtID = 6) AS '超女包'
FROM timezonetype a
) aa
ON we.weeks_ID = aa.weekID
这是最终形态的语句，中间的那一串括号(select price ......)语句，是根据房型表数据动态生成的列语句(程序中需要事先 select * from roomtype 哦，然后再循环生成这几行)。
思路就是纵列的星期是从字典表weeks中查询出来的行头，数据列是房型，根据中间表roomprice填写矩阵中对应的单元格。
前端实现就不先管了，绑定一下TABLE的JSON，再维护个房型表的集合做ID的映射，然后提交中间表更新就行了，不过感觉维护那个集合还是挺坑的，有的框架支持MVVM，但这个功能没必要都引出来，也许后续有时间再把前端给补上。
其实一开始想的是把这个中间表映射用JSON字段去存储，但后来又觉得用中间表设计比较设计上比较透明，容易写文档，而且写行转列查询在前端的数据JSON也比较标准，只是一个集合数据，前端比较容易理解,所以就又改成了中间表的设计。