英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论