SELECT B.[ID],B.[编号],B.[名字],B.[别名],B.[单位],
--销售退货
ISNUll((SELECT SUM(C.数量) FROM [单据] A,采购销售 C WHERE A.制单日期 BETWEEN @起始日期 AND @结束日期 AND C.商品信息ID = B.ID AND A.[标识] = 8 AND A.仓库信息ID = CASE WHEN @仓库信息ID = 0 THEN A.仓库信息ID ELSE @仓库信息ID END AND A.来往单位信息ID = CASE WHEN @来往单位信息ID = 0 THEN A.来往单位信息ID ELSE @来往单位信息ID END),0) AS 销售退货,
--销售总数量(包涵退货)
ISNUll((SELECT SUM(C.数量) FROM [单据] A,采购销售 C WHERE A.制单日期 BETWEEN @起始日期 AND @结束日期 AND C.商品信息ID = B.ID AND A.[标识] = 2 AND A.仓库信息ID = CASE WHEN @仓库信息ID = 0 THEN A.仓库信息ID ELSE @仓库信息ID END AND A.来往单位信息ID = CASE WHEN @来往单位信息ID = 0 THEN A.来往单位信息ID ELSE @来往单位信息ID END),0) AS 销售总数量,
--实际销售数量
(销售总数量 - 销售退货) AS 实际销售数量,
--销售均价
(SELECT AVG(C.单价) FROM [单据] A,采购销售 C WHERE A.制单日期 BETWEEN @起始日期 AND @结束日期 AND C.商品信息ID = B.ID AND [标识] = 2 AND A.仓库信息ID = CASE WHEN @仓库信息ID = 0 THEN A.仓库信息ID ELSE @仓库信息ID END AND A.来往单位信息ID = CASE WHEN @来往单位信息ID = 0 THEN A.来往单位信息ID ELSE @来往单位信息ID END) AS 销售均价,
--销售总金额
(SELECT SUM(C.数量 * C.单价) FROM [单据] A,采购销售 C WHERE A.制单日期 BETWEEN @起始日期 AND @结束日期 AND C.商品信息ID = B.ID AND [标识] = 2 AND A.仓库信息ID = CASE WHEN @仓库信息ID = 0 THEN A.仓库信息ID ELSE @仓库信息ID END AND A.来往单位信息ID = CASE WHEN @来往单位信息ID = 0 THEN A.来往单位信息ID ELSE @来往单位信息ID END) - (SELECT SUM(C.数量 * C.单价) FROM [单据] A,采购销售 C WHERE A.制单日期 BETWEEN @起始日期 AND @结束日期 AND C.商品信息ID = B.ID AND [标识] = 8 AND A.仓库信息ID = CASE WHEN @仓库信息ID = 0 THEN A.仓库信息ID ELSE @仓库信息ID END AND A.来往单位信息ID = CASE WHEN @来往单位信息ID = 0 THEN A.来往单位信息ID ELSE @来往单位信息ID END) AS 销售金额,
--成本均价
(SELECT AVG(C.单价) FROM [单据] A,采购销售 C WHERE A.制单日期 BETWEEN @起始日期 AND @结束日期 AND C.商品信息ID = A.ID AND [标识] = 1) AS 成本均价,
--成本总金额
(实际销售数量 * 成本均价) AS 成本金额,
--毛利
(销售金额 - 成本金额) AS 毛利
FROM [商品信息] B WHERE B.[分类] = @分类ID提示,这句不能解析,是为什么,运来直接执行,又没有错误,放存储过,他却提示错误,
要怎么修改!
(销售总数量 - 销售退货) AS 实际销售数量,
存储CActionScriptselect库
--销售退货
ISNUll((SELECT SUM(C.数量) FROM [单据] A,采购销售 C WHERE A.制单日期 BETWEEN @起始日期 AND @结束日期 AND C.商品信息ID = B.ID AND A.[标识] = 8 AND A.仓库信息ID = CASE WHEN @仓库信息ID = 0 THEN A.仓库信息ID ELSE @仓库信息ID END AND A.来往单位信息ID = CASE WHEN @来往单位信息ID = 0 THEN A.来往单位信息ID ELSE @来往单位信息ID END),0) AS 销售退货,
--销售总数量(包涵退货)
ISNUll((SELECT SUM(C.数量) FROM [单据] A,采购销售 C WHERE A.制单日期 BETWEEN @起始日期 AND @结束日期 AND C.商品信息ID = B.ID AND A.[标识] = 2 AND A.仓库信息ID = CASE WHEN @仓库信息ID = 0 THEN A.仓库信息ID ELSE @仓库信息ID END AND A.来往单位信息ID = CASE WHEN @来往单位信息ID = 0 THEN A.来往单位信息ID ELSE @来往单位信息ID END),0) AS 销售总数量,
--实际销售数量
(销售总数量 - 销售退货) AS 实际销售数量,
--销售均价
(SELECT AVG(C.单价) FROM [单据] A,采购销售 C WHERE A.制单日期 BETWEEN @起始日期 AND @结束日期 AND C.商品信息ID = B.ID AND [标识] = 2 AND A.仓库信息ID = CASE WHEN @仓库信息ID = 0 THEN A.仓库信息ID ELSE @仓库信息ID END AND A.来往单位信息ID = CASE WHEN @来往单位信息ID = 0 THEN A.来往单位信息ID ELSE @来往单位信息ID END) AS 销售均价,
--销售总金额
(SELECT SUM(C.数量 * C.单价) FROM [单据] A,采购销售 C WHERE A.制单日期 BETWEEN @起始日期 AND @结束日期 AND C.商品信息ID = B.ID AND [标识] = 2 AND A.仓库信息ID = CASE WHEN @仓库信息ID = 0 THEN A.仓库信息ID ELSE @仓库信息ID END AND A.来往单位信息ID = CASE WHEN @来往单位信息ID = 0 THEN A.来往单位信息ID ELSE @来往单位信息ID END) - (SELECT SUM(C.数量 * C.单价) FROM [单据] A,采购销售 C WHERE A.制单日期 BETWEEN @起始日期 AND @结束日期 AND C.商品信息ID = B.ID AND [标识] = 8 AND A.仓库信息ID = CASE WHEN @仓库信息ID = 0 THEN A.仓库信息ID ELSE @仓库信息ID END AND A.来往单位信息ID = CASE WHEN @来往单位信息ID = 0 THEN A.来往单位信息ID ELSE @来往单位信息ID END) AS 销售金额,
--成本均价
(SELECT AVG(C.单价) FROM [单据] A,采购销售 C WHERE A.制单日期 BETWEEN @起始日期 AND @结束日期 AND C.商品信息ID = A.ID AND [标识] = 1) AS 成本均价,
--成本总金额
(实际销售数量 * 成本均价) AS 成本金额,
--毛利
(销售金额 - 成本金额) AS 毛利
FROM [商品信息] B WHERE B.[分类] = @分类ID提示,这句不能解析,是为什么,运来直接执行,又没有错误,放存储过,他却提示错误,
要怎么修改!
(销售总数量 - 销售退货) AS 实际销售数量,
存储CActionScriptselect库
SELECT B.[ID] ,
B.[编号] ,
B.[名字] ,
B.[别名] ,
B.[单位] ,
--销售退货
ISNULL(( SELECT SUM(C.数量)
FROM [单据] A ,
采购销售 C
WHERE A.制单日期 BETWEEN @起始日期 AND @结束日期
AND C.商品信息ID = B.ID
AND A.[标识] = 8
AND A.仓库信息ID = CASE WHEN @仓库信息ID = 0 THEN A.仓库信息ID
ELSE @仓库信息ID
END
AND A.来往单位信息ID = CASE WHEN @来往单位信息ID = 0
THEN A.来往单位信息ID
ELSE @来往单位信息ID
END
), 0) AS 销售退货 ,
--销售总数量(包涵退货)
ISNULL(( SELECT SUM(C.数量)
FROM [单据] A ,
采购销售 C
WHERE A.制单日期 BETWEEN @起始日期 AND @结束日期
AND C.商品信息ID = B.ID
AND A.[标识] = 2
AND A.仓库信息ID = CASE WHEN @仓库信息ID = 0 THEN A.仓库信息ID
ELSE @仓库信息ID
END
AND A.来往单位信息ID = CASE WHEN @来往单位信息ID = 0
THEN A.来往单位信息ID
ELSE @来往单位信息ID
END
), 0) AS 销售总数量 ,
--实际销售数量
( ISNULL(( SELECT SUM(C.数量)
FROM [单据] A ,
采购销售 C
WHERE A.制单日期 BETWEEN @起始日期 AND @结束日期
AND C.商品信息ID = B.ID
AND A.[标识] = 2
AND A.仓库信息ID = CASE WHEN @仓库信息ID = 0 THEN A.仓库信息ID
ELSE @仓库信息ID
END
AND A.来往单位信息ID = CASE WHEN @来往单位信息ID = 0
THEN A.来往单位信息ID
ELSE @来往单位信息ID
END
), 0)
- ISNULL(( SELECT SUM(C.数量)
FROM [单据] A ,
采购销售 C
WHERE A.制单日期 BETWEEN @起始日期 AND @结束日期
AND C.商品信息ID = B.ID
AND A.[标识] = 8
AND A.仓库信息ID = CASE WHEN @仓库信息ID = 0 THEN A.仓库信息ID
ELSE @仓库信息ID
END
AND A.来往单位信息ID = CASE WHEN @来往单位信息ID = 0
THEN A.来往单位信息ID
ELSE @来往单位信息ID
END
), 0) ) AS 实际销售数量 ,
--销售均价
( SELECT AVG(C.单价)
FROM [单据] A ,
采购销售 C
WHERE A.制单日期 BETWEEN @起始日期 AND @结束日期
AND C.商品信息ID = B.ID
AND [标识] = 2
AND A.仓库信息ID = CASE WHEN @仓库信息ID = 0 THEN A.仓库信息ID
ELSE @仓库信息ID
END
AND A.来往单位信息ID = CASE WHEN @来往单位信息ID = 0 THEN A.来往单位信息ID
ELSE @来往单位信息ID
END
) AS 销售均价 ,
--销售总金额
( SELECT SUM(C.数量 * C.单价)
FROM [单据] A ,
采购销售 C
WHERE A.制单日期 BETWEEN @起始日期 AND @结束日期
AND C.商品信息ID = B.ID
AND [标识] = 2
AND A.仓库信息ID = CASE WHEN @仓库信息ID = 0 THEN A.仓库信息ID
ELSE @仓库信息ID
END
AND A.来往单位信息ID = CASE WHEN @来往单位信息ID = 0 THEN A.来往单位信息ID
ELSE @来往单位信息ID
END
)
- ( SELECT SUM(C.数量 * C.单价)
FROM [单据] A ,
采购销售 C
WHERE A.制单日期 BETWEEN @起始日期 AND @结束日期
AND C.商品信息ID = B.ID
AND [标识] = 8
AND A.仓库信息ID = CASE WHEN @仓库信息ID = 0 THEN A.仓库信息ID
ELSE @仓库信息ID
END
AND A.来往单位信息ID = CASE WHEN @来往单位信息ID = 0 THEN A.来往单位信息ID
ELSE @来往单位信息ID
END
) AS 销售金额 ,
--成本均价
( SELECT AVG(C.单价)
FROM [单据] A ,
采购销售 C
WHERE A.制单日期 BETWEEN @起始日期 AND @结束日期
AND C.商品信息ID = A.ID
AND [标识] = 1
) AS 成本均价 ,
--成本总金额
( 实际销售数量 * 成本均价 ) AS 成本金额 ,
--毛利
( 销售金额 - 成本金额 ) AS 毛利
FROM [商品信息] B
WHERE B.[分类] = @分类ID
解决办法:
1.直接使用原始代码段
2.嵌套一层
( 销售金额 - 成本金额 ) AS 毛利
这两个也涉及到了这个问题。
insert into @t select 1,2--这种是可以的
select col1,col2,col1+col2 as col3 from @t--这种也是可以的
select col1,col2,col1+col2 as col3 from @t order by col3 --这种不行
--select col1,col2,col1+col2 as col3,col2+col3 as col4 from @t order by col3 --1.解决办法一:用col1+col2代替col3
select col1,col2,col1+col2 as col3,col2+(col1+col2) as col4 from @t order by col3 --2.解决办法二:嵌套
;WITH maco AS
(
select col1,col2,col1+col2 as col3 from @t
)
SELECT * ,col2+col3 as col4 FROM maco