mysql - SQL 查询从带有输入参数的单个字段中查找多个值

Question

这个问题已经有答案了:

Turning a Comma Separated string into individual rows (18 个回答)

已关闭 7 年前。

是否可以从单个字段中查找一组输入参数内的多个值？

数据库示例将是这样的

User             Login_DT_TM
0001             1/1/2015 12:00, 1/1/2015 12:30,1/6/2015 09:00
0002             1/3/2015 13:00, 1/5/2015 16:00

我需要一些东西来给我这个输出

0001           1/1/2015 12:00
0001           1/1/2015 12:30
0002           1/3/2015 13:00

其中login_DT_TM => 1/1/2015 '00:00' 并且login_DT_TM =< 1/4/2015 '23:59'

Answer 1

SQL Fiddle

MS SQL Server 2008 架构设置:

CREATE TABLE Test_Table ([User] VARCHAR(10) , Login_DT_TM VARCHAR(4000))
INSERT INTO Test_Table VALUES
('0001','1/1/2015 12:00, 1/1/2015 12:30,1/6/2015 09:00'),
('0002','1/3/2015 13:00, 1/5/2015 16:00')

查询 1:

WITH X AS
(
 SELECT [User] 
       ,CAST(Split.a.value('.', 'VARCHAR(100)')  AS DATETIME) Login_DT_TM
 FROM   
     (SELECT    [User]
              ,Cast ('<X>' + Replace(Login_DT_TM, ',', '</X><X>') + '</X>' AS XML) AS Data
       FROM    Test_Table
     ) AS t 
     CROSS APPLY Data.nodes ('/X') AS Split(a) 
)
SELECT  [User]
       ,Login_DT_TM
FROM X
WHERE Login_DT_TM > '20141231'
 AND  Login_DT_TM < '20150105'

<强> Results :

| User |        Login_DT_TM        |
|------|---------------------------|
| 0001 | 2015-01-01 12:00:00.000   |
| 0001 | 2015-01-01 12:30:00.000   |
| 0002 | 2015-01-03 13:00:00.000   |