select
a.字段A,
a.字段B,
字段C = CASE WHEN EXISTS(SELECT 1 FROM 表 where left(字段A,2)=left(a.字段A,2) and right(字段A,2)>right(a.字段A,2))
THEN 'F' ELSE 'T' END
from
表 a
a.字段A,
a.字段B,
字段C = CASE WHEN EXISTS(SELECT 1 FROM 表 where left(字段A,2)=left(a.字段A,2) and right(字段A,2)>right(a.字段A,2))
THEN 'F' ELSE 'T' END
from
表 a
CREATE TABLE #T(字段A VARCHAR(10),字段B CHAR(1))
INSERT INTO #T SELECT '0101','x'
INSERT INTO #T SELECT '0102','x'
INSERT INTO #T SELECT '0103','x'
INSERT INTO #T SELECT '1001','x'
INSERT INTO #T SELECT '1002','x'
INSERT INTO #T SELECT '1501','x'
INSERT INTO #T SELECT '1502','x'
INSERT INTO #T SELECT '2001','x'
INSERT INTO #T SELECT '2002','x'
INSERT INTO #T SELECT '2501','x'
INSERT INTO #T SELECT '2502','x'
INSERT INTO #T SELECT '2503','x'--执行查询
select
a.字段A,
a.字段B,
字段C = CASE WHEN EXISTS(SELECT 1 FROM #T where left(字段A,2)=left(a.字段A,2) and right(字段A,2)>right(a.字段A,2))
THEN 'F' ELSE 'T' END
from
#T a
--输出结果
字段A 字段B 字段C
----- ----- -----
0101 x F
0102 x F
0103 x T
1001 x F
1002 x T
1501 x F
1502 x T
2001 x F
2002 x T
2501 x F
2502 x F
2503 x T
from 表 a left join (
select 字段A=max(字段a) from 表 group by left(字段a,2)
)b on a.字段A=b.字段A
CREATE TABLE #T(字段A VARCHAR(10),字段B CHAR(1))
INSERT INTO #T SELECT '0101','x'
INSERT INTO #T SELECT '0102','x'
INSERT INTO #T SELECT '0103','x'
INSERT INTO #T SELECT '1001','x'
INSERT INTO #T SELECT '1002','x'
INSERT INTO #T SELECT '1501','x'
INSERT INTO #T SELECT '1502','x'
INSERT INTO #T SELECT '2001','x'
INSERT INTO #T SELECT '2002','x'
INSERT INTO #T SELECT '2501','x'
INSERT INTO #T SELECT '2502','x'
INSERT INTO #T SELECT '2503','x'
go--查询
select a.*,字段C=CASE WHEN B.字段A IS NULL THEN 'F' ELSE 'T' END
from #T a left join (
select 字段A=max(字段a) from #T group by left(字段a,2)
)b on a.字段A=b.字段A
godrop table #T/*--结果
字段A 字段B 字段C
---------- ---- ----
0101 x F
0102 x F
0103 x T
1001 x F
1002 x T
1501 x F
1502 x T
2001 x F
2002 x T
2501 x F
2502 x F
2503 x T(所影响的行数为 12 行)
--*/
from t1,(select max(字段A) as 字段A from t1 group by substring(字段A,1,2)) as t2
where t1.字段A*=t2.字段A