一张表Info
性别 班级 类型
01 02 01
02 01 02
01 01 02
02 02 02--------------------------------------------------------------
另一张表 code
KindCode code value
sex 01 男
sex 02 女
class 01 1班
calss 02 2班
type 01 A
type 02 B
----------------------------------------
怎么把这张表查询出 一下结果性别 班级 类型
男 2班 A
女 1班 B
男 1班 B
女 2班 B
性别 班级 类型
01 02 01
02 01 02
01 01 02
02 02 02--------------------------------------------------------------
另一张表 code
KindCode code value
sex 01 男
sex 02 女
class 01 1班
calss 02 2班
type 01 A
type 02 B
----------------------------------------
怎么把这张表查询出 一下结果性别 班级 类型
男 2班 A
女 1班 B
男 1班 B
女 2班 B
第二张code表设计的不太合理。
1.要么分三个表,分别存不同的code(性别,班级,类型);2.要么还用一张表,但至少在code表中,加个标识位,标识该条code到底属于哪个code, 是性别,还是班级,还是类型。应该是这样设计的。
不然,你的SQL,实现起来,会有问题的。
WITH a AS
(
SELECT '01' sex, '02' CLASS, '01' TYPE FROM dual
UNION ALL
SELECT '02', '01', '02' FROM dual
UNION ALL
SELECT '01', '01', '02' FROM dual
UNION ALL
SELECT '02', '02', '02' FROM dual
)
, B AS
(
SELECT 'sex' a,'01' b, '男' c FROM dual
UNION ALL
SELECT 'sex','02', '女' FROM dual
UNION ALL
SELECT 'class','01', '1班' FROM dual
UNION ALL
SELECT 'class','02', '2班' FROM dual
UNION ALL
SELECT 'type','01', 'A' FROM dual
UNION ALL
SELECT 'type','02', 'B' FROM dual
)
SELECT a1.c,a2.c,a3.c
FROM a a0,
(SELECT a,b,c FROM b WHERE b.a='sex') a1,
(SELECT a,b,c FROM b WHERE b.a='class')a2,
(SELECT a,b,c FROM b WHERE b.a='type')a3
WHERE a0.sex =a1.b(+) AND a0.CLASS=a2.b(+) AND a0.type=a3.b(+)
--result:男 1班 B
男 2班 A
女 1班 B
女 2班 B
不好意思,看错了。
2楼的SQL好用的。
别纠结了。
2 SELECT '01' sex,'02' class,'01' type FROM DUAL UNION ALL
3 SELECT '02' sex,'01' class,'02' type FROM DUAL UNION ALL
4 SELECT '01' sex,'01' class,'02' type FROM DUAL UNION ALL
5 SELECT '02' sex,'02' class,'02' type FROM DUAL
6 ),code AS (
7 SELECT 'sex' kindcode,'01' code,'男' value FROM DUAL UNION ALL
8 SELECT 'sex' kindcode,'02' code,'女' value FROM DUAL UNION ALL
9 SELECT 'class' kindcode,'01' code,'1班' value FROM DUAL UNION ALL
10 SELECT 'class' kindcode,'02' code,'2班' value FROM DUAL UNION ALL
11 SELECT 'type' kindcode,'01' code,'A' value FROM DUAL UNION ALL
12 SELECT 'type' kindcode,'02' code,'B' value FROM DUAL
13 ),t AS (
14 SELECT sex,class,type,ROWNUM rn FROM info
15 )
16 SELECT MAX(DECODE(n.kindcode,'sex',n.value)) sex,
17 MAX(DECODE(n.kindcode,'class',n.value)) class,
18 MAX(DECODE(n.kindcode,'type',n.value)) type
19 FROM (
20 SELECT m.rn,m.kindcode,c.value FROM (
21 SELECT rn, 'sex' kindcode,sex code FROM t UNION ALL
22 SELECT rn, 'class' kindcode,class code FROM t UNION ALL
23 SELECT rn, 'type' kindcode,type code FROM t
24 ) m
25 LEFT JOIN code c ON m.kindcode = c.kindcode AND m.code = c.code
26 ) n
27 GROUP BY n.rn
28 ORDER BY n.rn
29 ;SEX CLASS TYPE
---- ----- ----
男 2班 A
女 1班 B
男 1班 B
女 2班 B
select (select val from code_tb where kindcode='sex' and code_id = sex) as 性别,
(select val from code_tb where kindcode='class' and code_id = class_id) as 班级,
(select val from code_tb where kindcode='type' and code_id = type_id) as 类型
from (select * from info);e..就是介样