SQL统计：从北京到天津的与从天津到北京的汇总到一起

--建表
CREATE TABLE LuFei(
id INT,
fromaddress VARCHAR(50),
toaddress VARCHAR(50),
types VARCHAR(50),
prices INT
)
GO
--添加数据
INSERT LuFei SELECT 1,'北京','天津','交通费',100
UNION ALL SELECT 2,'北京','天津','交通费',200
UNION ALL SELECT 3,'天津','北京','交通费',300
UNION ALL SELECT 4,'北京','上海','交通费',400
UNION ALL SELECT 5,'上海','北京','交通费',500
UNION ALL SELECT 6,'朝阳','中关村','交通费',600
UNION ALL SELECT 7,'中关村','朝阳','交通费',700

GO
--执行查询
SELECT SUM(prices) AS addprice,COUNT(prices) AS coun, CASE WHEN (UNICODE(fromaddress) - UNICODE(toaddress)) > 1 THEN fromaddress + '<==>' +toaddress ELSE toaddress + '<==>' + fromaddress END AS LineName 
FROM  LuFei 
WHERE types='交通费' 
GROUP BY CASE WHEN (UNICODE(fromaddress) - UNICODE(toaddress)) > 1 THEN fromaddress + '<==>' +  toaddress ELSE toaddress+ '<==>' + fromaddress END ORDER BY addprice DESC
GO

--删除这个表
DROP TABLE LuFei
GO