之前问了一个分组查询的有关问题。还是这个，难度增加了点

2013-10-18

之前问了一个分组查询的问题。还是这个，难度增加了点vcPart_idvcBeginvcSupplier_id 091010D01120120401135

之前问了一个分组查询的问题。还是这个，难度增加了点

vcPart_id     vcBegin     vcSupplier_id
091010D011     20120401     1350
091010D011     20120701     1350
091010D011     20121001     1350
091010D011     20121101     1350
091010D011     20130101     1350
0911102100     20120401     1350
0911102100     20120701     1350
0911102109     20121001     1350
0911102109     20121101     1350
0911102109     20130101     1350
112120T031     20120401     1001
112120T031     20120701     1001
112120T031     20130101     1001
112120T032     20120801     1001
112120T032     20130101     1001
120000T150     20120401     1001
120000T150     20120701     1001
120000T150     20130101     1001
120000T160     20120401     1001
120000T160     20120701     1001

以上是表结构。
想要得到的结果是vcPart_id相同的数据中vcBegin最大的和第二大的数据集
既以下结果：
vcPart_id      vcBegin      vcSupplier_id
091010D011     20130101     1350
091010D011     20121101     1350
0911102100     20120701     1350
0911102100     20120401     1350
0911102109     20130101     1350
0911102109     20121101     1350
112120T031     20130101     1001
112120T031     20120701     1001
112120T032     20130101     1001
112120T032     20120801     1001
120000T150     20130101     1001
120000T150     20120701     1001
120000T160     20120701     1001
120000T160     20120401     1001
求指点。谢谢了
数据结构
[解决办法]
少了个DESC

----------------------------
-- Author  :DBA_Huangzj(發糞塗牆)
-- Date    :2013-10-17 11:37:35
-- 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]([vcPart_id] varchar(10),[vcBegin] datetime,[vcSupplier_id] int)
insert [huang]
select '091010D011','20120401',1350 union all
select '091010D011','20120701',1350 union all
select '091010D011','20121001',1350 union all
select '091010D011','20121101',1350 union all
select '091010D011','20130101',1350 union all
select '0911102100','20120401',1350 union all
select '0911102100','20120701',1350 union all
select '0911102109','20121001',1350 union all
select '0911102109','20121101',1350 union all
select '0911102109','20130101',1350 union all
select '112120T031','20120401',1001 union all
select '112120T031','20120701',1001 union all
select '112120T031','20130101',1001 union all
select '112120T032','20120801',1001 union all

select '112120T032','20130101',1001 union all
select '120000T150','20120401',1001 union all
select '120000T150','20120701',1001 union all
select '120000T150','20130101',1001 union all
select '120000T160','20120401',1001 union all
select '120000T160','20120701',1001
--------------开始查询--------------------------
SELECT vcPart_id , vcBegin , vcSupplier_id
FROM (
select *,ROW_NUMBER()OVER(PARTITION BY vcPart_id ORDER BY vcBegin DESC)id
from [huang])b
WHERE id<=2

----------------结果----------------------------
/*
vcPart_id vcBegin vcSupplier_id
---------- ----------------------- -------------
091010D011 2013-01-01 00:00:00.000 1350
091010D011 2012-11-01 00:00:00.000 1350
0911102100 2012-07-01 00:00:00.000 1350
0911102100 2012-04-01 00:00:00.000 1350
0911102109 2013-01-01 00:00:00.000 1350
0911102109 2012-11-01 00:00:00.000 1350
112120T031 2013-01-01 00:00:00.000 1001
112120T031 2012-07-01 00:00:00.000 1001
112120T032 2013-01-01 00:00:00.000 1001
112120T032 2012-08-01 00:00:00.000 1001
120000T150 2013-01-01 00:00:00.000 1001
120000T150 2012-07-01 00:00:00.000 1001
120000T160 2012-07-01 00:00:00.000 1001
120000T160 2012-04-01 00:00:00.000 1001
*/

[解决办法]

引用:

Quote: 引用:

修改了一下上面的，这下应该是对了哈：



