这样的结果如何实现?大神快来呀
数据如下
表A:
MB001 MB002 MB003
TRP997 1 PMP1V-20-XX
TRP997 2 PMP1V-20-XX
TRP997 3 51154-XX
TRP997 4 51154-XX
TRB999 1 10-61292-X
TRB999 2 10-61292-X
TRB999 3 940732
TRB999 4 961627
结果:
MB001 MB002 MB003
TRP997 1 PMP1V-20-XX
TRP997 2 51154-XX
TRB999 1 10-61292-X
TRB999 2 940732
TRB999 3 961627
求大神解析
[解决办法]
CREATE TABLE #tab(MB001 VARCHAR(50),MB002 INT,MB003 VARCHAR(50))
INSERT INTO #tab
select 'TRP997', 1, 'PMP1V-20-XX' UNION ALL
select 'TRP997', 2, 'PMP1V-20-XX' UNION ALL
select 'TRP997', 3, '51154-XX' UNION ALL
select 'TRP997', 4, '51154-XX' UNION ALL
select 'TRB999', 1, '10-61292-X' UNION ALL
select 'TRB999', 2, '10-61292-X' UNION ALL
select 'TRB999', 3, '940732' UNION ALL
select 'TRB999', 4, '961627'
SELECT MB001,
ROW_NUMBER()OVER(PARTITION BY mb001 ORDER BY MB001)MB002,
MB003 FROM (
SELECT ROW_NUMBER()OVER(PARTITION BY mb003 ORDER BY MB001)id,* FROM #tab
) a WHERE id=1
ORDER BY a.MB001 desc
MB001 MB002 MB003
-------------------------------------------------- -------------------- --------------------------------------------------
TRP997 1 51154-XX
TRP997 2 PMP1V-20-XX
TRB999 1 10-61292-X
TRB999 2 940732
TRB999 3 961627
(5 行受影响)
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-12-19 12:40:08
-- 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: )
--
----------------------------------------------------------------
--> 测试数据:[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([MB001] varchar(6),[MB002] int,[MB003] varchar(11))
insert [A]
select 'TRP997',1,'PMP1V-20-XX' union all
select 'TRP997',2,'PMP1V-20-XX' union all
select 'TRP997',3,'51154-XX' union all
select 'TRP997',4,'51154-XX' union all
select 'TRB999',1,'10-61292-X' union all
select 'TRB999',2,'10-61292-X' union all
select 'TRB999',3,'940732' union all
select 'TRB999',4,'961627'
--------------开始查询--------------------------
select [MB001],ROW_NUMBER()OVER(PARTITION BY [MB001] ORDER BY [MB003])[MB002],[MB003]
from (SELECT DISTINCT [MB001],[MB003] FROM [A]) [A]
ORDER BY [MB001] DESC
----------------结果----------------------------
/*
MB001 MB002 MB003
------ -------------------- -----------
TRB999 1 10-61292-X
TRB999 2 940732
TRB999 3 961627
TRP997 1 51154-XX
TRP997 2 PMP1V-20-XX
*/
create table a(MB001 varchar(30), MB002 int, MB003 varchar(30)
)
insert into a
select 'TRP997', 1 ,'PMP1V-20-XX' union all
select 'TRP997', 2 ,'PMP1V-20-XX' union all
select 'TRP997', 3 ,'51154-XX' union all
select 'TRP997', 4 ,'51154-XX' union all
select 'TRB999', 1 ,'10-61292-X' union all
select 'TRB999', 2 ,'10-61292-X' union all
select 'TRB999', 3 ,'940732' union all
select 'TRB999', 4 ,'961627'
go
select MB001,
row_number() over(partition by MB001 ORDER BY MIN_MB002),
MB003
from
(
select *,
min(MB002) over(partition by MB001,MB003) as min_mb002,
ROW_NUMBER() over(PARTITION by MB001,MB003 order by @@servername) as rownum
from a
)t
where rownum = 1
order by MB001 desc
/*
MB001(无列名)MB003
TRP9971PMP1V-20-XX
TRP997251154-XX
TRB999110-61292-X
TRB9992940732
TRB9993961627
*/