日期:2014-05-17 浏览次数:20735 次
----------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-10-16 08:00:20
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
-- Dec 28 2012 20:23:12
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([PartNumber] varchar(12),[first_date_on_BOM] datetime)
insert [huang]
select '1013617-00-A','2013-03-05' union all
select '1013617-00-B','2013-03-05' union all
select '1013617-00-C','2013-04-24'
--------------开始查询--------------------------
SELECT *
FROM dbo.huang
WHERE [PartNumber] IN (
select MIN([PartNumber])
from [huang])
----------------结果----------------------------
/*
PartNumber first_date_on_BOM
------------ -----------------------
1013617-00-A 2013-03-05 00:00:00.000
*/
create table dw
(PartNumber varchar(16),first_date_on_BOM varchar(12))
insert into dw
select '1013617-00-A','2013-03-05' union all
select '1013617-00-B','2013-03-05' union all
select '1013617-00-C','2013-04-24'
select top 1 PartNumber,first_date_on_BOM
from dw a
where not exists
(select 1 from dw b
where datediff(d,b.first_date_on_BOM,a.first_date_on_BOM)>0)
/*
PartNumber first_date_on_BOM
---------------- -----------------
1013617-00-A 2013-03-05
(1 row(s) affected)
*/
create table #tab([PartNumber] varchar(12),[first_date_on_BOM] datetime)
insert #tab
select '1013617-00-A','2013-03-05' union all
select '1013617-00-B','2013-03-05' union all
select '1013617-00-C','2013-04-24'
select PartNumber,first_date_on_BOM from (
select ROW_NUMBER()over(partition by first_date_on_BOM order by PartNumber asc)id,* from #tab