if object_id('[huang]') is not null drop table [huang]
go 
create table [huang]([vcPart_id] varchar(10),[vcBegin] datetime,[vcSupplier_id] int)
insert [huang]
select '091010D011','20120401',1350 union all
select '091010D011','20120701',1350 union all
select '091010D011','20121001',1350 union all
select '091010D011','20121101',1350 union all
select '091010D011','20130101',1350 union all
select '0911102100','20120401',1350 union all
select '0911102100','20120701',1350 union all
select '0911102109','20121001',1350 union all
select '0911102109','20121101',1350 union all
select '0911102109','20130101',1350 union all
select '112120T031','20120401',1001 union all
select '112120T031','20120701',1001 union all
select '112120T031','20130101',1001 union all
select '112120T032','20120801',1001 union all
select '112120T032','20130101',1001 union all
select '120000T150','20120401',1001 union all
select '120000T150','20120701',1001 union all
select '120000T150','20130101',1001 union all
select '120000T160','20120401',1001 union all
select '120000T160','20120701',1001
--------------开始查询--------------------------
SELECT vcPart_id , vcBegin       ,          vcSupplier_id 
 FROM (
select *,ROW_NUMBER()OVER(PARTITION BY vcPart_id
                              ORDER BY [vcSupplier_id] desc)id
 from [huang])b
 WHERE id<=2
----------------结果----------------------------
/* 
vcPart_id  vcBegin                 vcSupplier_id
---------- ----------------------- -------------
091010D011 2012-04-01 00:00:00.000 1350
091010D011 2012-07-01 00:00:00.000 1350
0911102100 2012-04-01 00:00:00.000 1350
0911102100 2012-07-01 00:00:00.000 1350
0911102109 2012-10-01 00:00:00.000 1350
0911102109 2012-11-01 00:00:00.000 1350
112120T031 2012-04-01 00:00:00.000 1001
112120T031 2012-07-01 00:00:00.000 1001
112120T032 2012-08-01 00:00:00.000 1001
112120T032 2013-01-01 00:00:00.000 1001
120000T150 2012-04-01 00:00:00.000 1001
120000T150 2012-07-01 00:00:00.000 1001
120000T160 2012-04-01 00:00:00.000 1001
120000T160 2012-07-01 00:00:00.000 1001
*/

120000T150这个ID的vcBegin最大的应该是20130101.

修改了一下，刚才是引用上面的代码，跟着上面也犯了一个错误，现在修改了：

if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([vcPart_id] varchar(10),[vcBegin] datetime,[vcSupplier_id] int)
insert [huang]
select '091010D011','20120401',1350 union all
select '091010D011','20120701',1350 union all

select '091010D011','20121001',1350 union all
select '091010D011','20121101',1350 union all
select '091010D011','20130101',1350 union all
select '0911102100','20120401',1350 union all
select '0911102100','20120701',1350 union all
select '0911102109','20121001',1350 union all
select '0911102109','20121101',1350 union all
select '0911102109','20130101',1350 union all
select '112120T031','20120401',1001 union all
select '112120T031','20120701',1001 union all
select '112120T031','20130101',1001 union all
select '112120T032','20120801',1001 union all
select '112120T032','20130101',1001 union all
select '120000T150','20120401',1001 union all
select '120000T150','20120701',1001 union all
select '120000T150','20130101',1001 union all
select '120000T160','20120401',1001 union all
select '120000T160','20120701',1001

--------------开始查询--------------------------
SELECT vcPart_id , vcBegin,vcSupplier_id
FROM (
select *,ROW_NUMBER()OVER(PARTITION BY vcPart_id
ORDER BY [vcBegin] desc)id
from [huang])b
WHERE id<=2
/*
vcPart_id vcBegin vcSupplier_id
---------- ----------------------- -------------
091010D011 2013-01-01 00:00:00.000 1350
091010D011 2012-11-01 00:00:00.000 1350
0911102100 2012-07-01 00:00:00.000 1350
0911102100 2012-04-01 00:00:00.000 1350
0911102109 2013-01-01 00:00:00.000 1350
0911102109 2012-11-01 00:00:00.000 1350
112120T031 2013-01-01 00:00:00.000 1001
112120T031 2012-07-01 00:00:00.000 1001
112120T032 2013-01-01 00:00:00.000 1001
112120T032 2012-08-01 00:00:00.000 1001
120000T150 2013-01-01 00:00:00.000 1001
120000T150 2012-07-01 00:00:00.000 1001
120000T160 2012-07-01 00:00:00.000 1001
120000T160 2012-04-01 00:00:00.000 1001
*/

热点排行