你效率问题不是因为没用case when,而是没用with块with cte as ( SELECT ROW_NUMBER() OVER ( ORDER BY jztime ) AS number , jztime FROM ( SELECT time1 jztime UNION SELECT time2 UNION SELECT time3 UNION SELECT time4 UNION SELECT time5 UNION SELECT time6 ) SELECT name AS 名字, ( SELECT jztimeandnumbertable1.jztime FROM cte jztimeandnumbertable1 WHERE jztimeandnumbertable1.number = 1 ) AS 第一时间 , ( SELECT jztimeandnumbertable2.jztime FROM cte jztimeandnumbertable2 WHERE jztimeandnumbertable2.number = 2 ) AS 第二时间 , ( SELECT jztimeandnumbertable3.jztime FROM cte jztimeandnumbertable3 WHERE jztimeandnumbertable3.number = 3 ) AS 第三时间 FROM testtable
with v as( select name,times,rn from (select name,times,row_number() over(partition by name order by times) 'rn' from (select name,time1 'times' from testtable union all select name,time2 'times' from testtable union all select name,time3 'times' from testtable union all select name,time4 'times' from testtable union all select name,time5 'times' from testtable union all select name,time6 'times' from testtable) t) u where u.rn<=3) select name '名字',[1] '第一时间',[2] '第二时间',[3] '第三时间' from v pivot(max(times) for rn in([1],[2],[3])) p/* 名字 第一时间 第二时间 第三时间 ---------- ----------------------- ----------------------- ----------------------- a1 2014-02-23 00:00:00.000 2014-02-23 01:00:00.000 2014-02-23 02:00:00.000 a2 2014-02-24 00:00:00.000 2014-02-24 01:00:00.000 2014-02-24 02:00:00.000 a3 2014-02-25 00:00:00.000 2014-02-25 01:00:00.000 2014-02-25 02:00:00.000 a4 2014-02-26 00:00:00.000 2014-02-26 01:00:00.000 2014-02-26 02:00:00.000(4 row(s) affected) */
啊啊啊!我都不记得 with块了。多谢提醒0.0
出什么错了额,貌似是with语句块里没有引用表的原因,不知道咋解决。
应该不需要扫描6次的,请看执行计划. 应该是union all那里吧
with cte as ( SELECT ROW_NUMBER() OVER ( ORDER BY jztime ) AS number , jztimetable.jztime FROM ( SELECT time1 jztime UNION SELECT time2 UNION SELECT time3 UNION SELECT time4 UNION SELECT time5 UNION SELECT time6 ) jztimetable WHERE ISDATE(jztimetable.jztime) = 1 ) SELECT name AS 名字, ( SELECT jztimeandnumbertable1.jztime FROM cte jztimeandnumbertable1 WHERE jztimeandnumbertable1.number = 1 ) AS 第一时间 , ( SELECT jztimeandnumbertable2.jztime FROM cte jztimeandnumbertable2 WHERE jztimeandnumbertable2.number = 2 ) AS 第二时间 , ( SELECT jztimeandnumbertable3.jztime FROM cte jztimeandnumbertable3 WHERE jztimeandnumbertable3.number = 3 ) AS 第三时间 FROM testtable 好像我复制的时候漏copy了
(
SELECT ROW_NUMBER() OVER ( ORDER BY jztime ) AS number ,
jztime
FROM ( SELECT time1 jztime
UNION
SELECT time2
UNION
SELECT time3
UNION
SELECT time4
UNION
SELECT time5
UNION
SELECT time6
)
SELECT name AS 名字,
( SELECT jztimeandnumbertable1.jztime
FROM cte jztimeandnumbertable1
WHERE jztimeandnumbertable1.number = 1
) AS 第一时间 ,
( SELECT jztimeandnumbertable2.jztime
FROM cte jztimeandnumbertable2
WHERE jztimeandnumbertable2.number = 2
) AS 第二时间 ,
( SELECT jztimeandnumbertable3.jztime
FROM cte jztimeandnumbertable3
WHERE jztimeandnumbertable3.number = 3
) AS 第三时间
FROM testtable
with v as(
select name,times,rn
from
(select name,times,row_number() over(partition by name order by times) 'rn'
from
(select name,time1 'times' from testtable
union all
select name,time2 'times' from testtable
union all
select name,time3 'times' from testtable
union all
select name,time4 'times' from testtable
union all
select name,time5 'times' from testtable
union all
select name,time6 'times' from testtable) t) u
where u.rn<=3)
select name '名字',[1] '第一时间',[2] '第二时间',[3] '第三时间'
from v
pivot(max(times) for rn in([1],[2],[3])) p/*
名字 第一时间 第二时间 第三时间
---------- ----------------------- ----------------------- -----------------------
a1 2014-02-23 00:00:00.000 2014-02-23 01:00:00.000 2014-02-23 02:00:00.000
a2 2014-02-24 00:00:00.000 2014-02-24 01:00:00.000 2014-02-24 02:00:00.000
a3 2014-02-25 00:00:00.000 2014-02-25 01:00:00.000 2014-02-25 02:00:00.000
a4 2014-02-26 00:00:00.000 2014-02-26 01:00:00.000 2014-02-26 02:00:00.000(4 row(s) affected)
*/
应该是union all那里吧
with cte as
(
SELECT ROW_NUMBER() OVER ( ORDER BY jztime ) AS number ,
jztimetable.jztime
FROM ( SELECT time1 jztime
UNION
SELECT time2
UNION
SELECT time3
UNION
SELECT time4
UNION
SELECT time5
UNION
SELECT time6
) jztimetable
WHERE ISDATE(jztimetable.jztime) = 1
)
SELECT name AS 名字,
( SELECT jztimeandnumbertable1.jztime
FROM cte jztimeandnumbertable1
WHERE jztimeandnumbertable1.number = 1
) AS 第一时间 ,
( SELECT jztimeandnumbertable2.jztime
FROM cte jztimeandnumbertable2
WHERE jztimeandnumbertable2.number = 2
) AS 第二时间 ,
( SELECT jztimeandnumbertable3.jztime
FROM cte jztimeandnumbertable3
WHERE jztimeandnumbertable3.number = 3
) AS 第三时间
FROM testtable
好像我复制的时候漏copy了
好像我复制的时候漏copy了你这个不行哦