两个查询帮助 [英] Two Queries Help

问题描述

您好，

1。我有一个包含XML格式数据的列。在此处显示示例：

1. I have a column that has data in XML format. Showing an example here:

<

ExtendedInfo>

  <

PageCount> 1775< / PageCount>

  <

碎片> 5.52113< /碎片>

< /

ExtendedInfo>

<
ExtendedInfo>
  <
PageCount>1775</PageCount>
  <
Fragmentation>5.52113</Fragmentation>
</
ExtendedInfo>

我想根据asc或desc选择所述xml数据列的内容。

I would like to select the contents of the said xml data column based on either asc or desc.

2。有两列是StartDate和EndDate，格式如下：

2. There are two columns which are StartDate and EndDate with the following format:

2019-03-10 06：54：54.403      2019-03-10 06：54：57.683

2019-03-10 06:54:54.403      2019-03-10 06:54:57.683

我希望查询以HH：MM：SS的形式返回两列之间的时差，除了用asc排序它们或者desc。

I would like the query to return the time difference between the two columns in the form of HH:MM:SS besides sorting them by either asc or desc.

非常感谢您的早期回复。

Will appreciate your early response.

谢谢。

Victor

Victor

推荐答案

1。 XML：

1. XML:

DECLARE @xml xml;
SET @xml = '
<ExtendedInfo>
	<PageCount>1775</PageCount>
	<Fragmentation>5.52113</Fragmentation>
</ExtendedInfo>';

SELECT 
	b.value('(./PageCount/text())[1]', 'varchar(10)') AS PageCount,
	b.value('(./Fragmentation/text())[1]', 'varchar(10)') AS Fragmentation
FROM @xml.nodes('/ExtendedInfo') AS a(b);

2。日期差异格式

DECLARE @d1 datetime = '2019-03-10 06:54:54.403'
DECLARE @d2 datetime = '2019-03-10 06:54:57.683'

SELECT LEFT('0' + CAST(DATEDIFF(ss, @d1, @d2) / 3600 AS varchar(2)), 2) + ':' + 
       LEFT('0' + CAST((DATEDIFF(ss, @d1, @d2) % 3600) / 60 AS varchar(2)), 2) + ':' + 
       LEFT('0' + CAST((DATEDIFF(ss, @d1, @d2) % 3600) % 60 AS varchar(2)), 2) AS [HH:MM:SS];

这篇关于两个查询帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！