急救一段SQL的转换,本人不胜感激......

[busiliao]

有一段MSSQL下的SQL语句需要转换成ORACLE下能执行的语句

SELECT DISTINCT c.gnmkbh AS gnmkbh, c.cdxsmc AS cdxsmc, c.dywj AS dywj, c.cdzbh AS cdzbh,d.* FROM (SELECT * FROM (SELECT qxlb FROM admin_QX_ZW WHERE ','+'1'+',' LIKE '%,' + cast(zwbh AS varchar) + ',%') a INNER JOIN (SELECT gnmkbh, cdxsmc, dywj, cdzbh FROM admin_QX_KTGNMKLB where qxbz<>'X') b ON ',' + CAST(a.qxlb AS varchar(8000)) + ',' LIKE '%,' + b.GNMKBH + ',%') c INNER JOIN (SELECT name, nnumber, sybz FROM admin_QX_MENUMASTER WHERE sybz <>'X') d ON c.cdzbh = d.nnumber order by c.cdzbh

还请帮忙,本人不胜感激,另开贴给分.

[maotin]

这句语句是不是该精简一下

[beckhambobo]

SELECT DISTINCT c.gnmkbh AS gnmkbh, c.cdxsmc AS cdxsmc, c.dywj AS dywj, c.cdzbh AS cdzbh,d.* FROM
(SELECT * FROM
(SELECT qxlb FROM admin_QX_ZW WHERE ','||'1'||',' LIKE '%,' ||to_char(zwbh) || ',%') a,
(SELECT gnmkbh, cdxsmc, dywj, cdzbh FROM admin_QX_KTGNMKLB where qxbz<>'X') b where ','||to_char(a.qxlb)||',' LIKE '%,' ||b.GNMKBH ||',%') c ,
(SELECT name, nnumber, sybz FROM admin_QX_MENUMASTER WHERE sybz <>'X') d
where c.cdzbh = d.nnumber
order by c.cdzbh

[beckhambobo]

简写:
SELECT DISTINCT a.qxlb,b.gnmkbh, b.cdxsmc, b.dywj, b.cdzbh,c.name,c.nnumber,c.sybz
FROM admin_QX_ZW a,admin_QX_KTGNMKLB b,admin_QX_MENUMASTER c where to_char(a.zwbh) LIKE '%1%' and b.qxbz<>'X' and instr(a.qxlb,b.GNMKBH)>0 and c.sybz <>'X' and c.nnumber=b.cdzbh

[wawxj]

SELECT DISTINCT c.gnmkbh AS gnmkbh, c.cdxsmc AS cdxsmc, c.dywj AS dywj, c.cdzbh AS cdzbh,d.* FROM
(SELECT * FROM (SELECT qxlb FROM admin_QX_ZW WHERE ','||'1'||',' LIKE '%,'||to_char(zwbh)||',%') a,(SELECT gnmkbh, cdxsmc, dywj, cdzbh FROM admin_QX_KTGNMKLB where qxbz != 'X') b where ','||to_char(a.qxlb)||',' LIKE '%,' ||b.GNMKBH||',%') c ,(SELECT name, nnumber, sybz FROM admin_QX_MENUMASTER WHERE sybz != 'X') d where c.cdzbh = d.nnumber(+) order by c.cdzbh

[LGQDUCKY]

oracle连接字符串是用||符号，