1.用一条SQL列出 仓存;
select itemno,sum(qty)-(select sum(qty) from inout_list a where a.itemno=b.itemno and flag=0 group by itemno)
from inout_list b where flag=1 group by itemno2.用一条SQL列出 每个产品的库存金额;
select itemno,sum(qty)*(select in_price from item where itemno=b.itemno)
-(select sum(qty) from inout_list a where a.itemno=b.itemno and flag=0 group by itemno)*
(select out_price from item where itemno=b.itemno) )
from inout_list b where flag=1 group by itemno3.用一条SQL列出 所有产品的库存金额.
select itemno,sum(库存) as 总库存 from
(
select itemno,库存=sum(qty)*(select in_price from item where itemno=b.itemno)
-(select sum(qty) from inout_list a where a.itemno=b.itemno and flag=0 group by itemno)*
(select out_price from item where itemno=b.itemno) )
from inout_list b where flag=1 group by itemno
) a
group by itemno
select itemno,sum(qty)-(select sum(qty) from inout_list a where a.itemno=b.itemno and flag=0 group by itemno)
from inout_list b where flag=1 group by itemno2.用一条SQL列出 每个产品的库存金额;
select itemno,sum(qty)*(select in_price from item where itemno=b.itemno)
-(select sum(qty) from inout_list a where a.itemno=b.itemno and flag=0 group by itemno)*
(select out_price from item where itemno=b.itemno) )
from inout_list b where flag=1 group by itemno3.用一条SQL列出 所有产品的库存金额.
select itemno,sum(库存) as 总库存 from
(
select itemno,库存=sum(qty)*(select in_price from item where itemno=b.itemno)
-(select sum(qty) from inout_list a where a.itemno=b.itemno and flag=0 group by itemno)*
(select out_price from item where itemno=b.itemno) )
from inout_list b where flag=1 group by itemno
) a
group by itemno
解决方案 »
- 客户端连结SQl服务器问题
- 算法求助
- 怎么写这样的Sql语句,高手来看看
- 两个表之间传递数据(biao1列名与biao2字段值对比),求助,在线等
- Sql数据合并问题(急啊)
- 在线等、给分:left(content,?) content 数据类型为text,请问?怎么设,我要取前100个字
- 绝对经典的问题!在线等!
- 存储过程中如果出错,要继续运行,如何处理?有没有相当于vb中的 On error resume next ?在线等待。谢谢
- 谁能解决这个问题愿与其结为好友
- 关于Sql server中数据设备与数据库的建立,先谢了!!!分不够的话还加!
- 求SQl语句
- 如何搜索表中某列中所有行中特点字符或字符组的数量
风云大侠,你的思路正确,但语法有误,改正语法:1.用一条SQL列出 仓存;
select itemno,sum(qty)-(select sum(qty) from inout_list a where a.itemno=b.itemno and flag=2 group by itemno)
from inout_list b where flag=1 group by itemno2.用一条SQL列出 每个产品的库存金额;
select itemno,(sum(qty)*(select in_price from item where itemno=b.itemno))
-((select sum(qty) from inout_list a where a.itemno=b.itemno and flag=0 group by itemno)*
(select in_price from item where itemno=b.itemno))
from inout_list b where flag=1 group by itemno3.用一条SQL列出 所有产品的库存金额.执行不出来,应该是对第二问的再次求和就行了
select sum(库存) as 总库存 from(
select 库存=(sum(qty)*(select in_price from item where itemno=b.itemno))
-((select sum(qty) from inout_list a where a.itemno=b.itemno and flag=2 group by itemno)*
(select in_price from item where itemno=b.itemno))
from inout_list b where flag=1 group by itemno)
as c
呵呵,风云兄,你谦虚了,现在我把第三题在你的基础上修改了一下,你看行不行?
select C.itemno,(R_qty-C_qty) as qty from
(select itemno,sum(qty) as R_qty from INOut_list where flag=1 group by itemno) R,
(select itemno,sum(qty) as C_qty from INOut_list where flag=2 group by itemno) C
where R.itemno=C.itemno
union
select itemno,qty from INOut_list where flag=1
这样执行出来达不到预期结果,不过你的另一种方法值得探讨.
from inout_list b where flag=1 group by itemno''的确是会不一样它要是有入没出呢??
select 1-NUll 等于 NUll 的情况
select itemno,sum(case flag when 1 then qty else -qty end)
from INOut_list
group by itemno2 用一条SQL列出 每个产品的库存金额;
select itemno,sum(case flag when 1 then qty else -qty end) * (select in_price from item where itemno = INOut_list.itemno )
from INOut_list
group by itemno3
select sum(sum(case flag when 1 then qty else -qty end) * (select in_price from item where itemno = INOut_list.itemno ))
from INOut_list
select sum((case flag when 1 then qty else -qty end) * (select in_price from item where itemno = INOut_list.itemno )
from INOut_list
http://community.csdn.net/Expert/topic/4337/4337895.xml?temp=.4944116
我要急死了,帮帮忙呀大家
第三问还是不行,系统提示:
服务器: 消息 130,级别 15,状态 1,行 1
不能对包含聚合或子查询的表达式执行聚合函数。
有一张采购订单表(orders),表的结构如下:(订单编号,采购总金额)
一张收款记录表(money),表的结构如下:(订单编号,收款日期,收款金额);
用一条SQL语句求欠收款记录表。
FROM 采购订单表 A LEFT OUTER JOIN
(SELECT 订单编号,SUM(收款金额) AS 收款总金额
FROM 收款记录表
GROUP BY 订单编号) B
ON A.订单编号=B.订单编号
SELECT itemno, 仓存 = SUM(CASE flag WHEN 1 THEN qty WHEN 2 THEN -qty END)
FROM [INOut_list]
GROUP BY itemno2.
SELECT A.itemno, A.in_price * B.仓存
FROM item A,(SELECT itemno, 仓存 = SUM(CASE flag WHEN 1 THEN qty WHEN 2 THEN -qty END)
FROM [INOut_list]
GROUP BY itemno) B
WHERE A.itemno = B.itemno3.
SELECT SUM(A.in_price * B.仓存)
FROM item A,(SELECT itemno, 仓存 = SUM(CASE flag WHEN 1 THEN qty WHEN 2 THEN -qty END)
FROM [INOut_list]
GROUP BY itemno) B
WHERE A.itemno = B.itemno
-(select (sum(b.qty )* (select out_price from item as d where a.itemno=d.itemno))
from inout_list as b where b.flag=2 and a.itemno=b.itemno
group by itemno) from inout_list a where a.flag='1' group by itemno第2个问题,第2个会,第一个就不用说了。
select a.itemno , sum(case a.flag when 1 then a.qty * b.in_price else -qty * b.in_price end)
from INOut_list a inner join item b on a.itemno = b.itemno
group by a.itemno 改正3
select sum(case a.flag when 1 then a.qty * b.in_price else -qty * b.in_price end)
from INOut_list a inner join item b on a.itemno = b.itemno