数量统计后排序

[cdnlkj]

有个零件出库记录的表，包括每次出库的客户号、零件编号（1~5个）及这些零件的数量，列名如下：
SerialNo、Part No.-a、Qty.-a、Part No.-b、Qty.-b、Part No.-c、Qty.-c、Part No.-d、Qty.-d、Part No.-e、Qty.-e

现在我想列出某个零件的使用清单，包括客户号、该客户的使用这个零件的数量，然后按照使用数量排序。
我写了下面的SQL语句，但列出的是该零件使用的总和，我该怎么改？谢谢！

SELECT SerialNo,Total=
(select sum([Qty.-a]) from 记录表 WHERE [Part No.-a] LIKE '%19e22120')
+(select sum([Qty.-b]) from 记录表 WHERE [Part No.-b] LIKE '%19e22120')
+(select sum([Qty.-c]) from 记录表 WHERE [Part No.-c] LIKE '%19e22120')
+(select sum([Qty.-d]) from 记录表 WHERE [Part No.-d] LIKE '%19e22120')
+(select sum([Qty.-e]) from 记录表 WHERE [Part No.-e] LIKE '%19e22120')
FROM 记录表 GROUP BY SerialNo ORDER BY Total DESC

[cdnlkj]

"19e22120"是零件编号的后8位

[zicxc]

SELECT SerialNo,Total=
sum(case when [Part No.-a] LIKE '%19e22120' then [Qty.-a] else 0 end)
+sum(case when [Part No.-b] LIKE '%19e22120' then [Qty.-b] else 0 end)
+sum(case when [Part No.-c] LIKE '%19e22120' then [Qty.-c] else 0 end)
+sum(case when [Part No.-d] LIKE '%19e22120' then [Qty.-d] else 0 end)
+sum(case when [Part No.-e] LIKE '%19e22120' then [Qty.-e] else 0 end)
FROM 记录表 GROUP BY SerialNo ORDER BY Total DESC

[zicxc]

SELECT SerialNo,Total=
sum(case when [Part No.-a] LIKE '%19e22120' then [Qty.-a] else 0 end)
+sum(case when [Part No.-b] LIKE '%19e22120' then [Qty.-b] else 0 end)
+sum(case when [Part No.-c] LIKE '%19e22120' then [Qty.-c] else 0 end)
+sum(case when [Part No.-d] LIKE '%19e22120' then [Qty.-d] else 0 end)
+sum(case when [Part No.-e] LIKE '%19e22120' then [Qty.-e] else 0 end)
FROM 记录表 GROUP BY SerialNo ORDER BY 2 DESC

[cdnlkj]

“ORDER BY 2”是什么意思？

[zsforever]

第2列的意思
select * from
(
SELECT SerialNo,Total=sum(case when [Part No.-a] LIKE '%19e22120' then [Qty.-a] else 0 end) FROM 记录表 GROUP BY SerialNo unoin all
SELECT SerialNo,Total=sum(case when [Part No.-b] LIKE '%19e22120' then [Qty.-b] else 0 end) FROM 记录表 GROUP BY SerialNo unoin all
SELECT SerialNo,Total=sum(case when [Part No.-c] LIKE '%19e22120' then [Qty.-c] else 0 end) FROM 记录表 GROUP BY SerialNo unoin all
SELECT SerialNo,Total=sum(case when [Part No.-d] LIKE '%19e22120' then [Qty.-d] else 0 end) FROM 记录表 GROUP BY SerialNo unoin all
SELECT SerialNo,Total=sum(case when [Part No.-e] LIKE '%19e22120' then [Qty.-e] else 0 end)
) A order by Total

[cdnlkj]

多谢啦！