求教一个SQL 语句
一个表 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
如果一个语句无法完成 那么几个语句可以完成 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
[解决办法]
本帖最后由 wwwwb 于 2013-03-21 09:15:13 编辑 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)
INSERT INTO T1 VALUES ('2',5,'s2',6)
INSERT INTO T1 VALUES ('3',6,'s2',5)
INSERT INTO T1 VALUES ('4',7,'s2',6)
INSERT INTO T1 VALUES ('1',8,'s3',5)
INSERT INTO T1 VALUES ('2',9,'s3',6)
INSERT INTO T1 VALUES ('3',10,'s3',5)
INSERT INTO T1 VALUES ('4',11,'s3',6)
ALTER TABLE T1 ADD COLUMN SSS I
REPLA ALL SSS WITH VAL(SUBS(SS,2,1))
BROW
SELECT SS,SSS INTO CURSOR TMP FROM T1 GROUP BY SS
SELECT TMP
BROW
LCSTR="SELECT XUHAO"
SCAN
LCSTR=LCSTR+",SUM(IIF(ALLTRIM(SS)=='"+ALLTRIM(SS)+"',SSS,SSS)) AS SS"+ALLTRIM(SUBS(SS,2,1))+;
",SUM(IIF(ALLTRIM(SS)=='"+ALLTRIM(SS)+"',ZH,0)) AS ZH"+ALLTRIM(SUBS(SS,2,1))+;
",SUM(IIF(ALLTRIM(SS)=='"+ALLTRIM(SS)+"',JS,0)) AS JS"+ALLTRIM(SUBS(SS,2,1))+;
IIF(RECNO()<RECCOUNT(),[],[ FROM T1 GROUP BY XUHAO INTO CURSOR T2])
ENDSCAN
MESSAGEBOX(LCSTR)
&LCSTR
BROWSE
只有size列未能正确填充，期待正确答案！
------解决方案--------------------
CREATE CURSOR T1 (XUHAO C(1),ZH I,SIZE 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)
INSERT INTO T1 VALUES ('2',5,'S2',6)
INSERT INTO T1 VALUES ('3',6,'S2',5)
INSERT INTO T1 VALUES ('4',7,'S2',6)
INSERT INTO T1 VALUES ('1',8,'S3',5)
INSERT INTO T1 VALUES ('2',9,'S3',6)
INSERT INTO T1 VALUES ('3',10,'S3',5)
INSERT INTO T1 VALUES ('4',11,'S3',6)
SELECT A.XUHAO, ;
NVL(B1.SIZE,[S1]) AS SIZE1, NVL(B1.ZH,0) AS ZH1, NVL(B1.JS,0) AS JS1, ;
NVL(B2.SIZE,[S2]) AS SIZE2, NVL(B2.ZH,0) AS ZH2, NVL(B2.JS,0) AS JS2, ;
NVL(B3.SIZE,[S3]) AS SIZE3, NVL(B3.ZH,0) AS ZH3, NVL(B3.JS,0) AS JS3 ;
FROM (SELECT XUHAO FROM T1 GROUP BY XUHAO) A ;
LEFT JOIN (SELECT * FROM T1 WHERE SIZE = 'S1') B1 ON A.XUHAO = B1.XUHAO ;
LEFT JOIN (SELECT * FROM T1 WHERE SIZE = 'S2') B2 ON A.XUHAO = B2.XUHAO ;
LEFT JOIN (SELECT * FROM T1 WHERE SIZE = 'S3') B3 ON A.XUHAO = B3.XUHAO