新实现的一个四表行转列视图查询

新实现的一个四表行转列视图查询

 

先上个建库建表插入数据:

/*

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也比较标准，只是一个集合数据，前端比较容易理解,所以就又改成了中间表的设计。