不知道你具体要怎么合并,现在是一个简单,将时间合并到一个字段 WITH t AS( SELECT 'a'c1,'b'c2,'c'c3,'20120618'txtm FROM dual UNION ALL SELECT 'a'c1,'b'c2,'c'c3,'20120620'txtm FROM dual ) SELECT c1,c2,c3,wm_concat(txtm) FROM t GROUP BY c1,c2,c3;
还可以参考这个,缺点是要固定的行列 WITH t AS( SELECT 'a'c1,'b'c2,'c'c3,'20120618'txtm FROM dual UNION ALL SELECT 'a'c1,'b'c2,'c'c3,'20120620'txtm FROM dual ) SELECT c1,c2,c3,Max(Decode(rn,1,txtm))rn1,Max(Decode(rn,2,txtm))rn2 FROM ( SELECT c1,c2,c3,txtm,Row_Number() over (PARTITION BY c1,c2,c3 ORDER BY txtm)rn FROM t ) GROUP BY c1,c2,c3;
select l.linkid, n.nodename, m.nodename,wm_concat(to_char(r.timetag,'hh24:mi:ss')),r.status from roadstatushistory r, links l, nodes n, nodes m where l.enternodeid = n.nodeid and l.exitnodeid = m.nodeid and l.linkid=r.linkid and r.STATUS<= 10 and r.TIMETAG >=to_date('2012-06-18 00:00:00','yyyy-mm-dd hh24:mi:ss') and r.TIMETAG <=to_date('2012-06-19 00:00:00','yyyy-mm-dd hh24:mi:ss') 怎么在这个SQL语句里面写呢
WITH t AS(
SELECT 'a'c1,'b'c2,'c'c3,'20120618'txtm FROM dual
UNION ALL
SELECT 'a'c1,'b'c2,'c'c3,'20120620'txtm FROM dual
)
SELECT c1,c2,c3,wm_concat(txtm) FROM t GROUP BY c1,c2,c3;
WITH t AS(
SELECT 'a'c1,'b'c2,'c'c3,'20120618'txtm FROM dual
UNION ALL
SELECT 'a'c1,'b'c2,'c'c3,'20120620'txtm FROM dual
)
SELECT c1,c2,c3,Max(Decode(rn,1,txtm))rn1,Max(Decode(rn,2,txtm))rn2 FROM
(
SELECT c1,c2,c3,txtm,Row_Number() over (PARTITION BY c1,c2,c3 ORDER BY txtm)rn FROM t
)
GROUP BY c1,c2,c3;
select l.linkid, n.nodename, m.nodename,wm_concat(to_char(r.timetag,'hh24:mi:ss')),r.status from roadstatushistory r, links l, nodes n, nodes m where l.enternodeid = n.nodeid and l.exitnodeid = m.nodeid and l.linkid=r.linkid and r.STATUS<= 10 and r.TIMETAG >=to_date('2012-06-18 00:00:00','yyyy-mm-dd hh24:mi:ss') and r.TIMETAG <=to_date('2012-06-19 00:00:00','yyyy-mm-dd hh24:mi:ss') 怎么在这个SQL语句里面写呢