求教一个SQL 语句,该如何处理
求教一个SQL 语句
一个表 T1 的结构如下:
如何通过SQL 查询 得到如下结果
如果一个语句无法完成 那么几个语句可以完成
------解决方案--------------------
Select a.xuhao, ;
b1.size as size1, Nvl(b1.zh,0) as zh1, Nvl(b1.js,0) as js1, ;
b2.size as size2, Nvl(b2.zh,0) as zh2, Nvl(b2.js,0) as js2, ;
b3.size as size3, Nvl(b3.zh,0) as zh3, Nvl(b3.js,0) as js3 ;
from (select xuhao from 源表 group by xuhao) a ;
left join (select * from 源表 where size = 's1') b1 on a.xuhao = b1.xuhao ;
left join (select * from 源表 where size = 's2') b2 on a.xuhao = b2.xuhao ;
left join (select * from 源表 where size = 's3') b3 on a.xuhao = b3.xuhao
------解决方案--------------------
SELECT d.xuhao,MAX(IiF(size='s1',size,'')) AS s1,
MAX(IiF(size='s1',zh,'')) AS z1,
MAX(IiF(size='s2',js,'')) AS j1,
MAX(IiF(size='s2',size,'')) AS s2,
MAX(IiF(size='s2',zh,'')) AS z2,
MAX(IiF(size='s2',js,'')) AS j2,
MAX(IiF(size='s3',size,'')) AS s3,
MAX(IiF(size='s3',zh,'')) AS z3,
MAX(IiF(size='s3',js,'')) AS j3
FROM (
SELECT c1.*,c.`zh`,c.`size` AS nz,c.`js` FROM (
SELECT * FROM (SELECT DISTINCT xuhao FROM tt1) a,(SELECT DISTINCT size FROM tt1) b) c1
LEFT JOIN tt1 c ON c.xuhao=c1.xuhao AND c.size=c1.size
ORDER BY c1.size,c1.xuhao) d
GROUP BY d.xuhao
------解决方案--------------------
CLOS ALL
CREATE CURSOR T1 (XUHAO C(1),ZH I,SS C(2) ,JS I)
INSERT INTO T1 VALUES ('1',1,'s1',5)
INSERT INTO T1 VALUES ('2',2,'s1',6)
INSERT INTO T1 VALUES ('3',3,'s1',5)
INSERT INTO T1 VALUES ('4',4,'s1',6)
一个表 T1 的结构如下:
xuhao zh size js
1 1 s1 5
2 2 s1 6
3 3 s1 5
4 4 s1 6
2 5 s2 6
3 6 s2 5
4 7 s2 6
1 8 s3 5
2 9 s3 6
3 10 s3 5
4 11 s3 6
如何通过SQL 查询 得到如下结果
xuhao size1 zh1 js1 size2 zh2 js2 size3 zh3 js3
1 s1 1 5 s2 0 0 s3 8 5
2 s1 2 6 s2 5 6 s3 9 6
3 s1 3 5 s2 6 5 s3 10 5
4 s1 4 6 s2 7 6 s3 11 6
如果一个语句无法完成 那么几个语句可以完成
------解决方案--------------------
Select a.xuhao, ;
b1.size as size1, Nvl(b1.zh,0) as zh1, Nvl(b1.js,0) as js1, ;
b2.size as size2, Nvl(b2.zh,0) as zh2, Nvl(b2.js,0) as js2, ;
b3.size as size3, Nvl(b3.zh,0) as zh3, Nvl(b3.js,0) as js3 ;
from (select xuhao from 源表 group by xuhao) a ;
left join (select * from 源表 where size = 's1') b1 on a.xuhao = b1.xuhao ;
left join (select * from 源表 where size = 's2') b2 on a.xuhao = b2.xuhao ;
left join (select * from 源表 where size = 's3') b3 on a.xuhao = b3.xuhao
------解决方案--------------------
SELECT d.xuhao,MAX(IiF(size='s1',size,'')) AS s1,
MAX(IiF(size='s1',zh,'')) AS z1,
MAX(IiF(size='s2',js,'')) AS j1,
MAX(IiF(size='s2',size,'')) AS s2,
MAX(IiF(size='s2',zh,'')) AS z2,
MAX(IiF(size='s2',js,'')) AS j2,
MAX(IiF(size='s3',size,'')) AS s3,
MAX(IiF(size='s3',zh,'')) AS z3,
MAX(IiF(size='s3',js,'')) AS j3
FROM (
SELECT c1.*,c.`zh`,c.`size` AS nz,c.`js` FROM (
SELECT * FROM (SELECT DISTINCT xuhao FROM tt1) a,(SELECT DISTINCT size FROM tt1) b) c1
LEFT JOIN tt1 c ON c.xuhao=c1.xuhao AND c.size=c1.size
ORDER BY c1.size,c1.xuhao) d
GROUP BY d.xuhao
------解决方案--------------------
CLOS ALL
CREATE CURSOR T1 (XUHAO C(1),ZH I,SS C(2) ,JS I)
INSERT INTO T1 VALUES ('1',1,'s1',5)
INSERT INTO T1 VALUES ('2',2,'s1',6)
INSERT INTO T1 VALUES ('3',3,'s1',5)
INSERT INTO T1 VALUES ('4',4,'s1',6)