编码 销售数量 零售价 销售日期
001 5 6 2008-01-01
002 2 12 2008-01-01
001 3 6 2008-01-02
002 8 12 2008-01-02
001 2 6.5 2008-01-03
003 10 12.5 2008-01-03我查询2008-01-02 到 2008-01-03 的数据结果编码 销售数量 销售金额此前销售 7 54 --2008-01-02以前的数据
001 5 31
002 8 96
003 10 125
001 5 6 2008-01-01
002 2 12 2008-01-01
001 3 6 2008-01-02
002 8 12 2008-01-02
001 2 6.5 2008-01-03
003 10 12.5 2008-01-03我查询2008-01-02 到 2008-01-03 的数据结果编码 销售数量 销售金额此前销售 7 54 --2008-01-02以前的数据
001 5 31
002 8 96
003 10 125
解决方案 »
- SQL2008,如何初始化数据库,删除里面的数据!
- 求解一sql语句?
- SQLserver2005转换SQLserver2000
- sql 查询 XML
- java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Error establishing socket
- 问个触发器问题......
- 补学者,来请教高手解答
- 江湖告急!!(在线等,最好一小时给答案!!!THANKYOU)
- 请问各位在FOXPRO中有一个"编号"字段,用什么语句查询才能显示出此字段中开始的两个字节为12的所有记录?
- 请教nonono
- 为什么用xp_fixeddrives得到的磁盘可用空间与实际不符?
- 有点难度那位给我看下
from ta where 销售日期 between '2008-01-02' and '2008-01-03'
union
select '此前销售',sum(销售数量), sum(销售数量*零售价 )
from ta
where 销售日期 < '2008-01-02'
from tb
where 销售日期<'2008-01-02'
union all
select 编码,sum(销售数量) 销售数量,sum(销售数量*零售价) as 销售金额
from tb
where 销售日期 between '2008-01-02' and '2008-01-03'
group by 编码
union all
select 编码, 销售数量,销售数量=sum(销售数量),销售金额=sum(销售数量*零售价) from tablename where where 销售日期>'2008-01-02' group by 编码
union all
select 编码, 销售数量,销售数量=sum(销售数量),销售金额=sum(销售数量*零售价) from tablename where where 销售日期>'2008-01-01' group by 编码
table where 销售日期>=2008-01-01 and 销售日期<'2008-01-04'
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (code varchar(3),qty int,price money,date datetime)
insert into #T
select '001',5,6,'2008-01-01' union all
select '002',2,12,'2008-01-01' union all
select '001',3,6,'2008-01-02' union all
select '002',8,12,'2008-01-02' union all
select '001',2,6.5,'2008-01-03' union all
select '003',10,12.5,'2008-01-03'select code='此前销售',qty=sum(qty),money=sum(qty*price) from #T where date < '2008-01-02'
union all
select code,sum(qty),sum(qty*price) from #T where date between '2008-01-02' and '2008-01-03' group by code/*
code qty money
-------- ----------- ---------------------
此前销售 7 54.00
001 5 31.00
002 8 96.00
003 10 125.00
*/
不用 union all 可以做到吗?
select 编码,sum(销售数量) as 销售数量,sum(销售数量*零售价) as 销售金额
from tb
where 销售日期 between '2008-01-02' and '2008-01-03'
group by 编码
union all
select '此前销售' as 编码,sum(销售数量) as 销售数量,sum(销售数量*零售价) as 销售金额
from tb
where 销售日期<'2008-01-02'
from tb
where 销售日期 < '2008-01-03'
group by case when 销售日期 < '2008-01-02' then '此前销售' else 编码 end
create table #T (编码 varchar(3),销售数量 int,零售价 money,销售日期 datetime)
insert into #T
select '001',5,6,'2008-01-01' union all
select '002',2,12,'2008-01-01' union all
select '001',3,6,'2008-01-02' union all
select '002',8,12,'2008-01-02' union all
select '001',2,6.5,'2008-01-03' union all
select '003',10,12.5,'2008-01-03'
goselect case when 销售日期 < '2008-01-02' then '此前销售' else 编码 end as 编码,sum(销售数量) as 销售数量,
sum(销售数量*零售价) as 销售金额
from #t
where 销售日期 <= '2008-01-03'
group by case when 销售日期 < '2008-01-02' then '此前销售' else 编码 endif object_id('tempdb.dbo.#T') is not null drop table #T/*编码 销售数量 销售金额
-------- ----------- ---------------------
001 5 31.0000
002 8 96.0000
003 10 125.0000
此前销售 7 54.0000(所影响的行数为 4 行)*/
from ta where 销售日期<'2008-01-02'
union all
select 编码,sum(销售数量) as 销售数量,sum(销售数量*销售金额) as 销售金额
from ta where 销售日期>='2008-01-02'
group by 编码
order by 编码