iT邦幫忙

0

玩玩SQL~查詢機台API回傳的資料哪幾天為黑燈(已貼SQL)

  • 分享至 

  • xImage

這資料是某機台在每月初固定回傳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"}]')

https://ithelp.ithome.com.tw/upload/images/20220208/20061369oKbAcTGZhh.png

經過SQL查詢後~得知這幾天為黑燈
https://ithelp.ithome.com.tw/upload/images/20220208/20061369VXLzn2RN19.png

以上我是在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
)

https://ithelp.ithome.com.tw/upload/images/20220208/20061369YU4jOf6YUK.png

圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

1 個回答

5
一級屠豬士
iT邦大師 1 級 ‧ 2022-02-08 17:14:24
最佳解答
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

不錯唷~PG的方式~
/images/emoticon/emoticon32.gif

我要發表回答

立即登入回答