select country,sum(cost) as aa from file201206 group by country order by aa
country aa
市话冯芳慧 12.429999999999998
国内长途鲁红华 12.870000000000001
市话鲁红华 13.969999999999992
国内长途冯芳慧 14.850000000000001
市话付方涛 15.339999999999996
市话黄丽娟 15.619999999999999
国内长途王洁 15.840000000000002
国内长途黄琼婵 16.170000000000002
市话杨葵尚 19.139999999999986
国内长途钟小华 19.139999999999997
市话姚梅 19.689999999999994
市话潘冬云 19.799999999999994
市话甘红 20.899999999999991
结果中,我需要去掉市话和国内长途字段,只取后面的人名,再通过人名来sum计算
------解决方案--------------------
- SQL code
CREATE TABLE file201206 ( country VARCHAR(100), aa FLOAT)GOINSERT INTO file201206SELECT '市话冯芳慧', 12.429999999999998 UNIONSELECT '国内长途鲁红华', 12.870000000000001 UNIONSELECT '市话鲁红华', 13.969999999999992 UNIONSELECT '国内长途冯芳慧', 14.850000000000001 UNIONSELECT '市话付方涛', 15.339999999999996 UNIONSELECT '市话黄丽娟', 15.619999999999999 UNIONSELECT '国内长途王洁', 15.840000000000002 UNIONSELECT '国内长途黄琼婵', 16.170000000000002 UNIONSELECT '市话杨葵尚', 19.139999999999986 UNIONSELECT '国内长途钟小华', 19.139999999999997 UNIONSELECT '市话姚梅', 19.689999999999994 UNIONSELECT '市话潘冬云', 19.799999999999994 UNIONSELECT '市话甘红', 20.899999999999991GOSELECT REPLACE(REPLACE(country,'市话',''),'国内长途','') AS country,SUM(aa) AS aaFROM file201206GROUP BY REPLACE(REPLACE(country,'市话',''),'国内长途','')country aa冯芳慧 27.28付方涛 15.34甘红 20.9黄丽娟 15.62黄琼婵 16.17鲁红华 26.84潘冬云 19.8王洁 15.84杨葵尚 19.14姚梅 19.69钟小华 19.14