2021.5.28
问题1,A表具有id(非主键),name,code三个字段,一个id可能对应多个name(曾用名),现需要找出A表中id和所有对应的name
SELECT DISTINCT T1.ID,T1.NAME,T1.CODE FROM A T1 INNER JOIN A T2 ON T1.ID = T2.ID
问题2,B表具有code(与A的code字段关联对应),desc两个字段,现需要将A,B联查,找出所有A.*和对应的B.desc数据,A.code可以为null,A.CODE为null时desc字段用‘无’替换
sql1:SELECT DISTINCT T1.* ,(CASE WHEN T1.CODE IS NULL THEN '无' ELSE B.DESC END)
FROM B , A T1 INNER JOIN A T2 ON T1.ID = T2.ID
WHERE B.CODE = T1.CODE OR T1.CODE IS NULL
这种方法,若A和B数据量很大时,效率很低,所以使用子查询提高效率
sql2:WITH T1 AS (
SELECT DISTINCT T1.* FROM A T1 INNER JOIN A T2 ON T1.ID = T2.ID
),
T2 AS (
SELECT * FROM B
)
SELECT DISTINCT T1.*,(CASE WHEN T1.CODE IS NULL THEN '无' ELSE T2.DESC END) DESC
FROM T1 LEFT JOIN T2 ON T1.CODE = T2.CODE