业务表和数据字典表关联(可以为null)
我有一个业务表和数据字典表,表结构和数据如下:
业务表里的字段关联数据字典项但是可以为null,怎么写一个sql能将两个表关联起来并显示所有数据字典的中文内容?(为null的字段显示为'')
最好不要太复杂,因为实际的业务表里面有7,8个字段关联着数据字典表且都可以为null

create table tmp_dic (
dicid number primary key,
KINDVARCHAR2(20),
NAMEVARCHAR2(20),
CODEVARCHAR2(20),
DETAILVARCHAR2(20)
);

CREATE TABLE TMP_STOCK_ORDER_HEADER(
STOCK_ORDER_HEADER_ID NUMBER primary key,
STOCK_ORDER_NUMBER VARCHAR2(200),
STOCK_ORDER_TYPE VARCHAR2(50),
ORDER_STATUS VARCHAR2(50),
URGENCY_LEVEL VARCHAR2(200)
)
;

insert into TMP_DIC (DICID, KIND, NAME, CODE, DETAIL) values ('1', 'ORDER_STATUS', '单据状态', 'DRAFT', '草稿');
insert into TMP_DIC (DICID, KIND, NAME, CODE, DETAIL) values ('2', 'ORDER_STATUS', '单据状态', 'FAILED', '更新库存失败');
insert into TMP_DIC (DICID, KIND, NAME, CODE, DETAIL) values ('3', 'ORDER_STATUS', '单据状态', 'INPROCESS', '更新库存中');
insert into TMP_DIC (DICID, KIND, NAME, CODE, DETAIL) values ('4', 'ORDER_STATUS', '单据状态', 'PARTIAL_SUCCESS', '更新部分库存成功');
insert into TMP_DIC (DICID, KIND, NAME, CODE, DETAIL) values ('5', 'ORDER_STATUS', '单据状态', 'SUCCESS', '更新库存成功');
insert into TMP_DIC (DICID, KIND, NAME, CODE, DETAIL) values ('6', 'ORDER_STATUS', '单据状态', 'WAITING', '已下单待入库');
insert into TMP_DIC (DICID, KIND, NAME, CODE, DETAIL) values ('13', 'STOCK_ORDER_TYPE', '出入库类型', 'DISCARD', '报废');
insert into TMP_DIC (DICID, KIND, NAME, CODE, DETAIL) values ('14', 'STOCK_ORDER_TYPE', '出入库类型', 'REPAIR', '返修');
insert into TMP_DIC (DICID, KIND, NAME, CODE, DETAIL) values ('15', 'STOCK_ORDER_TYPE', '出入库类型', 'RETURN', '退库');
insert into TMP_DIC (DICID, KIND, NAME, CODE, DETAIL) values ('16', 'STOCK_ORDER_TYPE', '出入库类型', 'STOCKIN', '入库');
insert into TMP_DIC (DICID, KIND, NAME, CODE, DETAIL) values ('17', 'STOCK_ORDER_TYPE', '出入库类型', 'STOCKOUT', '出库');
insert into TMP_DIC (DICID, KIND, NAME, CODE, DETAIL) values ('18', 'URGENCY_LEVEL', '紧急程度', '1', '平急');
insert into TMP_DIC (DICID, KIND, NAME, CODE, DETAIL) values ('19', 'URGENCY_LEVEL', '紧急程度', '2', '加急');
insert into TMP_DIC (DICID, KIND, NAME, CODE, DETAIL) values ('20', 'URGENCY_LEVEL', '紧急程度', '3', '特急');
insert into TMP_DIC (DICID, KIND, NAME, CODE, DETAIL) values ('21', 'URGENCY_LEVEL', '紧急程度', '4', '特提');

insert into TMP_STOCK_ORDER_HEADER (STOCK_ORDER_HEADER_ID, STOCK_ORDER_NUMBER, STOCK_ORDER_TYPE, ORDER_STATUS, URGENCY_LEVEL) values ('1', 'N123', 'STOCKIN', 'DRAFT', '1');
insert into TMP_STOCK_ORDER_HEADER (STOCK_ORDER_HEADER_ID, STOCK_ORDER_NUMBER, STOCK_ORDER_TYPE, ORDER_STATUS, URGENCY_LEVEL) values ('2', 'N234', 'STOCKOUT', null, null);
insert into TMP_STOCK_ORDER_HEADER (STOCK_ORDER_HEADER_ID, STOCK_ORDER_NUMBER, STOCK_ORDER_TYPE, ORDER_STATUS, URGENCY_LEVEL) values ('3', 'N345', null, null, null);

两个表关联类似这种样子,但因为业务表里允许有null值,所以这个sql是错的查不出数据.
select
STOCK_ORDER_HEADER_ID,
STOCK_ORDER_NUMBER,
d_.DETAIL as STOCK_ORDER_TYPE,
d_.DETAIL as ORDER_STATUS,
d_.DETAIL as URGENCY_LEVEL
from
TMP_STOCK_ORDER_HEADER,
tmp_dic d_
where 0=0
AND d_.KIND = 'STOCK_ORDER_TYPE' AND d_.CODE = STOCK_ORDER_TYPE
AND d_.KIND = 'ORDER_STATUS' AND d_.CODE = ORDER_STATUS
AND d_.KIND = 'URGENCY_LEVEL' AND d_.CODE = URGENCY_LEVEL
;


[解决办法]
SELECT Stock_Order_Header_Id,
Stock_Order_Number,
d_.Detail AS Stock_Order_Type,
d_.Detail AS Order_Status,
d_.Detail AS Urgency_Level
FROM Tmp_Stock_Order_Header a, Tmp_Dic d_
WHERE 0 = 0
AND d_.Kind = 'STOCK_ORDER_TYPE'
AND d_.Code = a.Stock_Order_Type

UNION ALL
SELECT Stock_Order_Header_Id,
Stock_Order_Number,
d_.Detail AS Stock_Order_Type,
d_.Detail AS Order_Status,
d_.Detail AS Urgency_Level

FROM Tmp_Stock_Order_Header a, Tmp_Dic d_
WHERE 0 = 0

AND d_.Kind = 'ORDER_STATUS'
AND d_.Code = a.Order_Status

UNION ALL
SELECT Stock_Order_Header_Id,
Stock_Order_Number,
d_.Detail AS Stock_Order_Type,
d_.Detail AS Order_Status,
d_.Detail AS Urgency_Level
FROM Tmp_Stock_Order_Header a, Tmp_Dic d_
WHERE 0 = 0

AND d_.Kind = 'URGENCY_LEVEL'
AND d_.Code = a.Urgency_Level

[解决办法]
有很多中方式的，我给一种
select
STOCK_ORDER_HEADER_ID,
STOCK_ORDER_NUMBER,
(SELECT DETAIL FROM TMP_DIC WHERE KIND='STOCK_ORDER_TYPE' AND CODE=S.STOCK_ORDER_TYPE) AS STOCK_ORDER_TYPE,
(SELECT DETAIL FROM TMP_DIC WHERE KIND='ORDER_STATUS' AND CODE=S.ORDER_STATUS) AS ORDER_STATUS,
(SELECT DETAIL FROM TMP_DIC WHERE KIND='URGENCY_LEVEL' AND CODE=S.URGENCY_LEVEL) AS URGENCY_LEVEL
from
TMP_STOCK_ORDER_HEADER S;
一点建议：楼主的数据表结构设计有点乱，数据存储冗余。