22,210
社区成员
发帖
与我相关
我的任务
分享
select s.item,s.cost,t.cost from tb s,tb t
where s.ITEM =t.ITEM and s.costty=0 and t.costty=5
and abs(1.0*(s.cost-t.cost)/s.cost)>0.1
item cost cost
003 9 10
004 11 13
select item,sum((5-COSTTY)/5*COST) COST0,sum(COSTTY/5*COST) COST5 from tb group by item having max(cost)*1.0/min(cost)>1.1
create table tb(ITEM varchar(3), COSTTY int, COST int )
go
insert tb select '001' , 0 , 10
insert tb select '001' , 5 , 10
insert tb select '002' , 0 , 15
insert tb select '002' , 5 , 15
insert tb select '003' , 0 , 10
insert tb select '003' , 5 , 9
insert tb select '004' , 0 , 11
insert tb select '004' , 5 , 13
go
select item,sum((5-COSTTY)/5*COST) COST0,sum(COSTTY/5*COST) COST5 from tb group by item having max(cost)*1.0/min(cost)>1.1
select s.item,s.cost,t.cost from tb s,tb t
where s.ITEM =t.ITEM and s.costty=0 and t.costty=5
and abs(1.0*(s.cost-t.cost)/s.cost)>0.1
select s.item,s.cost,t.cost,abs(1.0*(s.cost-t.cost)/s.cost) as test1,abs(1.0*(s.cost-t.cost)/t.cost) as test2 from tb s,tb t
where s.ITEM =t.ITEM and s.costty=0 and t.costty=5
go
drop table tb
item COST0 COST5
---- ----------- -----------
003 10 9
004 11 13
item cost cost
---- ----------- -----------
004 11 13
item cost cost test1 test2
---- ----------- ----------- --------------------------------------- ---------------------------------------
001 10 10 0.000000000000 0.000000000000
002 15 15 0.000000000000 0.000000000000
003 10 9 0.100000000000 0.111111111111
004 11 13 0.181818181818 0.153846153846
隐式转化为 float型,否则为int型。
select item,min(cost) COST0,max(cost) COST5
from tb
group by item
having max(cost)*1.0/min(cost)>1.1
go
create table #tb(ITEM varchar(3), COSTTY int, COST int )
go
insert #tb select '001' , 0 , 10
insert #tb select '001' , 5 , 10
insert #tb select '002' , 0 , 15
insert #tb select '002' , 5 , 15
insert #tb select '003' , 0 , 9
insert #tb select '003' , 5 , 10
insert #tb select '004' , 0 , 11
insert #tb select '004' , 5 , 13
select * from #tb t where exists (select * from #tb where ITEM=t.ITEM and COST<t.COST)
ITEM COSTTY COST
---- ----------- -----------
003 5 10
004 5 13
(2 行受影响)
create table tb(ITEM varchar(3), COSTTY int, COST int )
go
insert tb select '001' , 0 , 10
insert tb select '001' , 5 , 10
insert tb select '002' , 0 , 15
insert tb select '002' , 5 , 15
insert tb select '003' , 0 , 9
insert tb select '003' , 5 , 10
insert tb select '004' , 0 , 11
insert tb select '004' , 5 , 13
go
select item,min(cost) COST0,max(cost) COST5 from tb group by item having max(cost)*1.0/min(cost)>1.1
go
drop table tb
/*
item COST0 COST5
---- ----------- -----------
003 9 10
004 11 13
*/
select item,min(cost) COST0,max(cost) COST5 from tb group by item having max(cost)*1.0/min(cost)>1.1