mysql的行转列_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

聪明人一看就明白了，其实这都是运用一些技巧分步实现了要查询的结果，很简单