SELECT
B.DATANAME,
C.LANGUAGENAME,
A.TITLE_NAME
A.SIZE
FROM
SHUBUDOC_TBL AS A
LEFT JOIN
DATA_TBL AS B
ON A.DATA_CD=B.DATA_CD
LEFT JOIN
LANGUAGE_TBL AS C
ON A.LANGUAGE_CD=C.LANGUAGE_CD
B.DATANAME,
C.LANGUAGENAME,
A.TITLE_NAME
A.SIZE
FROM
SHUBUDOC_TBL AS A
LEFT JOIN
DATA_TBL AS B
ON A.DATA_CD=B.DATA_CD
LEFT JOIN
LANGUAGE_TBL AS C
ON A.LANGUAGE_CD=C.LANGUAGE_CD
-- Author: liangCK 小梁
---------------------------------
--> 生成测试数据: @DATA_TBL
DECLARE @DATA_TBL TABLE (DATA_CD VARCHAR(3),DATA_NAME VARCHAR(2))
INSERT INTO @DATA_TBL
SELECT '001','aa' UNION ALL
SELECT '002','bb' UNION ALL
SELECT '003','cc'
--> 生成测试数据: @LANGUAGE_TBL
DECLARE @LANGUAGE_TBL TABLE (LANGUAGE_CD VARCHAR(2),LANGUAGE_NAME VARCHAR(2))
INSERT INTO @LANGUAGE_TBL
SELECT '01','dd' UNION ALL
SELECT '02','ee'
--> 生成测试数据: @SYOMEIBU_TBL
DECLARE @SYOMEIBU_TBL TABLE (BI_ID VARCHAR(4),TITLE_NAME VARCHAR(2))
INSERT INTO @SYOMEIBU_TBL
SELECT '0001','ee' UNION ALL
SELECT '0002','ef' UNION ALL
SELECT '0003','eg' UNION ALL
SELECT '0004','ef'
--> 生成测试数据: @SHUBUDOC_TBL
DECLARE @SHUBUDOC_TBL TABLE (CD INT,BI_ID VARCHAR(4),DATA_CD VARCHAR(3),LANGUAGE_CD VARCHAR(2),TITLE_NAME VARCHAR(2),SIZE INT)
INSERT INTO @SHUBUDOC_TBL
SELECT 1,'0001','001','01','ee',3 UNION ALL
SELECT 2,'0002','001','02','ef',3 UNION ALL
SELECT 3,'0003','002','01','eg',4 UNION ALL
SELECT 4,'0004','003','02','eg',4--SQL查询如下:SELECT
B.DATA_NAME,
C.LANGUAGE_NAME,
A.TITLE_NAME,
A.SIZE
FROM
@SHUBUDOC_TBL AS A
LEFT JOIN
@DATA_TBL AS B
ON A.DATA_CD=B.DATA_CD
LEFT JOIN
@LANGUAGE_TBL AS C
ON A.LANGUAGE_CD=C.LANGUAGE_CD
/*
DATA_NAME LANGUAGE_NAME TITLE_NAME SIZE
--------- ------------- ---------- -----------
aa dd ee 3
aa ee ef 3
bb dd eg 4
cc ee eg 4(4 行受影响)*/
b.DATA_NAME DATANAME,
c.LANGUAGE_NAME LANGUAGENAME,
d.TITLE_NAME TITLE_NAME ,
a.SIZE
from SHUBUDOC_TBL a
left join DATA_TBL b on a.DATA_CD=b.DATA_CD
left join LANGUAGE_TBL c on a.LANGUAGE_CD=c.LANGUAGE_CD
left join SYOMEIBU_TBL d on a.BI_ID=d.BI_ID
a.DATA_NAME,
b.LANGUAGE_NAME,
c.ITLE_NAME,
c.SIZE
FROM DATA_TBL a
LEFT JOIN SHUBUDOC_TBL c ON a.DATA_CD=c.DATA_CD
LEFT JOIN LANGUAGE_TBL b ON c.LANGUAGE_CD=b.LANGUAGE_CD