56,687
社区成员
发帖
与我相关
我的任务
分享求SQL语句，我想用 case when else来写，可惜只能写一半。。。。。。
【很抱歉，可用分不满100，只能给50了。。。。。。】
CREATE TABLE IF NOT EXISTS MyTest1 (
ORDERNO INT,
TypeName VARCHAR(20) NOT NULL,
CARTON_A INT,
CARTON_B INT,
CARTON_C INT,
BULK_A INT,
BULK_B INT,
BULK_C INT,
WHOLE_A INT,
WHOLE_B INT
);
INSERT INTO MyTest1 (ORDERNO,TypeName,CARTON_A,CARTON_B,CARTON_C,BULK_A,BULK_B,BULK_C,WHOLE_A,WHOLE_B) VALUES ('1','女装','1','2','3','0','0','0','0','0');
INSERT INTO MyTest1 (ORDERNO,TypeName,CARTON_A,CARTON_B,CARTON_C,BULK_A,BULK_B,BULK_C,WHOLE_A,WHOLE_B) VALUES ('2','女装','0','2','3','0','0','0','0','0');
INSERT INTO MyTest1 (ORDERNO,TypeName,CARTON_A,CARTON_B,CARTON_C,BULK_A,BULK_B,BULK_C,WHOLE_A,WHOLE_B) VALUES ('3','男装','0','0','0','7','4','3','0','0');
INSERT INTO MyTest1 (ORDERNO,TypeName,CARTON_A,CARTON_B,CARTON_C,BULK_A,BULK_B,BULK_C,WHOLE_A,WHOLE_B) VALUES ('4','男装','0','0','0','0','0','0','10','15');
INSERT INTO MyTest1 (ORDERNO,TypeName,CARTON_A,CARTON_B,CARTON_C,BULK_A,BULK_B,BULK_C,WHOLE_A,WHOLE_B) VALUES ('5','男装','0','1','0','0','0','0','0','0');
INSERT INTO MyTest1 (ORDERNO,TypeName,CARTON_A,CARTON_B,CARTON_C,BULK_A,BULK_B,BULK_C,WHOLE_A,WHOLE_B) VALUES ('6','童装','5','1','0','0','0','0','0','0');
INSERT INTO MyTest1 (ORDERNO,TypeName,CARTON_A,CARTON_B,CARTON_C,BULK_A,BULK_B,BULK_C,WHOLE_A,WHOLE_B) VALUES ('7','童装','0','0','0','20','23','3','0','0');
INSERT INTO MyTest1 (ORDERNO,TypeName,CARTON_A,CARTON_B,CARTON_C,BULK_A,BULK_B,BULK_C,WHOLE_A,WHOLE_B) VALUES ('8','童装','0','0','0','0','0','0','14','52');
我自己写的语句只能得到这样的结果：
我的语句是：
SELECT ORDERNO,TypeName,CONCAT('',(CASE WHEN TypeName = '女装' THEN CONCAT('',(CASE WHEN CARTON_A>0 THEN CAST(CARTON_A AS CHAR) ELSE 0 END ), ' *C_A' ) ELSE 0 END ),', ',(CASE WHEN TypeName = '女装' THEN CONCAT('',(CASE WHEN CARTON_B>0 THEN CAST(CARTON_B AS CHAR) ELSE 0 END ), ' *C_B' ) ELSE 0 END ),', ',(CASE WHEN TypeName = '女装' THEN CONCAT('',(CASE WHEN CARTON_C>0 THEN CAST(CARTON_C AS CHAR) ELSE 0 END ), ' *C_C' ) ELSE 0 END )) AS 数量种类 FROM MyTest1
我真正想得到的结果是上图中男装、童装也显示出相应的数据：
3、男装 7 *BULK_A, 4 * BULK_B, 3 *BULK_C
4、男装 10 *WHOLE_A, 15 *WHOLE_B
5、男装 1 * CARTON_B,
6、童装 5 *CARTON_A, 1 *CARTON_B
7、童装 20 * BULK_A, 23 *BULK_B, 3 *BULK_C
8、童装 14 *WHOLE_A, 52 *WHOLE_B
如果数量为0的能不显示出来，那就更好了
CREATE TABLE IF NOT EXISTS MyTest1 (
ORDERNO INT,
TypeName VARCHAR(20) NOT NULL,
CARTON_A INT,
CARTON_B INT,
CARTON_C INT,
BULK_A INT,
BULK_B INT,
BULK_C INT,
WHOLE_A INT,
WHOLE_B INT
);
INSERT INTO MyTest1 (ORDERNO,TypeName,CARTON_A,CARTON_B,CARTON_C,BULK_A,BULK_B,BULK_C,WHOLE_A,WHOLE_B) VALUES ('1','女装','1','2','3','0','0','0','0','0');
INSERT INTO MyTest1 (ORDERNO,TypeName,CARTON_A,CARTON_B,CARTON_C,BULK_A,BULK_B,BULK_C,WHOLE_A,WHOLE_B) VALUES ('2','女装','0','2','3','0','0','0','0','0');
INSERT INTO MyTest1 (ORDERNO,TypeName,CARTON_A,CARTON_B,CARTON_C,BULK_A,BULK_B,BULK_C,WHOLE_A,WHOLE_B) VALUES ('3','男装','0','0','0','7','4','3','0','0');
INSERT INTO MyTest1 (ORDERNO,TypeName,CARTON_A,CARTON_B,CARTON_C,BULK_A,BULK_B,BULK_C,WHOLE_A,WHOLE_B) VALUES ('4','男装','0','0','0','0','0','0','10','15');
INSERT INTO MyTest1 (ORDERNO,TypeName,CARTON_A,CARTON_B,CARTON_C,BULK_A,BULK_B,BULK_C,WHOLE_A,WHOLE_B) VALUES ('5','男装','0','1','0','0','0','0','0','0');
INSERT INTO MyTest1 (ORDERNO,TypeName,CARTON_A,CARTON_B,CARTON_C,BULK_A,BULK_B,BULK_C,WHOLE_A,WHOLE_B) VALUES ('6','童装','5','1','0','0','0','0','0','0');
INSERT INTO MyTest1 (ORDERNO,TypeName,CARTON_A,CARTON_B,CARTON_C,BULK_A,BULK_B,BULK_C,WHOLE_A,WHOLE_B) VALUES ('7','童装','0','0','0','20','23','3','0','0');
INSERT INTO MyTest1 (ORDERNO,TypeName,CARTON_A,CARTON_B,CARTON_C,BULK_A,BULK_B,BULK_C,WHOLE_A,WHOLE_B) VALUES ('8','童装','0','0','0','0','0','0','14','52');
我自己写的语句只能得到这样的结果：
我的语句是：
SELECT ORDERNO,TypeName,CONCAT('',(CASE WHEN TypeName = '女装' THEN CONCAT('',(CASE WHEN CARTON_A>0 THEN CAST(CARTON_A AS CHAR) ELSE 0 END ), ' *C_A' ) ELSE 0 END ),', ',(CASE WHEN TypeName = '女装' THEN CONCAT('',(CASE WHEN CARTON_B>0 THEN CAST(CARTON_B AS CHAR) ELSE 0 END ), ' *C_B' ) ELSE 0 END ),', ',(CASE WHEN TypeName = '女装' THEN CONCAT('',(CASE WHEN CARTON_C>0 THEN CAST(CARTON_C AS CHAR) ELSE 0 END ), ' *C_C' ) ELSE 0 END )) AS 数量种类 FROM MyTest1
我真正想得到的结果是上图中男装、童装也显示出相应的数据：
3、男装 7 *BULK_A, 4 * BULK_B, 3 *BULK_C
4、男装 10 *WHOLE_A, 15 *WHOLE_B
5、男装 1 * CARTON_B,
6、童装 5 *CARTON_A, 1 *CARTON_B
7、童装 20 * BULK_A, 23 *BULK_B, 3 *BULK_C
8、童装 14 *WHOLE_A, 52 *WHOLE_B
如果数量为0的能不显示出来，那就更好了
...全文
请发表友善的回复…
发表回复
ETJojo 2018-09-03
- 打赏
- 举报
谢谢！
有个小问题，如果发生 ‘女装’ 只有 CARTON_A 有数据5，其他都没数据的情况， 查询结果将会是 “5 *C_A, ”，多了个 “，”，有没有办法去掉呢？
ETJojo 2018-09-03
- 打赏
- 举报
谢谢！
ITVin 2018-09-02
- 打赏
- 举报
SELECT * from (SELECT
ORDERNO,
TypeName,
CONCAT(
'',
CASE
WHEN TypeName = '女装' AND CARTON_A > 0 THEN
(
CASE
WHEN TypeName = '女装' THEN
CONCAT(
'',
(
CASE
WHEN CARTON_A > 0 THEN
CAST(CARTON_A AS CHAR)
ELSE
0
END
),
' *C_A, '
)
ELSE
0
END
) ELSE '' END,
CASE
WHEN TypeName = '女装' AND CARTON_B > 0 THEN
(
CASE
WHEN TypeName = '女装' THEN
CONCAT(
'',
(
CASE
WHEN CARTON_B > 0 THEN
CAST(CARTON_B AS CHAR)
ELSE
0
END
),
' *C_B, '
)
ELSE
0
END
)ELSE '' END,
CASE
WHEN TypeName = '女装' AND CARTON_C > 0 THEN
(
CASE
WHEN TypeName = '女装' THEN
CONCAT(
'',
(
CASE
WHEN CARTON_C > 0 THEN
CAST(CARTON_C AS CHAR)
ELSE
0
END
),
' *C_C'
)
ELSE
0
END
) ELSE '' END
) AS numType
FROM
MyTest1) T
WHERE numType<>''
ACMAIN_CHM 2018-09-02
- 打赏
- 举报
mysql> select * from MyTest1;
+---------+----------+----------+----------+----------+--------+--------+--------+---------+---------+
| ORDERNO | TypeName | CARTON_A | CARTON_B | CARTON_C | BULK_A | BULK_B | BULK_C | WHOLE_A | WHOLE_B |
+---------+----------+----------+----------+----------+--------+--------+--------+---------+---------+
| 1 | F | 1 | 2 | 3 | 0 | 0 | 0 | 0 | 0 |
| 2 | F | 0 | 2 | 3 | 0 | 0 | 0 | 0 | 0 |
| 3 | M | 0 | 0 | 0 | 7 | 4 | 3 | 0 | 0 |
| 4 | M | 0 | 0 | 0 | 0 | 0 | 0 | 10 | 15 |
| 5 | M | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 |
| 6 | C | 5 | 1 | 0 | 0 | 0 | 0 | 0 | 0 |
| 7 | C | 0 | 0 | 0 | 20 | 23 | 3 | 0 | 0 |
| 8 | C | 0 | 0 | 0 | 0 | 0 | 0 | 14 | 52 |
+---------+----------+----------+----------+----------+--------+--------+--------+---------+---------+
8 rows in set (0.00 sec)
mysql> select ORDERNO,TypeName,
-> CONCAT_WS(',',
-> IF(CARTON_A>0,concat(CARTON_A,'*CARTON_A'),null),
-> IF(CARTON_B>0,concat(CARTON_B,'*CARTON_B'),null),
-> IF(CARTON_C>0,concat(CARTON_C,'*CARTON_C'),null),
-> IF(BULK_A>0,concat(BULK_A,'*BULK_A'),null),
-> IF(BULK_B>0,concat(BULK_B,'*BULK_B'),null),
-> IF(BULK_C>0,concat(BULK_C,'*BULK_C'),null),
-> IF(WHOLE_A>0,concat(WHOLE_A,'*WHOLE_A'),null),
-> IF(WHOLE_B>0,concat(WHOLE_B,'*WHOLE_B'),null)
-> ) as k
-> from MyTest1;
+---------+----------+----------------------------------+
| ORDERNO | TypeName | k |
+---------+----------+----------------------------------+
| 1 | F | 1*CARTON_A,2*CARTON_B,3*CARTON_C |
| 2 | F | 2*CARTON_B,3*CARTON_C |
| 3 | M | 7*BULK_A,4*BULK_B,3*BULK_C |
| 4 | M | 10*WHOLE_A,15*WHOLE_B |
| 5 | M | 1*CARTON_B |
| 6 | C | 5*CARTON_A,1*CARTON_B |
| 7 | C | 20*BULK_A,23*BULK_B,3*BULK_C |
| 8 | C | 14*WHOLE_A,52*WHOLE_B |
+---------+----------+----------------------------------+
8 rows in set (0.00 sec)
mysql>
学海无涯-回头是岸 2018-09-02
- 打赏
- 举报
直接 外面 套一层 select *() where 数量种类!=0,0,0 。这个会降低一些查询速度
ETJojo 2018-09-01
- 打赏
- 举报
具体怎么套呢？请教了。另外，再套一层查询，对查询速度有没有影响呢？
ETJojo 2018-09-01
- 打赏
- 举报
具体怎么套呢？请教了
学海无涯-回头是岸 2018-09-01
- 打赏
- 举报
想要不显示出来 再套一层查询做判断 也不是不可以
