//建表
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Shop]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Shop]
GOCREATE TABLE [dbo].[Shop] (
[Year] [varchar] (4) COLLATE Chinese_PRC_CI_AS NULL ,
[Month] [varchar] (2) COLLATE Chinese_PRC_CI_AS NULL ,
[CustomerCode] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[MaterialCode] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[StockQty] [int] NULL ,
[Type] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Qty] [int] NULL ,
[Amount] [decimal](10, 1) NULL ,
[ShopCode] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO//测试数据
insert into [dbo].[shop]( [Year],[Month],[CustomerCode],[MaterialCode],[StockQty],[Type],[Qty],[Amount],[ShopCode])
values('2008' , '6' ,'XJ0032', 'C182W222003', 26, 'Shop', 22 ,1656.0 ,'SXJ236' )
insert into [dbo].[shop]( [Year],[Month],[CustomerCode],[MaterialCode],[StockQty],[Type],[Qty],[Amount],[ShopCode])
values('2009' , '5' ,'XJ0032', 'C182W222003', 26, 'Shop', 24 ,2656.0 ,'SXJ236' )
insert into [dbo].[shop]( [Year],[Month],[CustomerCode],[MaterialCode],[StockQty],[Type],[Qty],[Amount],[ShopCode])
values('2008' , '8' ,'XJ0032', 'C182W222003', 26, 'Shop', 25 ,3656.0 ,'SXJ236' )
insert into [dbo].[shop]( [Year],[Month],[CustomerCode],[MaterialCode],[StockQty],[Type],[Qty],[Amount],[ShopCode])
values('2007' , '3' ,'XJ0032', 'C182W222003', 26, 'Shop', 26 ,4656.0 ,'SXJ236' )//要求实现
如果本月大于等于6月分的话就从把六月份至本月的数据累加,
如果本月小于6月份,就从上年的五月至今年本月的数据累加。
要求就把[Qty],[Amount]这两列累加就可以了
怎样在sqlserver中写这个条件?
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Shop]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Shop]
GOCREATE TABLE [dbo].[Shop] (
[Year] [varchar] (4) COLLATE Chinese_PRC_CI_AS NULL ,
[Month] [varchar] (2) COLLATE Chinese_PRC_CI_AS NULL ,
[CustomerCode] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[MaterialCode] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[StockQty] [int] NULL ,
[Type] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Qty] [int] NULL ,
[Amount] [decimal](10, 1) NULL ,
[ShopCode] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO//测试数据
insert into [dbo].[shop]( [Year],[Month],[CustomerCode],[MaterialCode],[StockQty],[Type],[Qty],[Amount],[ShopCode])
values('2008' , '6' ,'XJ0032', 'C182W222003', 26, 'Shop', 22 ,1656.0 ,'SXJ236' )
insert into [dbo].[shop]( [Year],[Month],[CustomerCode],[MaterialCode],[StockQty],[Type],[Qty],[Amount],[ShopCode])
values('2009' , '5' ,'XJ0032', 'C182W222003', 26, 'Shop', 24 ,2656.0 ,'SXJ236' )
insert into [dbo].[shop]( [Year],[Month],[CustomerCode],[MaterialCode],[StockQty],[Type],[Qty],[Amount],[ShopCode])
values('2008' , '8' ,'XJ0032', 'C182W222003', 26, 'Shop', 25 ,3656.0 ,'SXJ236' )
insert into [dbo].[shop]( [Year],[Month],[CustomerCode],[MaterialCode],[StockQty],[Type],[Qty],[Amount],[ShopCode])
values('2007' , '3' ,'XJ0032', 'C182W222003', 26, 'Shop', 26 ,4656.0 ,'SXJ236' )//要求实现
如果本月大于等于6月分的话就从把六月份至本月的数据累加,
如果本月小于6月份,就从上年的五月至今年本月的数据累加。
要求就把[Qty],[Amount]这两列累加就可以了
怎样在sqlserver中写这个条件?
CREATE TABLE [dbo].[#Shop] (
[Year] [varchar] (4) COLLATE Chinese_PRC_CI_AS NULL ,
[Month] [varchar] (2) COLLATE Chinese_PRC_CI_AS NULL ,
[CustomerCode] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[MaterialCode] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[StockQty] [int] NULL ,
[Type] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Qty] [int] NULL ,
[Amount] [decimal](10, 1) NULL ,
[ShopCode] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO --//测试数据
insert into [dbo].[#Shop]( [Year],[Month],[CustomerCode],[MaterialCode],[StockQty],[Type],[Qty],[Amount],[ShopCode])
values('2008' , '6' ,'XJ0032', 'C182W222003', 26, 'Shop', 22 ,1656.0 ,'SXJ236' )
insert into [dbo].[#Shop]( [Year],[Month],[CustomerCode],[MaterialCode],[StockQty],[Type],[Qty],[Amount],[ShopCode])
values('2009' , '5' ,'XJ0032', 'C182W222003', 26, 'Shop', 24 ,2656.0 ,'SXJ236' )
insert into [dbo].[#Shop]( [Year],[Month],[CustomerCode],[MaterialCode],[StockQty],[Type],[Qty],[Amount],[ShopCode])
values('2008' , '8' ,'XJ0032', 'C182W222003', 26, 'Shop', 25 ,3656.0 ,'SXJ236' )
insert into [dbo].[#Shop]( [Year],[Month],[CustomerCode],[MaterialCode],[StockQty],[Type],[Qty],[Amount],[ShopCode])
values('2007' , '3' ,'XJ0032', 'C182W222003', 26, 'Shop', 26 ,4656.0 ,'SXJ236' )
insert into [dbo].[#Shop]( [Year],[Month],[CustomerCode],[MaterialCode],[StockQty],[Type],[Qty],[Amount],[ShopCode])
values('2009' , '8' ,'XJ0032', 'C182W222003', 26, 'Shop', 26 ,4656.0 ,'SXJ236' )
insert into [dbo].[#Shop]( [Year],[Month],[CustomerCode],[MaterialCode],[StockQty],[Type],[Qty],[Amount],[ShopCode])
values('2009' , '10' ,'XJ0032', 'C182W222003', 26, 'Shop', 26 ,4656.0 ,'SXJ236' ) select *from #Shop 如果本月大于等于6月分的话就从把六月份至本月的数据累加,
如果本月小于6月份,就从上年的五月至今年本月的数据累加。
要求就把[Qty],[Amount]这两列累加就可以了
怎样在sqlserver中写这个条件?select sum(Qty),sum(Amount)
from #Shop
where
cast(Year+Month as varchar(10)) between case when month(getdate())>=6 then cast(year(getdate()) as varchar(10))+'6' else cast(Year-1 as varchar(10))+'5' end and
cast(year(getdate()) as varchar(10))+ cast(month(getdate()) as varchar(10))
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Shop]
GO CREATE TABLE [dbo].[Shop] (
[Year] [varchar] (4) COLLATE Chinese_PRC_CI_AS NULL ,
[Month] [varchar] (2) COLLATE Chinese_PRC_CI_AS NULL ,
[CustomerCode] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[MaterialCode] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[StockQty] [int] NULL ,
[Type] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Qty] [int] NULL ,
[Amount] [decimal](10, 1) NULL ,
[ShopCode] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO --测试数据
insert into [dbo].[shop]( [Year],[Month],[CustomerCode],[MaterialCode],[StockQty],[Type],[Qty],[Amount],[ShopCode])
values('2008' , '6' ,'XJ0032', 'C182W222003', 26, 'Shop', 22 ,1656.0 ,'SXJ236' )
insert into [dbo].[shop]( [Year],[Month],[CustomerCode],[MaterialCode],[StockQty],[Type],[Qty],[Amount],[ShopCode])
values('2009' , '5' ,'XJ0032', 'C182W222003', 26, 'Shop', 24 ,2656.0 ,'SXJ236' )
insert into [dbo].[shop]( [Year],[Month],[CustomerCode],[MaterialCode],[StockQty],[Type],[Qty],[Amount],[ShopCode])
values('2008' , '8' ,'XJ0032', 'C182W222003', 26, 'Shop', 25 ,3656.0 ,'SXJ236' )
insert into [dbo].[shop]( [Year],[Month],[CustomerCode],[MaterialCode],[StockQty],[Type],[Qty],[Amount],[ShopCode])
values('2007' , '3' ,'XJ0032', 'C182W222003', 26, 'Shop', 26 ,4656.0 ,'SXJ236' ) select * from [Shop]select sum([Qty]) as [Qty],
sum([Amount]) as [Amount]
from [Shop]
where (([year] = ltrim(year(getdate())) and [Month] >= '6')
or ([year] = case when month(getdate()) >= 6 then ltrim(year(getdate()))
else ltrim(year(getdate())-1) end
and [Month] >= case when month(getdate()) >= 6 then '6'
else '5' end))
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Shop]
GO CREATE TABLE [dbo].[Shop] (
[Year] [varchar] (4) COLLATE Chinese_PRC_CI_AS NULL ,
[Month] [varchar] (2) COLLATE Chinese_PRC_CI_AS NULL ,
[CustomerCode] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[MaterialCode] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[StockQty] [int] NULL ,
[Type] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Qty] [int] NULL ,
[Amount] [decimal](10, 1) NULL ,
[ShopCode] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO --//测试数据
insert into [dbo].[shop]( [Year],[Month],[CustomerCode],[MaterialCode],[StockQty],[Type],[Qty],[Amount],[ShopCode])
values('2008' , '6' ,'XJ0032', 'C182W222003', 26, 'Shop', 22 ,1656.0 ,'SXJ236' )
insert into [dbo].[shop]( [Year],[Month],[CustomerCode],[MaterialCode],[StockQty],[Type],[Qty],[Amount],[ShopCode])
values('2009' , '5' ,'XJ0032', 'C182W222003', 26, 'Shop', 24 ,2656.0 ,'SXJ236' )
insert into [dbo].[shop]( [Year],[Month],[CustomerCode],[MaterialCode],[StockQty],[Type],[Qty],[Amount],[ShopCode])
values('2008' , '8' ,'XJ0032', 'C182W222003', 26, 'Shop', 25 ,3656.0 ,'SXJ236' )
insert into [dbo].[shop]( [Year],[Month],[CustomerCode],[MaterialCode],[StockQty],[Type],[Qty],[Amount],[ShopCode])
values('2007' , '3' ,'XJ0032', 'C182W222003', 26, 'Shop', 26 ,4656.0 ,'SXJ236' ) select qty=sum(case
when (month(getdate())>=6 and year(getdate())=[year] and [month]<=6)
or (month(getdate())<6 and (year(getdate())=[year]-1 and [month]>=5) or (year(getdate())=[year] and [month]<=month(getdate())))
then qty else 0 end) ,
Amount=sum(case
when (month(getdate())>=6 and year(getdate())=[year] and [month]<=6)
or (month(getdate())<6 and (year(getdate())=[year]-1 and [month]>=5) or (year(getdate())=[year] and [month]<=month(getdate())))
then Amount else 0 end )
from shop t
qty Amount
----------- ---------------------------------------
24 2656.0(1 行受影响)
sum([Qty]) as [Qty],
sum([Amount]) as [Amount]
from
[Shop]
where
([year] = ltrim(year(getdate())) and [Month] >= '6'
or
([year] = case when month(getdate()) >= 6 then ltrim(year(getdate())) else ltrim(year(getdate())-1) end
and
[Month] >= case when month(getdate()) >= 6 then '6'
else '5' end))
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Shop]
GO CREATE TABLE [dbo].[Shop] (
[Year] [varchar] (4) COLLATE Chinese_PRC_CI_AS NULL ,
[Month] [varchar] (2) COLLATE Chinese_PRC_CI_AS NULL ,
[CustomerCode] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[MaterialCode] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[StockQty] [int] NULL ,
[Type] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Qty] [int] NULL ,
[Amount] [decimal](10, 1) NULL ,
[ShopCode] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO --测试数据
insert into [dbo].[shop]( [Year],[Month],[CustomerCode],[MaterialCode],[StockQty],[Type],[Qty],[Amount],[ShopCode])
values('2008' , '6' ,'XJ0032', 'C182W222003', 26, 'Shop', 22 ,1656.0 ,'SXJ236' )
insert into [dbo].[shop]( [Year],[Month],[CustomerCode],[MaterialCode],[StockQty],[Type],[Qty],[Amount],[ShopCode])
values('2009' , '5' ,'XJ0032', 'C182W222003', 26, 'Shop', 24 ,2656.0 ,'SXJ236' )
insert into [dbo].[shop]( [Year],[Month],[CustomerCode],[MaterialCode],[StockQty],[Type],[Qty],[Amount],[ShopCode])
values('2008' , '8' ,'XJ0032', 'C182W222003', 26, 'Shop', 25 ,3656.0 ,'SXJ236' )
insert into [dbo].[shop]( [Year],[Month],[CustomerCode],[MaterialCode],[StockQty],[Type],[Qty],[Amount],[ShopCode])
values('2007' , '3' ,'XJ0032', 'C182W222003', 26, 'Shop', 26 ,4656.0 ,'SXJ236' ) select
sum(
case when
(month(getdate())>=6 and year(getdate())=[year] and [month]<=6)
or
(month(getdate())<6 and (year(getdate())=[year]-1 and [month]>=5)
or
(year(getdate())=[year]
and
[month]<=month(getdate())))
then
qty else 0 end) as qty,
sum(
case when
(month(getdate())>=6 and year(getdate())=[year] and [month]<=6)
or
(month(getdate())<6 and (year(getdate())=[year]-1 and [month]>=5) or (year(getdate())=[year] and [month]<=month(getdate())))
then
Amount else 0 end ) as Amount
from
shop t
/*qty Amount
----------- ---------------------------------------
24 2656.0(1 行受影响)*/