有个零件出库记录的表,包括每次出库的客户号、零件编号(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
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
解决方案 »
- 求一简单语句,逆置行列
- 如何建立一个JOB 导出表中某些数据到文本,然后对这些数据处理,再删除?
- sql至少问题 请各位大神帮忙
- MSSQL Server 2000怎么把备份停止或取消?
- 代码看不懂啊~~!!!!!HELP !!!!!!!
- 合并两个表到一个临时表中的语句该怎么写?两个表结构不同
- 急,怎样修改列属性?
- 我的win2K server上装的Sql server7.0,改了机器名后重启时Sql server不能正常启动!
- 兩個SQL語句的比較,請有經驗及興趣的朋友指點.
- 请教高手:VFP的重入问题
- 急!!!关于两表关联,求部门中平均工资大于3000的员工所在部门的平均工资?????
- 来帮忙看一下
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
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
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