关于笛卡尔积陷阱的实例以及表的行转列运算

下面的代码演示了笛卡尔积陷阱的相关实例。

 

/*当我们采用以下语句进行查询的时候，得到的结果远超过我们需要的量。*/

select a.userID,B.username,A.myvalue,A.mymonth from 

cartTableA A, cartTableB B

where A.userID = B.userID

 

/*甚至我们采用以下的语句的时候，也仍然得不到我们想要的结果。*/

select a.userID,B.username,A.myvalue,A.mymonth from 

cartTableA A, cartTableB B

where A.userID = B.userID and A.mymonth = B.mymonth

 

/*只有采用以下语句才能得到我们想要查看的正确结果*/

 

select distinct a.userID,B.username,A.myvalue,A.mymonth from 

cartTableA A, cartTableB B

where A.userID = B.userID and A.mymonth = B.mymonth

 

 

 

 

drop table if exists Jinhua_Electric.cartTableA ;

 

create table Jinhua_Electric.cartTableA(

userID char(10),

mymonthchar(10),

myvaluedecimal(8,2),

DT char(8)

);

 

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1001','201808',12.33,'20180901');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1001','201808',12.33,'20180902');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1001','201808',12.33,'20180903');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1001','201808',12.33,'20180904');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1001','201808',12.33,'20180905');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1001','201808',12.33,'20180906');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1001','201808',12.33,'20180907');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1001','201808',12.33,'20180908');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1001','201808',12.33,'20180909');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1001','201808',12.33,'20180910');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1001','201808',12.33,'20180911');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1001','201808',12.33,'20180912');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1001','201808',12.33,'20180913');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1001','201808',12.33,'20180914');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1001','201807',58.45,'20180901');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1001','201807',58.45,'20180902');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1001','201807',58.45,'20180903');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1001','201807',58.45,'20180904');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1001','201807',58.45,'20180905');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1001','201807',58.45,'20180906');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1001','201807',58.45,'20180907');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1001','201807',58.45,'20180908');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1001','201807',58.45,'20180909');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1001','201807',58.45,'20180910');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1001','201807',58.45,'20180911');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1001','201807',58.45,'20180912');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1001','201807',58.45,'20180913');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1001','201807',58.45,'20180914');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1001','201806',134.99,'20180901');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1001','201806',134.99,'20180902');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1001','201806',134.99,'20180903');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1001','201806',134.99,'20180904');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1001','201806',134.99,'20180905');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1001','201806',134.99,'20180906');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1001','201806',134.99,'20180907');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1001','201806',134.99,'20180908');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1001','201806',134.99,'20180909');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1001','201806',134.99,'20180910');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1001','201806',134.99,'20180911');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1001','201806',134.99,'20180912');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1001','201806',134.99,'20180913');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1001','201806',134.99,'20180914');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1002','201808',88.24,'20180901');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1002','201808',88.24,'20180902');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1002','201808',88.24,'20180903');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1002','201808',88.24,'20180904');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1002','201808',88.24,'20180905');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1002','201808',88.24,'20180906');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1002','201808',88.24,'20180907');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1002','201808',88.24,'20180908');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1002','201808',88.24,'20180909');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1002','201808',88.24,'20180910');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1002','201808',88.24,'20180911');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1002','201808',88.24,'20180912');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1002','201808',88.24,'20180913');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1002','201808',88.24,'20180914');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1002','201807',44.98,'20180901');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1002','201807',44.98,'20180902');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1002','201807',44.98,'20180903');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1002','201807',44.98,'20180904');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1002','201807',44.98,'20180905');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1002','201807',44.98,'20180906');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1002','201807',44.98,'20180907');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1002','201807',44.98,'20180908');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1002','201807',44.98,'20180909');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1002','201807',44.98,'20180910');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1002','201807',44.98,'20180911');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1002','201807',44.98,'20180912');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1002','201807',44.98,'20180913');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1002','201807',44.98,'20180914');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1002','201806',34.21,'20180901');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1002','201806',34.21,'20180902');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1002','201806',34.21,'20180903');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1002','201806',34.21,'20180904');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1002','201806',34.21,'20180905');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1002','201806',34.21,'20180906');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1002','201806',34.21,'20180907');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1002','201806',34.21,'20180908');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1002','201806',34.21,'20180909');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1002','201806',34.21,'20180910');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1002','201806',34.21,'20180911');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1002','201806',34.21,'20180912');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1002','201806',34.21,'20180913');

INSERT INTO Jinhua_Electric.cartTableA(userID, mymonth, myvalue,DT) values('S1002','201806',34.21,'20180914');

 

 

drop table if exists Jinhua_Electric.cartTableB;

create table Jinhua_Electric.cartTableB(

userID char(10),

username char(10),

mymonthchar(10)

);

 

 

 

INSERT INTO Jinhua_Electric.cartTableB(userID, username,mymonth) values('S1001','A1','201808');

INSERT INTO Jinhua_Electric.cartTableB(userID, username,mymonth) values('S1002','A2','201808');

INSERT INTO Jinhua_Electric.cartTableB(userID, username,mymonth) values('S1003','A3','201808');

