求sql语句解答

[jwfengc]

如何将这个表转换成这个表
，结果按照id，时间，还有到访地排序。
急求大哥帮助

[道素]

如果是MS SQL，下面可以参考下

;WITH tb(ID,t,loc) AS
(
   SELECT 323,'a',21 UNION ALL
   SELECT 324,'b',30 UNION ALL
   SELECT 323,'c',21 UNION ALL
   SELECT 324,'d',30 UNION ALL
   SELECT 323,'e',20 UNION ALL
   SELECT 324,'f',34 UNION ALL
   SELECT 323,'g',20 UNION ALL
   SELECT 324,'h',34 UNION ALL
   SELECT 323,'i',21 UNION ALL
   SELECT 323,'j',21 UNION ALL
   SELECT 324,'k',30 UNION ALL
   SELECT 324,'l',30 
),a AS(
    SELECT *,RANK()OVER(PARTITION BY id,loc ORDER BY t) AS rn FROM tb
)
SELECT a1.id,a1.loc,a1.t+' - '+a2.t FROM a AS a1
LEFT JOIN a AS a2 ON a1.id=a2.id AND a1.loc=a2.loc AND a2.rn=a1.rn+1
WHERE a1.rn%2=1
ORDER BY id,a1.t
/*
id          loc         
----------- ----------- -----
323         21          a - c
323         20          e - g
323         21          i - j
324         30          b - d
324         34          f - h
324         30          k - l
*/

[shoppo0505]

建议，表结构直接按照第二个表格设计，开始和结束时间分为2列。 你这种方法，以后还会遇到很多问题的。

[jwfengc]

没看懂呀，运行出错

[qq_31358327]

没看懂 感觉都没什么条件 来判断哪个对应哪个了

[学海无涯-回头是岸]

引用 2 楼 ch21st 的回复:

如果是MS SQL，下面可以参考下

;WITH tb(ID,t,loc) AS
(
   SELECT 323,'a',21 UNION ALL
   SELECT 324,'b',30 UNION ALL
   SELECT 323,'c',21 UNION ALL
   SELECT 324,'d',30 UNION ALL
   SELECT 323,'e',20 UNION ALL
   SELECT 324,'f',34 UNION ALL
   SELECT 323,'g',20 UNION ALL
   SELECT 324,'h',34 UNION ALL
   SELECT 323,'i',21 UNION ALL
   SELECT 323,'j',21 UNION ALL
   SELECT 324,'k',30 UNION ALL
   SELECT 324,'l',30 
),a AS(
    SELECT *,RANK()OVER(PARTITION BY id,loc ORDER BY t) AS rn FROM tb
)
SELECT a1.id,a1.loc,a1.t+' - '+a2.t FROM a AS a1
LEFT JOIN a AS a2 ON a1.id=a2.id AND a1.loc=a2.loc AND a2.rn=a1.rn+1
WHERE a1.rn%2=1
ORDER BY id,a1.t
/*
id          loc         
----------- ----------- -----
323         21          a - c
323         20          e - g
323         21          i - j
324         30          b - d
324         34          f - h
324         30          k - l
*/

感觉 不对啊， 不应该是 a2.rn=a1.rn+1 他要的是 id和loc 都相等的，感觉应该用xml path 可以解决

[gw6328]

DECLARE @t TABLE(id INT,T VARCHAR(20),code int)
INSERT INTO @t(id,t,code) VALUES
(323,'20161003185623',21),
(324,'20161003185958',30),
(323,'20161003193445',21),
(324,'20161004120908',30),
(323,'20161004160808',20)

SELECT * FROM @t;

;WITH cte AS (
SELECT id,SUBSTRING(t,1,4)+'-'+SUBSTRING(t,5,2)+'-'+SUBSTRING(t,7,2)+' '+SUBSTRING(t,9,2)+':'+SUBSTRING(t,11,2)+':'+SUBSTRING(t,13,2) AS t,code FROM @t
)
SELECT id,code,MIN(t) st,MAX(t) et FROM cte GROUP BY id,code ORDER BY st

[Klera]

要是有2000条数据也这么写????..............................................................