34,590
社区成员
发帖
与我相关
我的任务
分享
create table tb(标识 int,行号 int,编号 varchar(10),组别 int)
insert into tb values(1 ,1 ,'S001', 1)
insert into tb values(2 ,2 ,'S001', 1)
insert into tb values(3 ,2 ,'S001', 2)
insert into tb values(4 ,2 ,'S001', 3)
insert into tb values(5 ,3 ,'S001', 1)
insert into tb values(6 ,1 ,'S002', 1)
go
select 标识,行号,编号,组别 from
(
select t.* , px = row_number() over(partition by 行号 order by 组别 desc , 标识) from tb t
) m
where px = 1
drop table tb
/*
标识 行号 编号 组别
----------- ----------- ---------- -----------
1 1 S001 1
4 2 S001 3
5 3 S001 1
(3 行受影响)
*/
create table tb(标识 int,行号 int,编号 varchar(10),组别 int)
insert into tb values(1 ,1 ,'S001', 1)
insert into tb values(2 ,2 ,'S001', 1)
insert into tb values(3 ,2 ,'S001', 2)
insert into tb values(4 ,2 ,'S001', 3)
insert into tb values(5 ,3 ,'S001', 1)
insert into tb values(6 ,1 ,'S002', 1)
go
select t.* from tb t where 组别 = (select max(组别) from tb where 行号 = t.行号) order by t.行号
select t.* from tb t where not exists (select 1 from tb where 行号 = t.行号 and 组别 > t.组别) order by t.行号
/*
标识 行号 编号 组别
----------- ----------- ---------- -----------
1 1 S001 1
6 1 S002 1
4 2 S001 3
5 3 S001 1
(所影响的行数为 4 行)
标识 行号 编号 组别
----------- ----------- ---------- -----------
1 1 S001 1
6 1 S002 1
4 2 S001 3
5 3 S001 1
(所影响的行数为 4 行)
*/
select m.* from
(
select t.* from tb t where 组别 = (select max(组别) from tb where 行号 = t.行号)
) m where 标识 = (select min(标识) from
(
select t.* from tb t where 组别 = (select max(组别) from tb where 行号 = t.行号)
) n where 行号 = m.行号
)
order by m.行号
select m.* from
(
select t.* from tb t where not exists (select 1 from tb where 行号 = t.行号 and 组别 > t.组别)
) m where 标识 = (select min(标识) from
(
select t.* from tb t where not exists (select 1 from tb where 行号 = t.行号 and 组别 > t.组别)
) n where 行号 = m.行号
)
order by m.行号
/*
标识 行号 编号 组别
----------- ----------- ---------- -----------
1 1 S001 1
4 2 S001 3
5 3 S001 1
(所影响的行数为 3 行)
标识 行号 编号 组别
----------- ----------- ---------- -----------
1 1 S001 1
4 2 S001 3
5 3 S001 1
(所影响的行数为 3 行)
*/
select 标识,行号,编号,组别 from
(
select t.* , px = (select count(1) from tb where 行号 = t.行号 and (组别 > t.组别 or (组别 = t.组别 and 标识 < t.标识)) ) + 1 from tb t
) m
where px = 1
/*
标识 行号 编号 组别
----------- ----------- ---------- -----------
1 1 S001 1
4 2 S001 3
5 3 S001 1
(所影响的行数为 3 行)
*/
drop table tb
--------------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date : 2010-04-25 11:25:27
-- Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
-- Blog : http://blog.csdn.net/htl258
--------------------------------------------------------------------------
--> 生成测试数据表:a
IF NOT OBJECT_ID('[a]') IS NULL
DROP TABLE [a]
GO
CREATE TABLE [a]([标识] INT,[行号] INT,[编号] NVARCHAR(10),[组别] INT)
INSERT [a]
SELECT 1,1,'S001',1 UNION ALL
SELECT 2,2,'S001',1 UNION ALL
SELECT 3,2,'S001',2 UNION ALL
SELECT 4,2,'S001',3 UNION ALL
SELECT 5,3,'S001',1 UNION ALL
SELECT 6,1,'S002',1
GO
--SELECT * FROM [a]
-->SQL查询如下:
select * from a t where not exists(select 1 from a where 行号=t.行号 and (组别>t.组别 or 组别=t.组别 and 标识<t.标识))
/*
标识 行号 编号 组别
----------- ----------- ---------- -----------
1 1 S001 1
4 2 S001 3
5 3 S001 1
(3 行受影响)
*/
create table tb(标识 int,行号 int,编号 varchar(10),组别 int)
insert into tb values(1 ,1 ,'S001', 1)
insert into tb values(2 ,2 ,'S001', 1)
insert into tb values(3 ,2 ,'S001', 2)
insert into tb values(4 ,2 ,'S001', 3)
insert into tb values(5 ,3 ,'S001', 1)
insert into tb values(6 ,1 ,'S002', 1)
go
select t.* from tb t where 组别 = (select max(组别) from tb where 行号 = t.行号) order by t.行号
select t.* from tb t where not exists (select 1 from tb where 行号 = t.行号 and 组别 > t.组别) order by t.行号
/*
标识 行号 编号 组别
----------- ----------- ---------- -----------
1 1 S001 1
6 1 S002 1
4 2 S001 3
5 3 S001 1
(所影响的行数为 4 行)
标识 行号 编号 组别
----------- ----------- ---------- -----------
1 1 S001 1
6 1 S002 1
4 2 S001 3
5 3 S001 1
(所影响的行数为 4 行)
*/
select m.* from
(
select t.* from tb t where 组别 = (select max(组别) from tb where 行号 = t.行号)
) m where 标识 = (select min(标识) from
(
select t.* from tb t where 组别 = (select max(组别) from tb where 行号 = t.行号)
) n where 行号 = m.行号
)
order by m.行号
select m.* from
(
select t.* from tb t where not exists (select 1 from tb where 行号 = t.行号 and 组别 > t.组别)
) m where 标识 = (select min(标识) from
(
select t.* from tb t where not exists (select 1 from tb where 行号 = t.行号 and 组别 > t.组别)
) n where 行号 = m.行号
)
order by m.行号
/*
标识 行号 编号 组别
----------- ----------- ---------- -----------
1 1 S001 1
4 2 S001 3
5 3 S001 1
(所影响的行数为 3 行)
标识 行号 编号 组别
----------- ----------- ---------- -----------
1 1 S001 1
4 2 S001 3
5 3 S001 1
(所影响的行数为 3 行)
*/
drop table tb
--上面的怎么变成黑白的了,郁闷...
select 标识,行号,编号,组别 from(select idd=row_number()over(partition by 行号 order by 组别 desc,行号),* from tb where 编号='S001')t where idd=1
create table tb(标识 int,行号 int,编号 varchar(10),组别 int)
insert into tb values(1 ,1 ,'S001', 1)
insert into tb values(2 ,2 ,'S001', 1)
insert into tb values(3 ,2 ,'S001', 2)
insert into tb values(4 ,2 ,'S001', 3)
insert into tb values(5 ,3 ,'S001', 1)
insert into tb values(6 ,1 ,'S002', 1)
go
select t.* from tb t where 组别 = (select max(组别) from tb where 行号 = t.行号) order by t.行号
select t.* from tb t where not exists (select 1 from tb where 行号 = t.行号 and 组别 > t.组别) order by t.行号
/*
标识 行号 编号 组别
----------- ----------- ---------- -----------
1 1 S001 1
6 1 S002 1
4 2 S001 3
5 3 S001 1
(所影响的行数为 4 行)
标识 行号 编号 组别
----------- ----------- ---------- -----------
1 1 S001 1
6 1 S002 1
4 2 S001 3
5 3 S001 1
(所影响的行数为 4 行)
*/
drop table tb
--------------------SQL Server数据格式化工具-------------------
---------------------------------------------------------------
-- DESIGNER :happycell188(喜喜)
-- QQ :584738179
-- Development Tool :Microsoft Visual C++ 6.0 C Language
-- FUNCTION :CONVERT DATA TO T-SQL
---------------------------------------------------------------
-- Microsoft SQL Server 2005
-- Developer Edition on Microsoft Windows XP [版本 5.1.2600]
---------------------------------------------------------------
---------------------------------------------------------------
use test
go
if object_id('test.dbo.tb') is not null drop table tb
-- 创建数据表
create table tb
(
标识 int,
行号 int,
编号 char(5),
组别 int
)
go
--插入测试数据
insert into tb select 1,1,'S001',1
union all select 2,2,'S001',1
union all select 3,2,'S001',2
union all select 4,2,'S001',3
union all select 5,3,'S001',1
union all select 6,1,'S002',1
go
--代码实现
select 标识,行号,编号,组别 from(
select idd=row_number()over(partition by 行号 order by 组别 desc,行号),* from tb where 编号='S001')t
where idd=1
/*测试结果
标识 行号 编号 组别
---------------------
1 1 S001 1
4 2 S001 3
5 3 S001 1
(3 行受影响)
*/
select * from tb t where not exists(select 1 from tb where 行号=t.行号 and 标识>t.标识)
select t.* from tb t where 组别 = (select max(组别) from tb where 行号 = t.行号)
select t.* from tb t where not exists (select 1 from tb where 行号 = t.行号 and 组别 > t.组别)
select * from tb t where not exists(select 1 from tb where 行号=t.行号 and 组别>t.组别)
select
*
from
tb t
where
组别=(select max(组别) from tb where 行号=t.行号)