表F_sqprice
partid workcode sprice otime stime
9D650 D222 88 2008-9-1 2008-12-31
9D650 D222 77 2009-1-1 2009-12-31
9D650 Z111 79 2009-7-1 2009-12-31
… "
select Partid,min(isnull(sprice,0)) as sprice
from F_sprice
where F_sprice.Otime<='2009-08-22'
and F_sprice.Stime>'2009-08-22'
GROUP BY F_sprice.Partid"
结果是:
partid sprice
9D650 77
问题:
怎样得到这些结果的厂商信息,及启用停用时间
partid sprice workcode otime stime
9D650 77 D222 2009-1-1 2009-12-31
partid workcode sprice otime stime
9D650 D222 88 2008-9-1 2008-12-31
9D650 D222 77 2009-1-1 2009-12-31
9D650 Z111 79 2009-7-1 2009-12-31
… "
select Partid,min(isnull(sprice,0)) as sprice
from F_sprice
where F_sprice.Otime<='2009-08-22'
and F_sprice.Stime>'2009-08-22'
GROUP BY F_sprice.Partid"
结果是:
partid sprice
9D650 77
问题:
怎样得到这些结果的厂商信息,及启用停用时间
partid sprice workcode otime stime
9D650 77 D222 2009-1-1 2009-12-31
select a.Partid,a.sprice,b.workcode,b.otime,b.stime from (
select Partid,min(isnull(sprice,0)) as sprice
from F_sprice
where F_sprice.Otime <='2009-08-22'
and F_sprice.Stime>'2009-08-22'
GROUP BY F_sprice.Partid)a,F_sprice b
where a.Partid=b.Partid
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-09-21 17:19:42
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[F_sqprice]
if object_id('[F_sqprice]') is not null drop table [F_sqprice]
go
create table [F_sqprice]([partid] varchar(5),[workcode] varchar(4),[sprice] int,[otime] datetime,[stime] datetime)
insert [F_sqprice]
select '9D650','D222',88,'2008-9-1','2008-12-31' union all
select '9D650','D222',77,'2009-1-1','2009-12-31' union all
select '9D650','Z111',79,'2009-7-1','2009-12-31'
--------------开始查询--------------------------
select Partid,min(isnull(sprice,0)) as sprice ,min(sprice) as sprice,min(otime) as otime,min(stime) as stime
from [F_sqprice] a
where a.Otime <='2009-08-22'
and a.Stime>'2009-08-22'
GROUP BY a.Partid
----------------结果----------------------------
/* Partid sprice sprice otime stime
------ ----------- ----------- ----------------------- -----------------------
9D650 77 77 2009-01-01 00:00:00.000 2009-12-31 00:00:00.000(1 行受影响)
*/
if object_id('[F_sqprice]') is not null drop table [F_sqprice]
go
create table [F_sqprice]([partid] varchar(5),[workcode] varchar(4),[sprice] int,[otime] datetime,[stime] datetime)
insert [F_sqprice]
select '9D650','D222',88,'2008-9-1','2008-12-31' union all
select '9D650','D222',77,'2009-1-1','2009-12-31' union all
select '9D650','Z111',79,'2009-7-1','2009-12-31'
select * from [F_sqprice]
select *
from [F_sqprice] t
where Otime <='2009-08-22'
and Stime>'2008-08-22' and not exists(select 1 from [F_sqprice] where sprice<t.sprice )
/*
partid workcode sprice otime stime
------ -------- ----------- ----------------------- -----------------------
9D650 D222 77 2009-01-01 00:00:00.000 2009-12-31 00:00:00.000(1 行受影响)
*/
from [F_sqprice] t
where Otime <='2009-08-22'
and Stime>'2008-08-22' and not exists(select 1 from [F_sqprice] where sprice<t.sprice )顶