时间戳

首页 > 解决方案 > 与 SQL Server 中的 CTE 合并

sql - 与 SQL Server 中的 CTE 合并

问题描述

我使用了这个merge语句，但它没有更新我的目标表。它没有抛出任何错误，而是返回 cte, 的结果greater。我希望它能够更新需要更新的行并插入需要插入的行并返回受影响的行数。

with greater (provider) as (
  select 
    provider 
  from 
    (
      select 
        h.provider, 
        npi 
      from 
        hha h 
        join Quality q on h.provider = q.provider 
      group by 
        h.provider, 
        npi
    ) a 
  group by 
    provider 
  having 
    count(*) > 1
), 
zip_npi (providernbr, zip, npi) as (
  select 
    providernbr, 
    zip, 
    npi 
  from 
    npi 
  group by 
    providernbr, 
    zip, 
    npi
), 
zip_quality (provider, zip, rating) as (
  select 
    provider, 
    zip, 
    rating 
  from 
    Quality
) 
select 
  a.provider, 
  c.npi, 
  'HHA', 
  max(rating) as rating 
from 
  greater a 
  join zip_quality q on a.provider = q.provider 
  join Zip_npi c on q.provider = c.providernbr 
  and q.zip = c.zip 
where 
  rating <> '' 
group by 
  a.provider, 
  c.npi merge provider as [Target] using greater as source on (
    [Target].npi = [Source].npi 
    and [Target].provider = [Source].provider
  ) WHEN MATCHED THEN 
UPDATE 
SET 
  [Target].rating = [Source].rating WHEN NOT MATCHED THEN INSERT (provider, npi, type, rating) 
VALUES 
  (
    [Source].provider, [Source].npi, 
    'HHA', [Source].rating
  );

标签： sqlsql-server

解决方案

您的最后一个 SELECT（在 zip_quality 之后）也应该是 CTE 的一部分，所以是这样的：

... ), 
zip_quality (provider, zip, rating) as (
  select 
    provider, 
    zip, 
    rating 
  from 
    Quality
), something_else (provider ,npi, some_text ,rating ) as (
select 
  a.provider, 
  c.npi, 
  'HHA', 
  max(rating) as rating 
from 
  greater a 
  join zip_quality q on a.provider = q.provider 
  join Zip_npi c on q.provider = c.providernbr 
  and q.zip = c.zip 
where 
  rating <> '' 
group by 
  a.provider, 
  c.npi 
)

merge into provider as [Target] 
using something_else as source 
on (
    [Target].npi = [Source].npi 
    and [Target].provider = [Source].provider
  ) WHEN MATCHED THEN 

UPDATE 
SET 
  [Target].rating = [Source].rating WHEN NOT MATCHED THEN INSERT (provider, npi, type, rating) 
VALUES 
  (
    [Source].provider, [Source].npi, 
    'HHA', [Source].rating
  );

推荐阅读