表结构如下:
productid productprice date
1 100 2011-1-1
1 95 2011-1-2
1 105 2011-1-3
2 200 2011-1-1
2 150 2011-1-2得出的结果应该是
productid
2请问该如何筛选
productid productprice date
1 100 2011-1-1
1 95 2011-1-2
1 105 2011-1-3
2 200 2011-1-1
2 150 2011-1-2得出的结果应该是
productid
2请问该如何筛选
case when
ltrim(isnull(b.productprice,0)*100.0/isnull(a.productprice,0))+'%'>'10%' then a.productid end
from
tb a,tb b
where
a.productid=b.productid
and
DATEDIFF(dd,a.date,b.date)=1
from tab a,tab b
where a.productid = b.productid
and a.date = dateadd(day,-1,b.date)
and a.productprice > b.productprice * 1.1
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-10-09 09:04:45
-- Verstion:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86)
-- Apr 22 2011 11:57:00
-- Copyright (c) Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([productid] int,[productprice] int,[date] datetime)
insert [tb]
select 1,100,'2011-1-1' union all
select 1,95,'2011-1-2' union all
select 1,105,'2011-1-3' union all
select 2,200,'2011-1-1' union all
select 2,150,'2011-1-2'
--------------开始查询--------------------------
select
*
from
(
select
case when
isnull(b.productprice,0)-isnull(a.productprice,0)*100.0/isnull(a.productprice,0)>10 then a.productid end as productid
from
tb a,tb b
where
a.productid=b.productid
and
DATEDIFF(dd,a.date,b.date)=1)t
where
productid is not null ----------------结果----------------------------
/* productid
-----------
2(1 行受影响)*/
INNER JOIN tblTest B ON A.productid=B.productid
WHERE A.productprice>B.productprice * 1.1 AND A.date < B.date
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-10-09 09:04:45
-- Verstion:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86)
-- Apr 22 2011 11:57:00
-- Copyright (c) Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([productid] int,[productprice] int,[date] datetime)
insert [tb]
select 1,100,'2011-1-1' union all
select 1,95,'2011-1-2' union all
select 1,105,'2011-1-3' union all
select 2,200,'2011-1-1' union all
select 2,150,'2011-1-2'
--------------开始查询--------------------------
select
distinct a.productid
from
(select id=row_number()over(order by date),* from tb) a,
(select id=row_number()over(order by date),* from tb) b
where
a.productid = b.productid
and
a.id=b.id-1
and
a.productprice > b.productprice * 1.1
----------------结果----------------------------
/* productid
-----------
2(1 行受影响)*/
from tab a,tab b
where a.productid = b.productid
and not exists (
select 1 from tab
where productid = b.productid
and date > a.date and date < b.date
)
and a.productprice > b.productprice * 1.1
distinct a.productid
from
(select id=row_number()over(order by date),* from tb) a,
(select id=row_number()over(order by date),* from tb) b
where
a.productid = b.productid
and
a.id=b.id-1
and
a.productprice > b.productprice * 1.1这个可能有问题,id不一定相连啊