SQl左外部联接以填充空 [英] SQl left outer join to fill null

问题描述

我已经使用左外部联接在SQL中联接了两个表(见下文).

I have joined two tables in SQL (see below) using a left outer join.

是否可以用其他数据填充空单元格?

Is it possible to fill the null cells with other data?

sqlfiddle

(来自小提琴)

CREATE TABLE Data1
    (cost_type nvarchar(10), contract nvarchar(10))
;

INSERT INTO Data1
    (cost_type, contract)
VALUES
    ('LABR', 'contract1'),
    ('EQP', ''),
    ('RST', 'contract1')
;

CREATE TABLE data2
    (cost_type nvarchar(10), contract nvarchar(10), Name nvarchar(max))
;

INSERT INTO data2
    (cost_type,contract,Name)
VALUES
    ('LABR','contract1','John'),
    ('MAT','contract1','John'),
    ('EQP','','')
;

当前正在运行的查询:

select * from data1 dt1 
left outer join data2 dt2 on dt1.cost_type=dt2.cost_type

我需要的结果如下:

COST_TYPE  CONTRACT  NAME  
LABR, contract1, John  
EQP, contract1, John  
RST, contract1, John

此结果基于找到CONTRACT列的最常用值并将所有null替换为该值的结果.我需要对名称"列做同样的事情.

This result is based on finding the most used value for the CONTRACT column and replacing all of the nulls with that value. I need to do the same thing for the NAME column.

推荐答案

当您在上面的注释中明确表示希望使用最常用合同/名称填充值时，查询就不会太复杂写;一个CTE来查找每个最常用的合同/名称，以及一个将CTE值替换为空值的查询；

When you made it clear in the comments above that you wanted the values filled with the most used contracts/names, the query wasn't too complex to write; a CTE to find each the most usual contract/name, and a query that replaces empty values with the CTE values;

WITH most_used_contract AS (
  SELECT TOP 1 contract FROM Data1
  WHERE contract IS NOT NULL
  GROUP BY contract
  ORDER BY COUNT(*) DESC
), most_used_name AS (
  SELECT TOP 1 name FROM Data2
  WHERE name IS NOT NULL
  GROUP BY name
  ORDER BY COUNT(*) DESC
)
SELECT data1.cost_type, 
       CASE WHEN data1.contract IS NULL OR data1.contract = ''
            THEN muc.contract
            ELSE data1.contract END contract, 
       CASE WHEN data2.name IS NULL OR data2.name = ''
            THEN mun.name
            ELSE data2.name END name
FROM data1  
LEFT JOIN data2 ON data1.cost_type=data2.cost_type
LEFT JOIN most_used_contract muc ON 1=1
LEFT JOIN most_used_name mun ON 1=1

要测试的SQLfiddle .

这篇关于SQl左外部联接以填充空的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！