临时表妙用、连表更新、sqlserver group contant

一、临时表妙用

-- 1、将老库中的mediaid和新库中的regionid对应上，然后插入到临时表中
SELECT * INTO #TempMediaRegion FROM (SELECT ww.C_MEDIA,C_ID FROM [NEWSYS].[LMSData].[dbo].Base_Region
right JOIN openquery(CRM, 'select * from T_LOCREGION') ww
ON C_CrmID=ww.C_REGION) hh

-- 2、将媒体按媒体分组
SELECT * INTO #TempLocalMediaRegion FROM (SELECT ww.C_MEDIA,
stuff((select ','+cast(C_ID as NVARCHAR(50)) FROM #TempMediaRegion where C_MEDIA=ww.C_MEDIA for xml path('')),1,1,'') b
FROM #TempMediaRegion ww GROUP BY C_MEDIA) sf

--3、 更新媒体表（地区）
UPDATE [NEWSYS].[LMSData].[dbo].Base_Media SET C_RegionID=#TempLocalMediaRegion.b FROM [NEWSYS].[LMSData].[dbo].Base_Media
LEFT JOIN #TempLocalMediaRegion ON C_CrmID=#TempLocalMediaRegion.C_MEDIA

 

二、连表更新

-- 更新
UPDATE M SET M.C_ParentId = MM.c_id FROM dbo.Base_Media M LEFT JOIN dbo.Base_Media MM ON M.c_crmparentid = mm.C_CrmID

 

三、sqlserver 相当于 mysql contact的用法

SELECT * INTO #TempLocalMediaRegion FROM (SELECT ww.C_MEDIA,
stuff((select ','+cast(C_ID as NVARCHAR(50)) FROM #TempMediaRegion where C_MEDIA=ww.C_MEDIA for xml path('')),1,1,'') b
FROM #TempMediaRegion ww GROUP BY C_MEDIA) sf