if exists(select * from sysobjects where id=object_id('table1'))
drop table table1
goif exists(select * from sysobjects where id=object_id('table2'))
drop table table2
gocreate table table1
(
[商品名] varchar(20),
[交易日期] varchar(20),
[交易金额] money,
[累计交易额] money,
[交易流水号] varchar(20),
[是否送货] bit
)create table table2
(
[商品名] varchar(20),
[优惠期开始日] varchar(20),
[优惠期结束日] varchar(20)
)insert table1
select '苹果','20041102',20 ,30000,'2004110201',1 union all
select '苹果','20041102',30 ,450,'2004110202',1 union all
select '苹果','20041102',10 ,30010,'2004110203',0insert table2
select '苹果','20040101','20040123' union
select '橘子','20050101','20050103' union
select '栗子','20041231','20050101'--select * from table1
--select * from table2select t2.[商品名],
(select isnull(sum([交易金额]),0) from table1 where [商品名]=t2.[商品名] and [交易日期]=(cast((year(getdate())) as varchar)+cast((month(getdate())) as varchar)+cast((day(getdate())-1) as varchar))) [上日交易额],
isnull((select top 1 [累计交易额] from table1 where [商品名]=t2.[商品名] order by [交易流水号] desc ),0) [结束累计额],
(select isnull(sum([交易金额]),0) from table1 where [商品名]=t2.[商品名] and [是否送货]=1) [优惠期送货发生额额],
(select isnull(sum([交易金额]),0) from table1 where [商品名]=t2.[商品名]and [是否送货]=0) [优惠期不送货发生额]
from table2 t2drop table table1
drop table table2结果:商品名 上日交易额 结束累计额 优惠期送货发生额额 优惠期不送货发生额
--------- --------------------- --------------------- ---------------------
橘子 .0000 .0000 .0000 .0000
栗子 .0000 .0000 .0000 .0000
苹果 .0000 30010.0000 50.0000 10.0000
drop table table1
goif exists(select * from sysobjects where id=object_id('table2'))
drop table table2
gocreate table table1
(
[商品名] varchar(20),
[交易日期] varchar(20),
[交易金额] money,
[累计交易额] money,
[交易流水号] varchar(20),
[是否送货] bit
)create table table2
(
[商品名] varchar(20),
[优惠期开始日] varchar(20),
[优惠期结束日] varchar(20)
)insert table1
select '苹果','20041102',20 ,30000,'2004110201',1 union all
select '苹果','20041102',30 ,450,'2004110202',1 union all
select '苹果','20041102',10 ,30010,'2004110203',0insert table2
select '苹果','20040101','20040123' union
select '橘子','20050101','20050103' union
select '栗子','20041231','20050101'--select * from table1
--select * from table2select t2.[商品名],
(select isnull(sum([交易金额]),0) from table1 where [商品名]=t2.[商品名] and [交易日期]=(cast((year(getdate())) as varchar)+cast((month(getdate())) as varchar)+cast((day(getdate())-1) as varchar))) [上日交易额],
isnull((select top 1 [累计交易额] from table1 where [商品名]=t2.[商品名] order by [交易流水号] desc ),0) [结束累计额],
(select isnull(sum([交易金额]),0) from table1 where [商品名]=t2.[商品名] and [是否送货]=1) [优惠期送货发生额额],
(select isnull(sum([交易金额]),0) from table1 where [商品名]=t2.[商品名]and [是否送货]=0) [优惠期不送货发生额]
from table2 t2drop table table1
drop table table2结果:商品名 上日交易额 结束累计额 优惠期送货发生额额 优惠期不送货发生额
--------- --------------------- --------------------- ---------------------
橘子 .0000 .0000 .0000 .0000
栗子 .0000 .0000 .0000 .0000
苹果 .0000 30010.0000 50.0000 10.0000
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货