admin 管理员组文章数量: 888338
mysql的行转列
被一道行转列的sql面试题羞辱了，好伤心.......
大概有这么一个场景，有三个产品，分别是1，2，3，和三个仓库，分别是01，02，03，三个仓库分别储存三个产品，数量分布如下图：
产品(PID)
仓库(SID)
数量(PNUM)
1
01
10
1
02
8
2
02
11
2
03
5
3
03
5
想得到的结果如下图：
产品(PID)
仓库一 (S1ID)
仓库二(S2ID)
仓库三(S3ID)
1
10
8
0
2
0
11
5
3
0
0
5
请写出sql?
这是一个典型的行转列的问题，
创建表
CREATE TABLE `product_store_count` (
`PID` varchar(11) DEFAULT NULL,
`SID` varchar(11) DEFAULT NULL,
`PNUM` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
插入测试数据
INSERT INTO product_store_count (PID,SID,PNUM) VALUES('1','01',10);
INSERT INTO product_store_count (PID,SID,PNUM) VALUES('1','02',20);
INSERT INTO product_store_count (PID,SID,PNUM) VALUES('1','03',10);
INSERT INTO product_store_count (PID,SID,PNUM) VALUES('2','01',25);
INSERT INTO product_store_count (PID,SID,PNUM) VALUES('2','02',16);
INSERT INTO product_store_count (PID,SID,PNUM) VALUES('3','03',12);
我是这样写的，：
SELECT M.PID,
CASE WHEN M.sum is null then 0 ELSE M.sum END S1ID,
CASE WHEN N.sum is null then 0 ELSE N.sum END S2ID,
CASE WHEN P.sum is null then 0 ELSE P.sum END S3ID
FROM (SELECT PID,SID, SUM(PNUM) sum FROM product_store_count WHERE SID='01' GROUP BY PID,SID) M
LEFT JOIN (SELECT PID,SID, SUM(PNUM) sum FROM product_store_count WHERE SID='02' GROUP BY PID,SID) N
ON M.PID=N.PID
LEFT JOIN (SELECT PID,SID, SUM(PNUM) sum FROM product_store_count WHERE SID='03' GROUP BY PID,SID) P
ON M.PID=P.PID
其实仔细想想我这样写其实是有问题的，以目前的测试数据，查询结果如下，只有仓库01、02的数据，并没有统计出03仓库，事实上03仓库是存有3号产品的。
为什么会有问题呢？仔细看一下测试数据可以发现，01仓库有1、2两种产品，02仓库有1、2两种产品，03仓库只有3，我写的sql还是左连接，向左匹配的结果，就会只有1、2两种产品在01、02、03仓库的库存分布情况，不会有3号产品的。
执行一下这条sqlINSERT INTO product_store_count (PID,SID,PNUM) VALUES('3','01',16);，让01仓库有1、2、3三种产品，再执行sql就会是正确的结果。
其实这么写最大问题就是有时候有可能执行是对的，有时候执行可能结果就是不对，漏掉有数据。生产中千万不能这么写，最好的写法应该是下面的这种写法：
行转列：
SELECT PID,
CASE SID WHEN '01' THEN PNUM ELSE 0 END S1ID,
CASE SID WHEN '02' THEN PNUM ELSE 0 END S2ID,
CASE SID WHEN '03' THEN PNUM ELSE 0 END S3ID
FROM product_store_count;
SELECT PID,
MAX(CASE SID WHEN '01' THEN PNUM ELSE 0 END ) S1ID ,
MAX(CASE SID WHEN '02' THEN PNUM ELSE 0 END ) S2ID,
MAX(CASE SID WHEN '03' THEN PNUM ELSE 0 END ) S3ID
FROM product_store_count GROUP BY PID;
那么列转行怎么写呢，看下面：
CREATE TABLE `product_store_count_2` (
`PID` varchar(11) DEFAULT NULL,
`S1ID` varchar(11) DEFAULT NULL,
`S2ID` varchar(11) DEFAULT NULL,
`S3ID` varchar(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `product_store_count_2` (`PID`, `S1ID`, `S2ID`, `S3ID`) VALUES ('1', '10', '20', '10');
INSERT INTO `product_store_count_2` (`PID`, `S1ID`, `S2ID`, `S3ID`) VALUES ('2', '25', '16', '0');
INSERT INTO `product_store_count_2` (`PID`, `S1ID`, `S2ID`, `S3ID`) VALUES ('3', '0', '0', '12');
SELECT PID, '01' SID,S1ID PNUM FROM product_store_count_2 WHERE S1ID>0
UNION
SELECT PID, '02' SID,S2ID PNUM FROM product_store_count_2 WHERE S2ID>0
UNION
SELECT PID, '03' SID,S3ID PNUM FROM product_store_count_2 WHERE S3ID>0
ORDER BY PID,SID ASC
聪明人一看就明白了，其实这都是运用一些技巧分步实现了要查询的结果，很简单
本文标签： mysql的行转列
版权声明：本文标题：mysql的行转列 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://www.freenas.com.cn/jishu/1693585815h230757.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。
发表评论