這資料是某機台在每月初固定回傳API上個月燈號紀錄~
主管想知道哪幾天機台出現了黑燈?
資料如下:
declare @Machine table(
MachineYear int
,MachineMonth int
,MachineData nvarchar(max)
)
insert into @Machine
values(2022,1,'[{"Date":"2022/1/1","Light":"blue"},{"Date":"2022/1/15","Light":"red"},{"Date":"2022/1/20","Light":"black"},{"Date":"2022/1/26","Light":"blue"}]')
,(2022,2,'[{"Date":"2022/2/3","Light":"blue"},{"Date":"2022/2/11","Light":"blue"},{"Date":"2022/2/16","Light":"black"},{"Date":"2022/2/18","Light":"red"},{"Date":"2022/2/22","Light":"black"}]')
經過SQL查詢後~得知這幾天為黑燈
以上我是在SQL Server 2017版用OpenJson查詢的~
各位試試看自己的方式如何~
我來貼我自己的方式~各位參考看看就好~或許更厲害的~
declare @MachineJson nvarchar(max)
select @MachineJson='[' + Stuff((
select ',' + Str
from (
select Replace(Replace(MachineData,'[',''),']','') Str
from @Machine
) k
for xml path('')
),1,1,'') + ']'
select *
from OpenJson(@MachineJson)
with (
[Date] date
,[Light] varchar(max)
)
where [Light] = 'black'
然後在SQL2016以上版本~資料轉Json~或Json轉回資料~的確滿方便的@@"
declare @Tmp table(
YY int
,MM int
,StrChar nvarchar(50)
,StrDate date
)
insert into @Tmp
values(2022,1,'A','2022/1/3')
,(2022,1,'B','2022/1/13')
,(2022,1,'C','2022/1/25')
,(2022,2,'D','2022/2/11')
declare @MachineJson nvarchar(max)
select @MachineJson=(
SELECT *
from @Tmp
FOR JSON PATH
)
select @MachineJson JSON
select *
from OpenJson(@MachineJson)
with (
YY int
,MM int
,StrChar nvarchar(50)
,StrDate date
)
create table it220208 (
js json
);
insert into it220208 values
('[{"Date":"2022/1/1","Light":"blue"},{"Date":"2022/1/15","Light":"red"},{"Date":"2022/1/20","Light":"black"},{"Date":"2022/1/26","Light":"blue"}]'),
('[{"Date":"2022/2/3","Light":"blue"},{"Date":"2022/2/11","Light":"blue"},{"Date":"2022/2/16","Light":"black"},{"Date":"2022/2/18","Light":"red"},{"Date":"2022/2/22","Light":"black"}]');
with t1 as (
select json_array_elements(js) as injs
from it220208
)
select injs->>'Date' as black_dt
from t1
where injs->>'Light' = 'black';
black_dt
-----------
2022/1/20
2022/2/16
2022/2/22
(3 rows)
線上展示: https://dbfiddle.uk/?rdbms=postgres_14&fiddle=43322290d98f019feeb5576a05c22211