能用SQL根据sgf棋谱输出最终局面

〇〇

思路差不多
with mv as(
select l,substr(s,2,1)e,substr(s,-2,1)x,substr(s,-1)y from(
select level l,regexp_substr(moves,';[WB]\[[a-z]{2}',1,level)s from goes connect by level<=200)),
m as(select mv.*,row_number()over( partition by x,y order by l desc)rn from mv ),
t as(select chr(96+level) l from dual connect by level<=19)
select yl,
case yl when 'a' then listagg(decode(e,'W','○','B','●',decode(xl,'a','┌','s','┐','┬'))) within group(order by xl)
when 's' then listagg(decode(e,'W','○','B','●',decode(xl,'a','└','s','┘','┴'))) within group(order by xl)
else listagg(decode(e,'W','○','B','●',decode(xl,'a','├','s','┤','┼'))) within group(order by xl)
end a from
(select x.l xl,y.l yl from
t x,t y)n,m where rn(+)=1 and m.x(+)=xl and m.y(+)=yl
group by yl order by 1;

〇〇

peter1166 发表于 2016-3-16 10:24
SELECT LISTAGG(BW,'')WITHIN GROUP(ORDER BY X)
FROM (
SELECT T2.XY, T1.XY ,T2.X, T2.Y

为什么要 CHR(14849212)呢

peter1166

'┼' 用这个的时候，总报错，ORA-00907: missing right parenthesis
后来改成  CHR(14849212) 就好了，奇怪了。

〇〇

缩短一点


with mv as(
select l,substr(s,2,1)e,substr(s,-2,1)x,substr(s,-1)y from(
select level l,regexp_substr(moves,';[WB]\[[a-z]{2}',1,level)s from goes connect by level<=REGEXP_COUNT(MOVES,';'))),
m as(select mv.*,row_number()over( partition by x,y order by l desc)rn from mv ),
t as(select chr(96+level) l from dual connect by level<=19)
select yl,
listagg(decode(e,'W','○','B','●',decode(yl,'a' ,decode(xl,'a','┌','s','┐','┬')
                                            ,'s' ,decode(xl,'a','└','s','┘','┴')
,decode(xl,'a','├','s','┤','┼')))) within group(order by xl)
a from
(select x.l xl,y.l yl from
t x,t y)n,m where rn(+)=1 and m.x(+)=xl and m.y(+)=yl
group by yl order by 1;

〇〇

peter1166 发表于 2016-3-16 11:05
'┼' 用这个的时候，总报错，ORA-00907: missing right parenthesis
后来改成  CHR(14849212) 就好了，奇怪 ...

我们的字符集不同
select ascii('┼') from dual;

ASCII('┼')
-----------
      43488

lastwinner

peter1166 发表于 2016-3-16 11:05
'┼' 用这个的时候，总报错，ORA-00907: missing right parenthesis
后来改成  CHR(14849212) 就好了，奇怪 ...

应该是字符集的问题

peter1166

也出来， 但是反了不行。


select CHR(43488) from dual;

CHR(43488)
----------
┼

SELECT ASCII('┼') FROM DUAL ;

  ASCII('┼')
------------
    14849212
   
SELECT VALUE FROM V$NLS_PARAMETERS
WHERE  PARAMETER = 'NLS_CHARACTERSET';

VALUE
----------------------------------------------------------------
AL32UTF8

〇〇

用instr快一些
col a for a40
with mv as(
select l,substr(s,2,1)e,substr(s,-2,1)x,substr(s,-1)y from(
select level l,regexp_substr(moves,';[WB]\[[a-z]{2}',regexp_instr(moves,';[WB]\[[a-z]{2}',1,level))s from goes connect by level<=REGEXP_COUNT(MOVES,';'))),
m as(select mv.*,row_number()over( partition by x,y order by l desc)rn from mv ),
t as(select chr(96+level) l from dual connect by level<=19)
select yl,
listagg(decode(e,'W','○','B','●',decode(yl,'a' ,decode(xl,'a','┌','s','┐','┬')
                                            ,'s' ,decode(xl,'a','└','s','┘','┴')
,decode(xl,'a','├','s','┤','┼')))) within group(order by xl)
a from
(select x.l xl,y.l yl from
t x,t y)n,m where rn(+)=1 and m.x(+)=xl and m.y(+)=yl
group by yl order by 1;

〇〇

peter1166 发表于 2016-3-16 14:30
也出来， 但是反了不行。

我的
ZHS16GBK

〇〇

with mv as(
select l,substr(s,2,1)e,substr(s,-2,1)x,substr(s,-1)y from(
select level l,substr(moves,regexp_instr(moves,';[WB]\[[a-z]{2}',1,level),5)s from goes connect by level<=REGEXP_COUNT(MOVES,';'))),
m as(select mv.*,row_number()over( partition by x,y order by l desc)rn from mv ),
t as(select chr(96+level) l from dual connect by level<=19)
select yl,
listagg(decode(e,'W','○','B','●',decode(yl,'a' ,decode(xl,'a','┌','s','┐','┬')
                                            ,'s' ,decode(xl,'a','└','s','┘','┴')
,decode(xl,'a','├','s','┤','┼')))) within group(order by xl)
a from
(select x.l xl,y.l yl from
t x,t y)n,m where rn(+)=1 and m.x(+)=xl and m.y(+)=yl
group by yl order by 1;