表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
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
--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
--2的话要做个1-12的表,调整下然后left join下--PS:这个好像没别的好优化了,坐等大神
你的数据类型是什么??如果需要精确 建议使用 DECIMAL2. 比如在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字段加索引 语句没什么可优化的了
(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
(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
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
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
5F的代码 0.123s
6F的代码 0.170sroy_88的代码不太明白 是什么意思 结贴晚了点 感谢各位 高手就是高手