关于此sql语句的若干个问题

[atmo]

表a是销售表 结构如下

id 商品ID 售出数量 售出价格

表b是进货表或者是库存表 结构如下

商品ID 进货价格

如下SQL得出 按月得出2011年每月的 销售总额 和 销售的成本总额

select 
sum(a.售出数量*售出价格) as price,
SUM(a.售出数量*进货价格) as BasePrice,
CONVERT(varchar(7),a.售出日期,120) as CurMonth 
from a  inner joinb 
on a.商品ID=b.id
where CONVERT(varchar(4),a.售出日期,120)='2011' 
group by CONVERT(varchar(7),a.售出日期,120) 
order by CONVERT(varchar(7),a.售出日期,120) desc

1.得出的结果Price和BasePrice 精确到小数点后7，8位，但是我的数量肯定是整数，销售价格或者进货价格最多是小数点后有3位，比如可能存在0.125类似这样的数据，Price和BasePrice只可能是存在小数点后的3位，现在是7，8位 为何？如何修改？我很质疑这个计算结果

2. 比如在2011-9月的时候没有销售记录 那么此时在查询结果中就不存在2001-9的记录，我想得出整年的每一个月的记录，能否SQL查询的时候直接 添加一条记录 price -->0 baseprice-->0 销售日期2011-9 ,如果不能在SQL中添加，在程序代码里如何添加，数据存储DataTable,求个算法，最好能避免循环判断12次。

3.以上SQL有严重的效率问题，1W多条数据，执行14S，无法忍受，请优化SQL.

感谢
atmo
2011-10-12

[--小F--]

1.得出的结果Price和BasePrice 精确到小数点后7，8位，但是我的数量肯定是整数，销售价格或者进货价格最多是小数点后有3位，比如可能存在0.125类似这样的数据，Price和BasePrice只可能是存在小数点后的3位，现在是7，8位 为何？如何修改？我很质疑这个计算结果
你的数据类型是什么？？如果需要精确 建议使用 DECIMAL

2. 比如在2011-9月的时候没有销售记录 那么此时在查询结果中就不存在2001-9的记录，我想得出整年的每一个月的记录，能否SQL查询的时候直接 添加一条记录 price -->0 baseprice-->0 销售日期2011-9 ,如果不能在SQL中添加，在程序代码里如何添加，数据存储DataTable,求个算法，最好能避免循环判断12次。
需要构造一个时间表来 left join 可以结合maste..spt_value 来构造 这个论坛和网上N多 如果需要 楼主跟帖说明

3.以上SQL有严重的效率问题，1W多条数据，执行14S，无法忍受，请优化SQL.

ID字段加索引 语句没什么可优化的了

[CalvinR]

[gogodiy]

这样试试

;with aaa as
(select cast('2011-'+case when LEN(number)=1 then '0'+CAST(number as varchar) else CAST(number as varchar) end+'-01' as datetime) as StartDate,
	DATEADD(DAY,-1,DATEADD(MONTH,1,cast('2011-'+case when LEN(number)=1 then '0'+CAST(number as varchar) else CAST(number as varchar) end+'-01' as datetime))) as EndDate
from master.dbo.spt_values 
where type='P'
and number between 1 and 12)
select 
CONVERT(varchar(7),a.售出日期,120) as CurMonth, 
convert(decimal(18,3),isnull(sum(a.售出数量*售出价格),0)) as price,
convert(decimal(18,3),isnull(sum(a.售出数量*进货价格),0)) as BasePrice
from a inner join b on (a.商品ID=b.id) 
inner join aaa as c on a.售出日期>=c.StartDate and a.售出日期<=c.EndDate
group by CONVERT(varchar(7),a.售出日期,120) 
order by CurMonth

[gogodiy]

稍微调整了下

