急急急！！求SQL语句

[taohua300]

原文档结构如下：
cfname rate
47586844 t f.txt 521.30
47586846 t f.txt 56.23
47586845 f.txt 526.00
47586844 t c.txt 473.00
47586846 t c.txt 0.13
要求生成结构如下：
cfname frate
47586844 t.txt 521.30 473.00
47586846 t.txt 56.23 0.13
47586845 f.txt 526.00 0.00

文件名都是“ f”或“ c”结束（不是后缀名），处理后，把“ f”和“ c“之前相同的放在一行，且” f“的rate在前，” c”在后，缺少“ f” 或“ c”的，补零，生成另外一张表
非常急

[WWWWA]

SELECT LEFT(CFNAME,9),;
SUM(IIF(SUBSTR(cfname,10)='t f.txt' OR SUBSTR(cfname,10)='f.txt', rate,00000.00)),;
SUM(IIF(SUBSTR(cfname,10)='t c.txt', rate,00000.00));
into dbf newtt FROM TT1 GROUP BY 1

[wwwwb]

就是交叉表
SELECT LEFT(CFNAME,9),;
SUM(IIF(SUBSTR(cfname,10)='t f.txt' OR SUBSTR(cfname,10)='f.txt', rate,00000.00)),;
SUM(IIF(SUBSTR(cfname,10)='t c.txt', rate,00000.00));
into dbf newtt FROM TT1 GROUP BY 1

or

SELECT LEFT(CFNAME,9),;
max(IIF(SUBSTR(cfname,10)='t f.txt' OR SUBSTR(cfname,10)='f.txt', rate,00000.00)),;
max(IIF(SUBSTR(cfname,10)='t c.txt', rate,00000.00));
into dbf newtt FROM r:\temp\TT1 GROUP BY 1

[taohua300]

先谢一下，我试试看

[taohua300]

[Quote=引用 2 楼 wwwwb 的回复:]
就是交叉表
SELECT LEFT(CFNAME,9),;
SUM(IIF(SUBSTR(cfname,10)='t f.txt' OR SUBSTR(cfname,10)='f.txt', rate,00000.00)),;
SUM(IIF(SUBSTR(cfname,10)='t c.txt', rate,00000.00));
into dbf newtt  FROM TT1 GROUP BY 1

or

SELECT LEFT(CFNAME,9),;
max(IIF(SUBSTR(cfname,10)='t f.txt' OR SUBSTR(cfname,10)='f.txt', rate,00000.00)),;
max(IIF(SUBSTR(cfname,10)='t c.txt', rate,00000.00));
into dbf newtt  FROM r:\temp\TT1 GROUP BY 1

[/Quote]
已经上传到http://www.access911.net/csdn/FileDescription.asp?id=99&mdb=2009-12-31&mode=1
帮忙看以下，
谢谢

[taohua300]

刚上传的那个文件不行，又重新传了一下
http://www.access911.net/csdn/FileDescription.asp?id=100&mdb=2009-12-31&mode=1

[wwwwb]

SELECT bz,;
SUM(IIF(bz1='t.txt' OR bz1='f.txt', VAL(newrate),00000.00)),;
SUM(IIF(bz1='c.txt', VAL(newrate),00000.00));
FROM ;
(SELECT fname,LEFT(fname,AT(' ',fname)-1) as bz,;
RIGHT(fname,LEN(fname)-AT('|',fname)) as newrate,;
SUBSTR(fname,AT(' ',fname)+1,AT('|',fname)-AT(' ',fname)-1) as bz1;
FROM r:\temp\yu) a ;
GROUP BY bz

实际上就是拆分字符串再分组

[taohua300]

不好意思，第一次给的地址的文件是错误的，第二次的才是正确的，从.txt文件添加的时候弄错了，

[taohua300]

很少用SQL语句，真到用的时候是在不知道怎么写了

[wwwwb]

SELECT bz,;
SUM(IIF(bz1='t.txt' OR bz1='f.txt', VAL(rate),00000.00)),;
SUM(IIF(bz1='c.txt', VAL(rate),00000.00));
FROM ;
(SELECT fname,LEFT(fname,AT(' ',fname)-1) as bz,;
RIGHT(fname,LEN(fname)-AT('|',fname)) as newrate,;
SUBSTR(fname,AT(' ',fname)+1,AT('|',fname)-AT(' ',fname)-1) as bz2,;
RIGHT(ALLTRIM(fname),5) as bz1,rate;
FROM r:\temp\yu) a ;
GROUP BY bz

自行修改一下