如下:
表A
店铺 调出数
A 10
B 8
C 20 表B
店铺 销售数量 价格 日期
A 3 100 2011-01-01
A 4 200 2011-02-02
A 5 250 2011-03-03
B 10 120 2011-05-05
C 5 130 2011-06-06现在的想要的结果是:查出店铺的销售总和,条件是假如店铺的销售数量总和大于调出数则按调出数来计算并且价格按日期的先后获取价格,如果小于则按销售量计算,结果大概如下店铺 销售总和
A 3*100 + 4*200 + 3*250 价格为250的只取两个
B 8*250
C 5*130求大虾们帮帮忙!
表A
店铺 调出数
A 10
B 8
C 20 表B
店铺 销售数量 价格 日期
A 3 100 2011-01-01
A 4 200 2011-02-02
A 5 250 2011-03-03
B 10 120 2011-05-05
C 5 130 2011-06-06现在的想要的结果是:查出店铺的销售总和,条件是假如店铺的销售数量总和大于调出数则按调出数来计算并且价格按日期的先后获取价格,如果小于则按销售量计算,结果大概如下店铺 销售总和
A 3*100 + 4*200 + 3*250 价格为250的只取两个
B 8*250
C 5*130求大虾们帮帮忙!
解决方案 »
- 在线等___为什么我安装完,说发生错误1069 - 由于登录失败而无法启动服务.救命呀
- Mssql可以按GUID排序?
- windows server 2008 r2 X64系统上安装 sqlserver2005 32位 和64位有什么区别
- 一个很菜的问题,请大家帮帮我
- 求sql语句
- Clustered 和 Nonclustered Indexes 各有什么特点和区别及长短处??
- 用了CASE后把numeric类型转换成了VARCHAR类型,怎么再给转回去啊?
- 为什么我在设置发布服务器的时候说我的不是有效的路径或文件名?
- 链接服务器问题。。。回答就给分
- ODBC开发sql serverDB中sql语句的字符串中含有"'"怎么办?
- 请问各位大侠,帮忙看下下面的sql有什么错误?
- 关于SQL Server 2000中的异地数据传送问题(急)
(
select A.店铺,B.销售数量 * B.价格 AS 销售金额 FROM A LEFT JOIN B ON A.店铺 =B.店铺
)t1
GROUP BY 店铺大概吧,没测.
(
select b.店铺,b.价格,
对应数量=case when (select sum(销售数量) from b as tb
where tb.店铺=b.店铺 and tb.日期<=b.日期)<=a.调出数
then b.销售数量 when (select sum(销售数量) from b as tb
where tb.店铺=b.店铺 and tb.日期<=b.日期)>a.调出数 and
(select sum(销售数量) from b as tb where tb.店铺=b.店铺 and tb.日期<b.日期)>a.调出数
then 0 else a.调出数-(select sum(销售数量) from b as tb
where tb.店铺=b.店铺 and tb.日期<b.日期)
end
from a,b
where a.店铺=b.店铺
) as c group by 店铺
create table #results
(
shop_name varchar(50),
sal_total int
)
declare @shop_name varchar(50)
declare @out_num int
declare @sal_num int
declare @tot_num int
declare @price int
declare @sal_total int
declare triger_cursor1 cursor for
Select shop_name,out_num From tba;
open cursor1
fetch next from cursor1 into @shop_name, @out_num
while(@@fetch_status=0)
begin
set @tot_num=0
set @sal_total=0
declare cursor2 cursor for
Select sal_num From tbb where shop_name = @shop_name
open cursor2
fetch next from cursor2 into @sal_num, @price
while(@@fetch_status=0)
begin
set @tot_num=@tot_num+@sal_num
if(@tot_num<@out_num)
begin
set @sal_total=@sal_total+@sal_num*@price
end
else
begin
set @sal_total=@sal_total+@price*(@out_num-@tot_num+@sal_num)
break
end
end
fetch next from cursor2 into @sal_num, @price
close cursor2
deallocate cursor2
insert into #results values(@shop_name,@sal_total)
fetch next from cursor1 into @shop_name, @out_num
end
close cursor1
deallocate cursor1select * from #results
店铺 销售额 数量
A 100 3
A 200 4
A 250 3
B 120 null
C 130 5
店铺A的值是对了,但B为什么为空,请大侠再指教
insert into 表A select 'A',10
insert into 表A select 'B',8
insert into 表A select 'C',20
create table 表B(店铺 varchar(10),销售数量 int,价格 int,日期 datetime)
insert into 表B select 'A',3,100,'2011-01-01'
insert into 表B select 'A',4,200,'2011-02-02'
insert into 表B select 'A',5,250,'2011-03-03'
insert into 表B select 'B',10,120,'2011-05-05'
insert into 表B select 'C',5,130,'2011-06-06'
go
;with c1 as(
select row_number()over(partition by 店铺 order by 日期)rn,* from 表B
),c2 as(
select a.rn,a.店铺,(case when a.销售数量<b.调出数 then a.销售数量 else b.调出数 end)*a.价格 销售总和,a.日期,
b.调出数-a.销售数量 as 剩余量
from c1 a inner join 表a b on a.店铺=b.店铺 and a.rn=1
union all
select a.rn,a.店铺,(case when a.销售数量<b.剩余量 then a.销售数量 else b.剩余量 end)*a.价格 销售总和,a.日期,
b.剩余量-a.销售数量 as 剩余量
from c1 a inner join c2 b on a.店铺=b.店铺 and a.rn=b.rn+1
)
select 店铺,sum(销售总和)as 销售总和 from c2 group by 店铺
/*
店铺 销售总和
---------- -----------
A 1850
B 960
C 650(3 行受影响)*/
go
drop table 表A,表B
create table 表B(店铺 varchar(10),销售数量 int,价格 int,日期 datetime)
insert into 表B select 'A',3,100,'2011-01-01'
insert into 表B select 'A',4,200,'2011-02-02'
insert into 表B select 'A',5,250,'2011-03-03'
insert into 表B select 'A',5,1000,'2011-04-04'
insert into 表B select 'B',10,120,'2011-05-05'
insert into 表B select 'C',5,130,'2011-06-06'
这样出来的结果就错了,因为
insert into 表B select 'A',3,100,'2011-01-01'
insert into 表B select 'A',4,200,'2011-02-02'
insert into 表B select 'A',5,250,'2011-03-03'
insert into 表B select 'A',5,1000,'2011-04-04'
店铺A只能按时间排序取前面10条
如
表B
店铺 销售数量 价格 日期
A 3 100 2011-01-01
A 4 200 2011-02-02
A 5 250 2011-03-03
A 5 300 2011-04-04
B 10 120 2011-05-05
C 5 130 2011-06-06得出来的结果应该是
店铺 销售数量 价格 日期
A 3 100 2011-01-01
A 4 200 2011-02-02
A 3 250 2011-03-03
B 10 120 2011-05-05
C 5 130 2011-06-06
取到前面销售数量和为10,之后的都排除掉
create table #A
(店铺 nvarchar(10), 调出数 int)
insert #A
select 'A',10 union all
select 'B',8 union all
select 'C',20 create table #B
(店铺 nvarchar(10),销售数量 int,价格 int,日期 datetime)
insert #B
select 'A',3 ,100 ,'2011-01-01' union all
select 'A',4 ,200,'2011-02-02' union all
select 'A',5 ,250,'2011-03-03' union all
select 'B',10 ,120,'2011-05-05' union all
select 'C',5 ,130,'2011-06-06';with T as(select b.店铺,(case when sum(销售数量)over(partition by b.店铺)>调出数 --假如店铺的销售数量总和大于调出数则按调出数来计算并且价格按日期的先后获取价格,如果小于则按销售量计算
then 调出数 else 销售数量 end)*价格 as 销售数
from #B as b join #A as a on a.店铺=b.店铺 )
select 店铺,sum(销售数) as 销售总和 from T group by 店铺;with T as(select b.店铺,(case when sum(销售数量)over(partition by b.店铺)>调出数
then 销售数量 else 调出数 end)*价格 as 销售数
from #B as b join #A as a on a.店铺=b.店铺 )
select 店铺,sum(销售数) as 销售总和 from T group by 店铺真不知道你想要哪一個的結果。。
(
select * from a,b where a.cusid=b.cusid
--select * from b
select * from (select
b.cusid,
b.price,
(case when (select sum(num) from b as tb
where tb.cusid=b.cusid and tb.createdate<=b.createdate)<=a.export then b.num --when (select sum(num) from b as tb
-- where tb.cusid=b.cusid and tb.createdate<=b.createdate)>a.export
-- --and (select sum(num) from b as tb where tb.cusid=b.cusid and tb.createdate<b.createdate)>a.export
--then a.export else a.export-(select isnull(sum(num),0) from b as tb
where tb.cusid=b.cusid and tb.createdate<b.createdate)
end
--else 0 end
) as total,
createdate
from a,b
where a.cusid=b.cusid) d where total>0
) as c group by cusid
create table #A
(店铺 nvarchar(10), 调出数 int)
insert #A
select 'A',10 union all
select 'B',8 union all
select 'C',20 create table #B
(店铺 nvarchar(10),销售数量 int,价格 int,日期 datetime)
insert #B
select 'A',3 ,100 ,'2011-01-01' union all
select 'A',4 ,200,'2011-02-02' union all
select 'A',5 ,250,'2011-03-03' union all
select 'B',10 ,120,'2011-05-05' union all
select 'C',5 ,130,'2011-06-06';with tempA as(select b.*,调出数,
(select sum(销售数量) from #B as b1 where b1.店铺=b.店铺 and b1.日期<=b.日期) as subsum
from #B as b join #A as a on a.店铺=b.店铺 )
, tempB as (select *,case when subsum<调出数 then 销售数量
when subsum-调出数>销售数量 then 0
when subsum-调出数<销售数量 then 销售数量-(subsum-调出数) end as gid
from tempA)
select 店铺,sum(gid*价格) as 销售总和 from tempB group by 店铺店铺 销售总和
---------- -----------
A 1850
B 960
C 650(3 row(s) affected)oh~~I'm sorry~~
select 'A', 10 union all
select 'B', 8 union all
select 'C', 20
create table B(店铺 varchar(10),销售数量 int,价格 int,日期 datetime)
insert into B select 'A',3,100,'2011-01-01'
insert into B select 'A',4,200,'2011-02-02'
insert into B select 'A',5,250,'2011-03-03'
insert into B select 'A',5,1000,'2011-04-04'
insert into B select 'B',10,120,'2011-05-05'
insert into B select 'C',5,130,'2011-06-06'
select 店铺,sum(价格*对应数量) as 销售总和 from
(
select b.店铺,b.价格,
对应数量=case when (select isnull(sum(销售数量),0) from b as tb
where tb.店铺=b.店铺 and tb.日期<=b.日期)<=a.调出数
then b.销售数量 when (select sum(销售数量) from b as tb
where tb.店铺=b.店铺 and tb.日期<=b.日期)>a.调出数 and
(select sum(销售数量) from b as tb where tb.店铺=b.店铺 and tb.日期<b.日期)>a.调出数
then 0 else a.调出数-(select isnull(sum(销售数量),0) from b as tb
where tb.店铺=b.店铺 and tb.日期<b.日期)
end
from a,b
where a.店铺=b.店铺
) as c group by 店铺drop table a,b
/*
店铺 销售总和
---------- -----------
A 1850
B 960
C 650
*/
set nocount on
declare @表A table (店铺 varchar(1),调出数 int)
insert into @表A
select 'A',10 union all
select 'B',8 union all
select 'C',20declare @表B table (店铺 varchar(1),销售数量 int,价格 int,日期 datetime)
insert into @表B
select 'A',3,100,'2011-01-01' union all
select 'A',4,200,'2011-02-02' union all
select 'A',5,250,'2011-03-03' union all
select 'B',10,120,'2011-05-05' union all
select 'C',5,130,'2011-06-06'select rn=identity(int),a.调出数,b.* into #t
from @表A a left join @表B b on a.店铺=b.店铺 order by b.日期
select 店铺,
销售总和=sum(case when 调出数>=[sum] then 销售数量*价格 when 调出数<[sum]
and [count]=1 then 调出数*价格 when 调出数<[sum] and [count]>1
then (销售数量-([sum]-调出数))*价格 end)
from (select *,[sum]=(select sum(销售数量) from #t where rn<=a.rn and 店铺=a.店铺),
[count]=(select count(销售数量) from #t where rn<=a.rn and 店铺=a.店铺) from #t a
)aa group by 店铺drop table #t
/*
店铺 销售总和
---- -----------
A 1850
B 960
C 650
*/
/*
如下:
表A
店铺 调出数
A 10
B 8
C 20 表B
店铺 销售数量 价格 日期
A 3 100 2011-01-01
A 4 200 2011-02-02
A 5 250 2011-03-03
B 10 120 2011-05-05
C 5 130 2011-06-06现在的想要的结果是:查出店铺的销售总和,条件是假如店铺的销售数量总和大于调出数则按调出数来计算并且价格按日期的先后获取价格,如果小于则按销售量计算,结果大概如下店铺 销售总和
A 3*100 + 4*200 + 3*250 价格为250的只取两个
B 8*250
C 5*130
*/
use ForTest
goif OBJECT_ID('A','u')is not null
drop table dbo.A
if OBJECT_ID('B','u')is not null
drop table dbo.B
gocreate table dbo.A(
ID int identity
constraint pk_A primary key(ID)
,[店铺] nvarchar(10)
,[调出数] int
)create table dbo.B(
ID int identity
constraint pk_B primary key(ID)
,[店铺] nvarchar(10)
,[销售量] int
,[价格] int
,[日期] datetime
)insert into dbo.A([店铺],[调出数])
select N'A',10
union all select N'B',8
union all select N'C',20
insert into dbo.B([店铺],[销售量],[价格],[日期])
select N'A',3,100,N'2011-01-01'
union all select N'A',4,200,N'2011-02-02'
union all select N'A',10,250,N'2011-03-03'
union all select N'A',10,250,N'2011-04-04'
union all select N'B',10,120,N'2011-05-05'
union all select N'C',8,130,N' 2011-06-06'
goselect
a1.[店铺]
,[销售量]=sum(case when c.[调出数]-b1.[销售总量]>0 then a1.[销售量]
else a1.[销售量]+c.[调出数]-b1.[销售总量]
end
)
,[销售总额]=sum(case when c.[调出数]-b1.[销售总量]>0 then a1.[销售量]*[价格]
else (a1.[销售量]+c.[调出数]-b1.[销售总量])*[价格]
end
)
from dbo.B a1
outer apply(
select [销售总量]=SUM([销售量]) from dbo.B
where ID<=a1.ID
and [店铺]=a1.[店铺]
group by [店铺]
) b1
inner join dbo.A c
on c.[店铺]=a1.[店铺]
and (c.[调出数]-b1.[销售总量]>=0
or a1.[销售量]+c.[调出数]-b1.[销售总量]>=0
)
group by a1.[店铺](3 行受影响)(6 行受影响)
店铺 销售量 销售总额
---------- ----------- -----------
A 10 1850
B 8 960
C 8 1040(3 行受影响)