A表 l_id l_name l_date l_rode
2200 朱先生 2007-06-28 sc153
2201 朱先生 2008-12-09 gc101
2202 朱先生 2009-05-26 wc28
2203 朱先生 2009-06-24 wc77
2204 朱先生 2009-12-04 sc448
2205 宗文辉 NULL gc170
2206 宗序梅 2009-05-12 sc219
2207 宗泽斌 2008-10-16 gc34
2208 邹鉴豪 2008-09-18 gc6
2209 邹先生 2009-05-08 wc111
2210 邹小艳 2009-04-01 gc84
2211 邹智宇 2009-12-07 gc245
2212 左明远先 2007-09-1 sc99根据 l_rode 来找出最后的代码 但是依据是根据 l_rode 里面的前两个字母来区分的 比如
sc最大的是448 gc最大的是245 ...... 可以写死 开头的只有(gc,wc,sc,bc) 但是有空的 而且还可以有重复的!我写的语句
2200 朱先生 2007-06-28 sc153
2201 朱先生 2008-12-09 gc101
2202 朱先生 2009-05-26 wc28
2203 朱先生 2009-06-24 wc77
2204 朱先生 2009-12-04 sc448
2205 宗文辉 NULL gc170
2206 宗序梅 2009-05-12 sc219
2207 宗泽斌 2008-10-16 gc34
2208 邹鉴豪 2008-09-18 gc6
2209 邹先生 2009-05-08 wc111
2210 邹小艳 2009-04-01 gc84
2211 邹智宇 2009-12-07 gc245
2212 左明远先 2007-09-1 sc99根据 l_rode 来找出最后的代码 但是依据是根据 l_rode 里面的前两个字母来区分的 比如
sc最大的是448 gc最大的是245 ...... 可以写死 开头的只有(gc,wc,sc,bc) 但是有空的 而且还可以有重复的!我写的语句
*
from
tb t
where
right(l_rode,len(l_rode)-2))=(select max((l_rode,len(l_rode)-2)) from tb where left(l_rode,2)=left(t.l_rode,2))
drop table tb
Go
Create table tb([l_id] Datetime,[l_name] nvarchar(4),[l_date] Datetime,[l_rode] nvarchar(5))
Insert tb
select '2200',N'朱先生','2007-06-28',N'sc153' union all
select '2201',N'朱先生','2008-12-09',N'gc101' union all
select '2202',N'朱先生','2009-05-26',N'wc28' union all
select '2203',N'朱先生','2009-06-24',N'wc77' union all
select '2204',N'朱先生','2009-12-04',N'sc448' union all
select '2205',N'宗文辉',null,N'gc170' union all
select '2206',N'宗序梅','2009-05-12',N'sc219' union all
select '2207',N'宗泽斌','2008-10-16',N'gc34' union all
select '2208',N'邹鉴豪','2008-09-18',N'gc6' union all
select '2209',N'邹先生','2009-05-08',N'wc111' union all
select '2210',N'邹小艳','2009-04-01',N'gc84' union all
select '2211',N'邹智宇','2009-12-07',N'gc245' union all
select '2212',N'左明远先','2007-09-1',N'sc99'
Go
Select * from tb t
where not exists(select 1 from tb where left([l_rode],patindex('%[0-9]%',[l_rode] )-1)=
left(t.[l_rode],patindex('%[0-9]%',t.[l_rode] )-1) and [l_id]>t.[l_id])
/*
l_id l_name l_date l_rode
----------------------- ------ ----------------------- ------
2209-01-01 00:00:00.000 邹先生 2009-05-08 00:00:00.000 wc111
2211-01-01 00:00:00.000 邹智宇 2009-12-07 00:00:00.000 gc245
2212-01-01 00:00:00.000 左明远先 2007-09-01 00:00:00.000 sc99(3 個資料列受到影響)
*/
-- Author :SQL77(只为思齐老)
-- Date :2010-01-26 14:10:30
-- Version:
-- Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
-- Aug 6 2000 00:57:48
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:#TB
if object_id('tempdb.dbo.#TB') is not null drop table #TB
go
create table #TB([l_id] int,[l_name] varchar(8),[l_date] datetime,[l_rode] varchar(5))
insert #TB
select 2200,'朱先生','2007-06-28','sc153' union all
select 2201,'朱先生','2008-12-09','gc101' union all
select 2202,'朱先生','2009-05-26','wc28' union all
select 2203,'朱先生','2009-06-24','wc77' union all
select 2204,'朱先生','2009-12-04','sc448' union all
select 2205,'宗文辉',null,'gc170' union all
select 2206,'宗序梅','2009-05-12','sc219' union all
select 2207,'宗泽斌','2008-10-16','gc34' union all
select 2208,'邹鉴豪','2008-09-18','gc6' union all
select 2209,'邹先生','2009-05-08','wc111' union all
select 2210,'邹小艳','2009-04-01','gc84' union all
select 2211,'邹智宇','2009-12-07','gc245' union all
select 2212,'左明远先','2007-09-1','sc99'
--------------开始查询--------------------------select * from #TB T WHERE LEFT(l_rode,2) IN('gc','wc','sc','bc')
AND NOT EXISTS(SELECT 1 FROM #TB
WHERE LEFT(l_rode,2) IN('gc','wc','sc','bc') AND l_name=T.l_name
AND CONVERT(INT,
RIGHT(l_rode,LEN(l_rode)-2)
)>CONVERT(INT,
RIGHT(T.l_rode,LEN(T.l_rode)-2)
))
----------------结果----------------------------
/* (所影响的行数为 13 行)l_id l_name l_date l_rode
----------- -------- ------------------------------------------------------ ------
2204 朱先生 2009-12-04 00:00:00.000 sc448
2205 宗文辉 NULL gc170
2206 宗序梅 2009-05-12 00:00:00.000 sc219
2207 宗泽斌 2008-10-16 00:00:00.000 gc34
2208 邹鉴豪 2008-09-18 00:00:00.000 gc6
2209 邹先生 2009-05-08 00:00:00.000 wc111
2210 邹小艳 2009-04-01 00:00:00.000 gc84
2211 邹智宇 2009-12-07 00:00:00.000 gc245
2212 左明远先 2007-09-01 00:00:00.000 sc99(所影响的行数为 9 行)
*/
这个sc最大的是 sc448啊 而你的是sc99
-- Author :SQL77(只为思齐老)
-- Date :2010-01-26 14:10:30
-- Version:
-- Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
-- Aug 6 2000 00:57:48
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:#TB
if object_id('tempdb.dbo.#TB') is not null drop table #TB
go
create table #TB([l_id] int,[l_name] varchar(8),[l_date] datetime,[l_rode] varchar(5))
insert #TB
select 2200,'朱先生','2007-06-28','sc153' union all
select 2201,'朱先生','2008-12-09','gc101' union all
select 2202,'朱先生','2009-05-26','wc28' union all
select 2203,'朱先生','2009-06-24','wc77' union all
select 2204,'朱先生','2009-12-04','sc448' union all
select 2205,'宗文辉',null,'gc170' union all
select 2206,'宗序梅','2009-05-12','sc219' union all
select 2207,'宗泽斌','2008-10-16','gc34' union all
select 2208,'邹鉴豪','2008-09-18','gc6' union all
select 2209,'邹先生','2009-05-08','wc111' union all
select 2210,'邹小艳','2009-04-01','gc84' union all
select 2211,'邹智宇','2009-12-07','gc245' union all
select 2212,'左明远先','2007-09-1','sc99'
--------------开始查询--------------------------select * from #TB T WHERE LEFT(l_rode,2) IN('gc','wc','sc','bc')
AND NOT EXISTS(SELECT 1 FROM #TB
WHERE LEFT(l_rode,2) IN('gc','wc','sc','bc') AND LEFT(l_rode,2)=LEFT(T.l_rode,2)
AND CONVERT(INT,
RIGHT(l_rode,LEN(l_rode)-2)
)>CONVERT(INT,
RIGHT(T.l_rode,LEN(T.l_rode)-2)
))
----------------结果----------------------------
/* (所影响的行数为 13 行)l_id l_name l_date l_rode
----------- -------- ------------------------------------------------------ ------
2204 朱先生 2009-12-04 00:00:00.000 sc448
2209 邹先生 2009-05-08 00:00:00.000 wc111
2211 邹智宇 2009-12-07 00:00:00.000 gc245(所影响的行数为 3 行)
*/??????????
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2010-01-26 14:12:08
-- 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)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([l_id] int,[l_name] varchar(8),[l_date] datetime,[l_rode] varchar(5))
insert [tb]
select 2200,'朱先生','2007-06-28','sc153' union all
select 2201,'朱先生','2008-12-09','gc101' union all
select 2202,'朱先生','2009-05-26','wc28' union all
select 2203,'朱先生','2009-06-24','wc77' union all
select 2204,'朱先生','2009-12-04','sc448' union all
select 2205,'宗文辉',null,'gc170' union all
select 2206,'宗序梅','2009-05-12','sc219' union all
select 2207,'宗泽斌','2008-10-16','gc34' union all
select 2208,'邹鉴豪','2008-09-18','gc6' union all
select 2209,'邹先生','2009-05-08','wc111' union all
select 2210,'邹小艳','2009-04-01','gc84' union all
select 2211,'邹智宇','2009-12-07','gc245' union all
select 2212,'左明远先','2007-09-1','sc99'
--------------开始查询--------------------------
select
*
from
tb t
where
right(l_rode,len(l_rode)-2)=(select max(cast(right(l_rode,len(l_rode)-2) as int)) from tb where left(l_rode,2)=left(t.l_rode,2))----------------结果----------------------------
/* l_id l_name l_date l_rode
----------- -------- ----------------------- ------
2204 朱先生 2009-12-04 00:00:00.000 sc448
2209 邹先生 2009-05-08 00:00:00.000 wc111
2211 邹智宇 2009-12-07 00:00:00.000 gc245(3 行受影响)*/
if object_id('[tb]') is not null drop table [tb]
create table [tb]([l_id] int,[l_name] varchar(8),[l_date] datetime,[l_rode] varchar(5))
go
insert [tb]
select 2200,'朱先生','2007-06-28','sc153' union all
select 2201,'朱先生','2008-12-09','gc101' union all
select 2202,'朱先生','2009-05-26','wc28' union all
select 2203,'朱先生','2009-06-24','wc77' union all
select 2204,'朱先生','2009-12-04','sc448' union all
select 2205,'宗文辉',null,'gc170' union all
select 2206,'宗序梅','2009-05-12','sc219' union all
select 2207,'宗泽斌','2008-10-16','gc34' union all
select 2208,'邹鉴豪','2008-09-18','gc6' union all
select 2209,'邹先生','2009-05-08','wc111' union all
select 2210,'邹小艳','2009-04-01','gc84' union all
select 2211,'邹智宇','2009-12-07','gc245' union all
select 2212,'左明远先','2007-09-1','sc99'select * from [tb] t
where l_id = (select top 1 l_id from [tb] where l_name=t.l_name
order by cast(stuff(l_rode,1,2,'') as int ))---------------------------
2202 朱先生 2009-05-26 00:00:00.000 wc28
2205 宗文辉 NULL gc170
2206 宗序梅 2009-05-12 00:00:00.000 sc219
2207 宗泽斌 2008-10-16 00:00:00.000 gc34
2208 邹鉴豪 2008-09-18 00:00:00.000 gc6
2209 邹先生 2009-05-08 00:00:00.000 wc111
2210 邹小艳 2009-04-01 00:00:00.000 gc84
2211 邹智宇 2009-12-07 00:00:00.000 gc245
2212 左明远先 2007-09-01 00:00:00.000 sc99
select * from [tb] t
where l_id = (select top 1 l_id from [tb] where l_name=t.l_name
order by cast(stuff(l_rode,1,2,'') as int ) desc)
(
select Max(cast(substring([l_rode],3,100) as int)) as [MaxNum],substring([l_rode],0,3) as [NO] from #TB group by substring([l_rode],0,3)
) as b on a.l_rode=(b.[NO]+cast(b.[MaxNum] as nvarchar(max)))