I was humiliated by a row of sql interview questions, so sad
There is a scenario where there are three products, i.e. 1, 2, 3, and three warehouses, i.e. 01, 02, 03. The three warehouses store three products respectively. The quantity distribution is as follows:

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 test data

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);

I wrote this:

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

In fact, when I think about it carefully, there is a problem. With the current test data, the query results are as follows. Only the data of warehouse 01 and warehouse 02 does not show warehouse 03. In fact, warehouse 03 has no product 3.
Why is there a problem? Looking at the test data carefully, we can see that there are 1 and 2 products in warehouse 01, 1 and 2 products in warehouse 02, and only 3 products in warehouse 03. The sql I wrote is still connected to the left, and the result of matching to the left will only show the inventory distribution of 1 and 2 products in warehouses 01, 02 and 03, and there will be no 3 products.

Execute this sqlinsert into product store count (PID, Sid, pnum) values ('3 ',' 01 ', 16);, let warehouse 01 have 1, 2 and 3 products, and then execute sql will be the correct result.

In fact, the biggest problem is that sometimes the execution may be right, sometimes the result may be wrong, and there is data missing. Never write like this in production. The best way to write is as follows:

Row to column:

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;

How to write column to row conversion

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 

Smart people can see it at a glance. In fact, they use some skills to realize the results to be queried step by step. It's very simple