SQL Server 编辑JSON列内容、同行不同列之间的赋值、多表联合查询

查询

select p.ID,p.Name,JSON_VALUE(d.Prop,'$."Position"') as Position
FROM DeTable as d INNER JOIN ProTable as p 
ON d.ID = p.ID 
where p.Type=1 order by d.id desc

修改的时候注意数据类型

修改int型数据：'Position'

  update d set Prop= JSON_MODIFY(Prop,'Position',p.Name) 
  from ProTable as p, DeTable as d 
  where Type=1 and d.ID=26 and p.ID=26

修改varchar型数据：,'$."Position"'

  update d set d.Prop=JSON_MODIFY(d.Prop,'$."Position"',p.Name) 
  from ProTable as p, DeTable as d 
  where Type=1 and d.ID=p.ID

多表联合查询，不依赖固定的视图

select top(10) t.ID,t.Type,JSON_VALUE(d.Prop,'$."Position"') as Position 
FROM HistoryTable AS t 
INNER JOIN DeTable as d 
ON t.ID = d.ID
INNER JOIN ProTable as p 
ON t.ID = p.ID 
WHERE t.DID<>0
order by t.id desc 

其它的

select (CAST(d.ProID as varchar(20))+RIGHT(POWER(CAST(10 AS BIGINT), 3)+d.CtrID, 3) + RIGHT(POWER(CAST(10 AS BIGINT), 3)+d.DevID, 3)) FROM DTable as d INNER JOIN PTable as p ON d.ProID = p.ProID WHERE p.County like '%xx%'  order by d.id desc