declare @sdate datetime,@edate datetime
set @sdate = '2005-01-01'
set @edate = '2005-06-01'select
类型 = Type,
书名 = BookName,
入库渠道1 = sum(case when OutDate is null and Channel = 1 then 1 else 0 end),
入库渠道2 = sum(case when OutDate is null and Channel = 2 then 1 else 0 end) ,
入库渠道3 = sum(case when OutDate is null and Channel = 3 then 1 else 0 end) ,
出库渠道1 = sum(case when OutDate is not null and Channel = 1 then 1 else 0 end) ,
出库渠道2 = sum(case when OutDate is not null and Channel = 2 then 1 else 0 end) ,
出库渠道3 = sum(case when OutDate is not null and Channel = 3 then 1 else 0 end)
from
表
where
(InDate between @sdate and @edate and OutDate is null)
or
OutDate between @sdate and @edate
group by
Type,BookName
order by
Type,BookName
set @sdate = '2005-01-01'
set @edate = '2005-06-01'select
类型 = Type,
书名 = BookName,
入库渠道1 = sum(case when OutDate is null and Channel = 1 then 1 else 0 end),
入库渠道2 = sum(case when OutDate is null and Channel = 2 then 1 else 0 end) ,
入库渠道3 = sum(case when OutDate is null and Channel = 3 then 1 else 0 end) ,
出库渠道1 = sum(case when OutDate is not null and Channel = 1 then 1 else 0 end) ,
出库渠道2 = sum(case when OutDate is not null and Channel = 2 then 1 else 0 end) ,
出库渠道3 = sum(case when OutDate is not null and Channel = 3 then 1 else 0 end)
from
表
where
(InDate between @sdate and @edate and OutDate is null)
or
OutDate between @sdate and @edate
group by
Type,BookName
order by
Type,BookName
from datatable
where (inDate between @开始日期 and @结束日期 or
outdate between @开始日期 and @结束日期)
group by channel
set @sdate = '2005-01-01'
set @edate = '2005-06-01'select
类型 = Type,
书名 = BookName,
入库渠道1 = sum(case when (InDate between @sdate and @edate) and Channel = 1 then 1 else 0 end),
入库渠道2 = sum(case when (InDate between @sdate and @edate) and Channel = 2 then 1 else 0 end) ,
入库渠道3 = sum(case when (InDate between @sdate and @edate) and Channel = 3 then 1 else 0 end) ,
出库渠道1 = sum(case when (OutDate between @sdate and @edate) and Channel = 1 then 1 else 0 end) ,
出库渠道2 = sum(case when (OutDate between @sdate and @edate) and Channel = 2 then 1 else 0 end) ,
出库渠道3 = sum(case when (OutDate between @sdate and @edate) and Channel = 3 then 1 else 0 end)
from
表
group by
Type,BookName
order by
Type,BookName
swordmanli(乘风破浪会有时,直挂云帆济沧海--太白)的方法总的来说是对的,不过group by 的字段不对,应该group by book name
libin_ftsafe(子陌红尘) 兄的方法结果正确,可是少了一个where 过滤条件,会造成查询效率降低很多,加上where (inDate between @开始日期 and @结束日期) or (outdate between @开始日期 and @结束日期))就完美了
@StartDate varchar(10),
@EndDate varchar(10)
as
delcare @SQLString varchar(400)
set @SQLString ='select * from where convert(char(10),OutDate,120)
between '''+@StartDate+''' end+'''+@EndDate +''' group by Channel'
exec(@SQLString)
godelcare @StartDate varchar(10),@EndDate varchar(10)
select @StartDate ='2005-06-26',@EndDate='2005-06-28'
exec Select_NotChannel @StartDate ,EndDate