多where条件查询

-- ???

create table tb
(
    工厂 varchar(10),
    库地 int,
    类型 varchar(10),
    属性 varchar(10),
    SKU varchar(10),
    批次 varchar(10),
    名称 varchar(10),
    入库时间 datetime,
    数量 int,
    重量 int,
    备注 varchar(10)
)
insert into tb
select 'BJ2006',399,'DK','RTE','651846','p102329','eeeee','2011-2-7',50,700,'' union all
select 'BJ2006',399,'DK','RTE','651846','p102329','eeeee','2011-2-7',30,600,'' union all
select 'BJ2007',411,'DK','RTE','646813','p102323','cccc','2011-1-10',50,500,'' union all
select 'BJ2114',422,'EO','CRE','218441','p102331','eeeee','2010-4-8',15,999,'' union all
select 'BJ2119',401,'RO','DFE','218981','p102329','eeeeeeee','2011-3-8',100,213,'' union all
select 'BJ2119',844,'RO','DFE','218981','p102329','eeeeeeee','2011-3-8',100,213,''
go

select N'HY' as [平台],
        (case when 工厂 = 'BJ2006' then 'PCC' when 工厂 <> 'BJ2006' and charindex('cc',名称) > 0 then 'DCC' else 'OBB' end) as[事业部],
        (case when 工厂 = 'BJ2004' then 'BJ2007' else 工厂 end) as 工厂,库地,SKU,批次,名称,
        (case when left(SKU,2) = '65' then N'主机' when left(SKU,2) = '64' then N'显示器' else N'其它' end) as [产品属性],
        入库时间,数量,
        datediff(dd,入库时间,getdate()) as [入库天数],
        (case when datediff(dd,入库时间,getdate()) between 30 and 60 then '30~60'
              when datediff(dd,入库时间,getdate()) between 30 and 60 then '60~90'
              else '90以上' end) as [库存天数]
from tb
where 工厂 in ('BJ2006','BJ2007','BJ2004') and datediff(dd,入库时间,getdate()) >= 30

drop table tb

 

转自园丁小三