sql 交叉表实战案例

表数据如下：

要求拿sql 展现如下效果

1、准备数据，创建表

```sql
DROP TABLE IF EXISTS `dd`;
CREATE TABLE `dd`  (
  `ddh` int NULL DEFAULT NULL,
  `bh` int NULL DEFAULT NULL,
  `je` int NULL DEFAULT NULL,
  `ywlx` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of dd
-- ----------------------------
INSERT INTO `dd` VALUES (80080006, 449881000, 6988, 'A系列');
INSERT INTO `dd` VALUES (80097914, 481239000, 2400, 'A系列');
INSERT INTO `dd` VALUES (80097991, 278690000, 4800, 'A系列');
INSERT INTO `dd` VALUES (80098183, 449031000, 480, 'A系列');
INSERT INTO `dd` VALUES (80133962, 263595000, 1800, 'A系列');
INSERT INTO `dd` VALUES (80134925, 347056000, 2888, 'A系列');
INSERT INTO `dd` VALUES (80134928, 215505000, 2888, 'A系列');
INSERT INTO `dd` VALUES (80135187, 221919000, 1200, 'A系列');
INSERT INTO `dd` VALUES (80135379, 143207000, 588, 'A系列');
INSERT INTO `dd` VALUES (80135568, 157634000, 2888, 'A系列');
INSERT INTO `dd` VALUES (80135645, 149741000, 1388, 'A系列');
INSERT INTO `dd` VALUES (80135650, 209992000, 1288, 'B系列');
INSERT INTO `dd` VALUES (80135659, 519156000, 1288, 'B系列');
INSERT INTO `dd` VALUES (80135676, 539960000, 2888, 'B系列');
INSERT INTO `dd` VALUES (80136510, 102442000, 1288, 'B系列');
INSERT INTO `dd` VALUES (80136607, 311132000, 1288, 'A系列');
INSERT INTO `dd` VALUES (80136628, 201531000, 1288, 'B系列');
INSERT INTO `dd` VALUES (80136638, 96925600, 1288, 'A系列');
INSERT INTO `dd` VALUES (80137083, 286966000, 1288, 'A系列');
INSERT INTO `dd` VALUES (80137087, 422342000, 2888, 'A系列');
INSERT INTO `dd` VALUES (80137777, 297070000, 2888, 'A系列');
INSERT INTO `dd` VALUES (80138054, 60489800, 6, 'C系列');
INSERT INTO `dd` VALUES (80138274, 131975000, 1288, 'C系列');
INSERT INTO `dd` VALUES (80138460, 206112000, 2888, 'C系列');
INSERT INTO `dd` VALUES (80138804, 99823300, 1288, 'C系列');
INSERT INTO `dd` VALUES (80139137, 211984000, 1288, 'C系列');
INSERT INTO `dd` VALUES (80139524, 507229000, 4498, 'C系列');

2、写SQL

WITH CTE AS (
    SELECT 
        CASE 
            WHEN je BETWEEN 5 AND 5000 THEN '5-5000'
            WHEN je BETWEEN 5000 AND 10000 THEN '5000-10000'
            WHEN je BETWEEN 10000 AND 20000 THEN '10000-20000'
            WHEN je BETWEEN 20000 AND 30000 THEN '20000-30000'
            WHEN je BETWEEN 30000 AND 50000 THEN '30000-50000'
            ELSE '50000以上'
        END AS je分布,
        COUNT(CASE WHEN ywlx='A系列' THEN 1 END) AS A系列订单数量,
        COUNT(DISTINCT CASE WHEN ywlx='A系列' THEN bh END) AS A系列用户数量,
        SUM(CASE WHEN ywlx='A系列' THEN je END) AS A系列金额,
        COUNT(CASE WHEN ywlx='B系列' THEN 1 END) AS B系列订单数量,
        COUNT(DISTINCT CASE WHEN ywlx='B系列' THEN bh END) AS B系列用户数量,
        SUM(CASE WHEN ywlx='B系列' THEN je END) AS B系列金额,
				  COUNT(CASE WHEN ywlx='C系列' THEN 1 END) AS C系列订单数量,
				 COUNT(DISTINCT CASE WHEN ywlx='C系列' THEN bh END) AS C系列用户数量,
        SUM(CASE WHEN ywlx='C系列' THEN je END) AS C系列金额
    FROM dd
    GROUP BY 
        je分布
)
SELECT 
    je分布,
    A系列订单数量,
    A系列用户数量,
    A系列金额,
    B系列订单数量,
    B系列用户数量,
    B系列金额,
		 C系列订单数量,
    C系列用户数量,
    C系列金额
FROM CTE
ORDER BY 
    je分布

显示效果：
![在这里插入图片描述](https://img-blog.csdnimg.cn/bee9c3d7f6b34ec99169e59358d0a8fe.png)