搜索最新记录中的特定字符串。

huangapple go评论67阅读模式
英文:

Search for the specific string as per the latest record

问题

我有系统记录的以日志文本为基础的数据。

我正在尝试从LogText列中搜索最新在系统中执行最新交易的用户名,而且LogText包含OnHold: False -> True

因此,从**#TblName**中的输出应该是

Key1 DateStamped Username
PF1 2023-01-05 Quoc.Phan
英文:

I have the data with log-text as per the system recorded.

IF OBJECT_ID('tempdb..#TblName') IS NOT NULL
    BEGIN
        DROP TABLE #TblName
    END

CREATE TABLE #TblName (
    Key1 varchar(50)
    ,DateStamped date
	,LogText nvarchar(max)
)

INSERT INTO #TblName VALUES ('PF1','2021-09-01','Gabriela.Santa 18:26:25  OnHold: False -> True  OnHoldDate:  -> 9/1/2021  OnHoldReasonCode:  -> NEWPDCN')
INSERT INTO #TblName VALUES ('PF1','2022-12-26','NhatCuong.Nguyen 08:21:18  OnHold: True -> False  OnHoldDate: 12/23/2022 ->   OnHoldReasonCode: NoCnf ->   OnHoldComment_c: 3D for Chemical miliing change, need to update CM BDWG ->   NhatCuong.Nguyen 15:10:42  OnHold: False -> True  OnHoldDate:  -> 12/26/2022  OnHoldReasonCode:  -> NoCnf  OnHoldComment_c:  -> 3D for Chemical miliing change, need to update CM BDWG')
INSERT INTO #TblName VALUES ('PF1','2023-01-05','NhatCuong.Nguyen 13:37:42  OnHold: True -> False  OnHoldDate: 12/26/2022 ->   OnHoldReasonCode: NoCnf ->   OnHoldComment_c: 3D for Chemical miliing change, need to update CM BDWG ->   Quoc.Phan 14:36:24  OnHold: False -> True  OnHoldDate:  -> 1/5/2023  OnHoldReasonCode:  -> NoCnf  OnHoldComment_c:  -> 3D for Chemical miliing change, need to update CM BDWG  Nguyen.Anh 14:55:42  OnHold: True -> False  OnHoldDate: 1/5/2023 ->   OnHoldReasonCode: NoCnf ->   OnHoldComment_c: 3D for Chemical miliing change, need to update CM BDWG ->   Quoc.Phan 14:57:29  OnHold: False -> True  OnHoldDate:  -> 1/5/2023  OnHoldReasonCode:  -> NoCnf  OnHoldComment_c:  -> 3D for Chemical miliing change, need to update CM BDWG')
INSERT INTO #TblName VALUES ('PF1','2022-12-23','ThiThanh.Nguyen 10:12:22  AnalysisCode:  -> MachSO  Quoc.Phan 16:11:22  OnHold: False -> True  OnHoldDate:  -> 12/23/2022  OnHoldReasonCode:  -> NoCnf  OnHoldComment_c:  -> 3D for Chemical miliing change, need to update CM BDWG')
INSERT INTO #TblName VALUES ('PF1','2021-09-16','Valentin.Opris 12:08:52  OnHold: True -> False  OnHoldDate: 9/1/2021 ->   OnHoldReasonCode: NEWPDCN ->   Bogdan.Stefanescu 17:19:26  OnHold: False -> True  OnHoldDate:  -> 9/16/2021  OnHoldReasonCode:  -> NEWPDCN  Jothimani.Alagappan 17:29:53  ChangeRequestReason_c: CM_V0107 Spirit Sunshine - Move ST in house -> CM_V0330 SPIRIT SUNSHINE - Update Manufacturing Drawing PDCN review')

select * from #TblName

I am trying to search the latest user name from LogText column who have done the latest transaction in the system and the LogText contain OnHold: False -> True

So, the output from #TblName should be

Key1 DateStamped Username
PF1 2023-01-05 Quoc.Phan

答案1

得分: 1

这是非常糟糕的设计,未来将会出现性能问题。如果“长文本”来自日志文件或应用程序,您需要重新编写每个事件以正确地进行分割。例如,您可以使用以下方式:

  • CHAR(29),CHAR(30),CHAR(31) - 分组、记录和单元分隔符
  • XML
  • JSON

然后,当数据进入您的表格时,您可以使用触发器或其他例行程序来拆分数据并以规范的方式填充它。例如,您需要为用户、密钥、挂起代码和事件创建表格。在事件表格中,您将拥有:

  • 密钥 ID
  • 用户 ID
  • 是否挂起
  • 挂起日期
  • 挂起原因代码 ID

这样,您将能够更轻松地解决类似提出的任务。

英文:

This is very bad design and you are going to have performance issues in the future. If the long text comes from log file or application you need to rewrite in order each event to be spillable correctly. For example, you can use:

  • CHAR(29), CHAR(30), CHAR(31) - group, record and unit separator
  • XML
  • JSON

Then, when the data comes in your table you can use trigger or other routine to split the data and populate it in a normalized manner. For example, you need table for users, for keys,for on hold codes and for events. In the events table you will have:

  • key id
  • user id
  • is on hold
  • on hold date
  • on hold reason code id

and you will be able to solve task like the ask one easier.

答案2

得分: 0

select t1.key1, t1.datestamped, substring(t1.logtext, charindex('onhold: false -> true', t1.logtext) - 20, 20) as username
from #tblname t1
inner join (
select key1, max(datestamped) as max_date
from #tblname
where logtext like '%onhold: false -> true%'
group by key1
) t2 on t1.key1 = t2.key1 and t1.datestamped = t2.max_date

英文:

To find the latest user name in the LogText column that has done a transaction in the system you can use the following query:

select t1.key1, t1.datestamped, substring(t1.logtext, charindex('onhold: false -> true', t1.logtext) - 20, 20) as username
from #tblname t1
inner join (
  select key1, max(datestamped) as max_date
  from #tblname
  where logtext like '%onhold: false -> true%'
  group by key1
) t2 on t1.key1 = t2.key1 and t1.datestamped = t2.max_date

huangapple
  • 本文由 发表于 2023年1月6日 13:55:58
  • 转载请务必保留本文链接:https://go.coder-hub.com/75027434.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定