22,210
社区成员
发帖
与我相关
我的任务
分享
USE tempdb
GO
--1. 要增加一个分割表值函数
IF OBJECT_ID('[dbo].[Fun_String2ToStringArray]') IS NOT NULL
DROP FUNCTION [dbo].[Fun_String2ToStringArray]
GO
CREATE FUNCTION [dbo].[Fun_String2ToStringArray](@str NVARCHAR(MAX), @split NVARCHAR(10))
RETURNS @table TABLE ([item] NVARCHAR(max))
AS
BEGIN
IF LEN(@split) = 0
BEGIN
SET @split = N','
END
DECLARE @xml XML;
SET @xml = CONVERT(XML, '<x><![CDATA[' + replace(CONVERT(VARCHAR(MAX), @str), @split, ']]></x><x><![CDATA[') + ']]></x>')
INSERT INTO @table
SELECT item
FROM (SELECT c.value('text()[1]', 'nvarchar(4000)') [item]
FROM @xml.nodes('/x') t(c)) t
WHERE item IS NOT NULL
RETURN
END
GO
--测试表及测试数据
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t(
result VARCHAR(20),
d DATETIME
)
GO
INSERT INTO t VALUES('23@34','2018-04-01 08:00')
INSERT INTO t VALUES('3','2018-04-01 09:00')
INSERT INTO t VALUES('3','2018-04-02 09:00')
INSERT INTO t VALUES('3@4.6@5.7@3.2@4','2018-04-03 08:00')
INSERT INTO t VALUES('30','2018-04-04 09:00')
INSERT INTO t VALUES('34','2018-04-05 09:00')
INSERT INTO t VALUES('6@4.6@4.7@3.2@3','2018-04-06 09:00')
INSERT INTO t VALUES('8','2018-04-07 09:00')
INSERT INTO t VALUES('8','2018-04-08 09:00')
--实际的查询语句
;WITH cte AS (
SELECT f.item AS result,t.d FROM t
CROSS APPLY dbo.[Fun_String2ToStringArray](t.result,'@') AS f
)
,cte2 AS(
SELECT TOP 125 * FROM cte ORDER BY d
),cte3 AS(
SELECT TOP 125 * FROM cte ORDER BY d DESC
)
SELECT * FROM cte2
UNION
SELECT * FROM cte3