;with aaa as
(select cast('2011-'+case when LEN(number)=1 then '0'+CAST(number as varchar) else CAST(number as varchar) end+'-01' as datetime) as StartDate,
	DATEADD(DAY,-1,DATEADD(MONTH,1,cast('2011-'+case when LEN(number)=1 then '0'+CAST(number as varchar) else CAST(number as varchar) end+'-01' as datetime))) as EndDate
from master.dbo.spt_values 
where type='P'
and number between 1 and 12)
select 
CONVERT(varchar(7),c.StartDate,120) as CurMonth, 
convert(decimal(18,3),isnull(sum(a.售出数量*售出价格),0)) as price,
convert(decimal(18,3),isnull(sum(a.售出数量*进货价格),0)) as BasePrice
from a inner join b on (a.商品ID=b.id) 
right join aaa as c on a.售出日期>=c.StartDate and a.售出日期<=c.EndDate
group by CONVERT(varchar(7),c.StartDate,120) 
order by CurMonth 

[atmo]

得出的结果Price和BasePrice 精确到小数点后7，8位，但是我的数量肯定是整数，销售价格或者进货价格最多是小数点后有3位，比如可能存在0.125类似这样的数据，Price和BasePrice只可能是存在小数点后的3位，现在是7，8位 为何？如何修改？

小数点后最多只有3位 这个数字乘以任何整数 结果不可能小数点后大于三位

所以这个结果 明显有问题 如何解决？

[geniuswjt]

--1 and 简单优化     售出日期上要建立索引
select 
CONVERT(varchar(7),a.售出日期,120) as CurMonth, 
convert(decimal(18,3),sum(a.售出数量*售出价格)) as price,
convert(decimal(18,3),sum(a.售出数量*进货价格)) as BasePrice
from a inner join b on (a.商品ID=b.id)
where a.售出日期>='2011-1-1' and a.售出日期<'2012-1-1'
group by CONVERT(varchar(7),a.售出日期,120) 
order by 1 desc

[geniuswjt]

--2的话要做个1-12的表，调整下然后left join下

--PS：这个好像没别的好优化了，坐等大神

[atmo]

顶起来啊 顶起来

[中国风]

DECLARE @dt DATETIME
SET @dt='2011-01-01'
;WITH a AS
(SELECT @dt AS dt1,@dt+' 23:59:59' AS dt2
UNION ALL
SELECT DATEADD(m,1,dt1),DATEADD(m,1,dt1+' 23:59:59') FROM a WHERE dt1<DATEADD(yy,1,@dt)
)
SELECT 
	sum(a.售出数量*售出价格) as price,
	SUM(a.售出数量*进货价格) as BasePrice,
	CONVERT(varchar(7),a.dt1,120) as CurMonth 
FROM 
	a
LEFT JOIN 
	(a2 INNER JOIN b ON a2.商品ID=b.ID) ON a2.售出日期 BETWEEN a.dt1 AND a.dt2
GROUP BY CONVERT(varchar(7),a.dt1,120)
ORDER BY CurMonth desc

[中国风]

以上方法試試效率

[oO寒枫Oo]

1.进货价格 你确定最多三位小数？
select * from a where len(cast(售出价格 as varchar))>6
2.要么做个日历表 要么用 maste..spt_value来构造一个
3.where CONVERT(varchar(4),a.售出日期,120)='2011' 改成区间比较的方式 严重怀疑你的服务器不行
1W多行数据要14S 很少见

[中国风]

改改

DECLARE @dt DATETIME
SET @dt='2011-01-01'
;WITH a AS
(SELECT @dt AS dt1,DATEADD(m,1,@dt) AS dt2
UNION ALL
SELECT DATEADD(m,1,dt1),DATEADD(m,2,dt1) FROM a WHERE dt1<DATEADD(yy,1,@dt)
)
SELECT 
    sum(a.售出数量*售出价格) as price,
    SUM(a.售出数量*进货价格) as BasePrice,
    CONVERT(varchar(7),a.dt1,120) as CurMonth 
FROM 
    a
LEFT JOIN 
    (a2 INNER JOIN b ON a2.商品ID=b.ID) ON a2.售出日期 >= a.dt1 AND a2.售出日期 <a.dt2
GROUP BY CONVERT(varchar(7),a.dt1,120)
ORDER BY CurMonth desc

[atmo]

1F的代码 0.50s
5F的代码 0.123s
6F的代码 0.170s

roy_88的代码不太明白 是什么意思

结贴晚了点 感谢各位 高手就是高手