INSERT INTO Jinhua_Electric.cartTableB(userID, username,mymonth) values('S1004','A4','201808');

INSERT INTO Jinhua_Electric.cartTableB(userID, username,mymonth) values('S1005','A5','201808');

INSERT INTO Jinhua_Electric.cartTableB(userID, username,mymonth) values('S1006','A6','201808');

INSERT INTO Jinhua_Electric.cartTableB(userID, username,mymonth) values('S1007','A7','201808');

INSERT INTO Jinhua_Electric.cartTableB(userID, username,mymonth) values('S1008','A8','201808');

INSERT INTO Jinhua_Electric.cartTableB(userID, username,mymonth) values('S1009','A9','201808');

INSERT INTO Jinhua_Electric.cartTableB(userID, username,mymonth) values('S1010','A10','201808');

INSERT INTO Jinhua_Electric.cartTableB(userID, username,mymonth) values('S1011','A11','201808');

INSERT INTO Jinhua_Electric.cartTableB(userID, username,mymonth) values('S1012','A12','201808');

INSERT INTO Jinhua_Electric.cartTableB(userID, username,mymonth) values('S1013','A13','201808');

INSERT INTO Jinhua_Electric.cartTableB(userID, username,mymonth) values('S1014','A14','201808');

INSERT INTO Jinhua_Electric.cartTableB(userID, username,mymonth) values('S1001','A1','201807');

INSERT INTO Jinhua_Electric.cartTableB(userID, username,mymonth) values('S1002','A2','201807');

INSERT INTO Jinhua_Electric.cartTableB(userID, username,mymonth) values('S1003','A3','201807');

INSERT INTO Jinhua_Electric.cartTableB(userID, username,mymonth) values('S1004','A4','201807');

INSERT INTO Jinhua_Electric.cartTableB(userID, username,mymonth) values('S1005','A5','201807');

INSERT INTO Jinhua_Electric.cartTableB(userID, username,mymonth) values('S1006','A6','201807');

INSERT INTO Jinhua_Electric.cartTableB(userID, username,mymonth) values('S1007','A7','201807');

INSERT INTO Jinhua_Electric.cartTableB(userID, username,mymonth) values('S1008','A8','201807');

INSERT INTO Jinhua_Electric.cartTableB(userID, username,mymonth) values('S1009','A9','201807');

INSERT INTO Jinhua_Electric.cartTableB(userID, username,mymonth) values('S1010','A10','201807');

INSERT INTO Jinhua_Electric.cartTableB(userID, username,mymonth) values('S1011','A11','201807');

INSERT INTO Jinhua_Electric.cartTableB(userID, username,mymonth) values('S1012','A12','201807');

INSERT INTO Jinhua_Electric.cartTableB(userID, username,mymonth) values('S1013','A13','201807');

INSERT INTO Jinhua_Electric.cartTableB(userID, username,mymonth) values('S1014','A14','201807');

INSERT INTO Jinhua_Electric.cartTableB(userID, username,mymonth) values('S1001','A1','201806');

INSERT INTO Jinhua_Electric.cartTableB(userID, username,mymonth) values('S1002','A2','201806');

INSERT INTO Jinhua_Electric.cartTableB(userID, username,mymonth) values('S1003','A3','201806');

INSERT INTO Jinhua_Electric.cartTableB(userID, username,mymonth) values('S1004','A4','201806');

INSERT INTO Jinhua_Electric.cartTableB(userID, username,mymonth) values('S1005','A5','201806');

INSERT INTO Jinhua_Electric.cartTableB(userID, username,mymonth) values('S1006','A6','201806');

INSERT INTO Jinhua_Electric.cartTableB(userID, username,mymonth) values('S1007','A7','201806');

INSERT INTO Jinhua_Electric.cartTableB(userID, username,mymonth) values('S1008','A8','201806');

INSERT INTO Jinhua_Electric.cartTableB(userID, username,mymonth) values('S1009','A9','201806');

INSERT INTO Jinhua_Electric.cartTableB(userID, username,mymonth) values('S1010','A10','201806');

INSERT INTO Jinhua_Electric.cartTableB(userID, username,mymonth) values('S1011','A11','201806');

INSERT INTO Jinhua_Electric.cartTableB(userID, username,mymonth) values('S1012','A12','201806');

INSERT INTO Jinhua_Electric.cartTableB(userID, username,mymonth) values('S1013','A13','201806');

INSERT INTO Jinhua_Electric.cartTableB(userID, username,mymonth) values('S1014','A14','201806');

 

 

表的行转列操作

采用max函数能实现

SELECT userid, max(case when mymonth=201806 then myvalue else 0 end) , max(case when mymonth=201807 then myvalue else 0 end) , max(case when mymonth=201808 then myvalue else 0 end)
from carttablea
group by userid

 

 

采用下面的函数可以做环比的计算

 

select S.userid, (S.month_second - S.month_first) as sec_mon_inc,
(S.month_third - S.month_first) as sec_mon_inc
from (SELECT userid, max(case when mymonth=201806 then myvalue else 0 end)as month_first ,
max(case when mymonth=201807 then myvalue else 0 end)as month_second,
max(case when mymonth=201808 then myvalue else 0 end)as month_third

from carttablea
group by userid) S