现在有一个表TB
ZSH LB XH
0201121 01 3585214
0201122 01 3652145
0201123 01 3678214
0201152 01 6248612
0201141 02 6238415
0201165 02 6912542
0201777 02 2514521
0201324 01 2151125现在要根据LB,选出ZSH后3位最大的记录与最小的记录,请问我要怎么写,谢谢
ZSH LB XH
0201121 01 3585214
0201122 01 3652145
0201123 01 3678214
0201152 01 6248612
0201141 02 6238415
0201165 02 6912542
0201777 02 2514521
0201324 01 2151125现在要根据LB,选出ZSH后3位最大的记录与最小的记录,请问我要怎么写,谢谢
inner join
(
select LB, min(right(rtrim(ZSH),3)) as min_zsh,max(right(rtrim(ZSH),3)) as max_zsh
from TB
group by LB
) B
on A.LB=B.LB
and (right(rtrim(A.ZSH),3)=B.min_zsh Or right(rtrim(A.ZSH),3)=B.max_zsh )
drop table tB
Go
Create table tB([ZSH] nvarchar(7),[LB] nvarchar(2),[XH] int)
Insert tB
select N'0201121',N'01',3585214 union all
select N'0201122',N'01',3652145 union all
select N'0201123',N'01',3678214 union all
select N'0201152',N'01',6248612 union all
select N'0201141',N'02',6238415 union all
select N'0201165',N'02',6912542 union all
select N'0201777',N'02',2514521 union all
select N'0201324',N'01',2151125
Go
Select * from tb t
where
not exists(select * from tb where [LB]=t.[LB] and cast(right([ZSH],3)as int)>cast(right(t.[ZSH],3)as int))
or
not exists(select * from tb where [LB]=t.[LB] and cast(right([ZSH],3)as int)<cast(right(t.[ZSH],3)as int))
/*
ZSH LB XH
------- ---- -----------
0201121 01 3585214
0201141 02 6238415
0201777 02 2514521
0201324 01 2151125(4 個資料列受到影響)*/
drop table tB
Go
Create table tB([ZSH] nvarchar(7),[LB] nvarchar(2),[XH] int)
Insert tB
select N'0201121',N'01',3585214 union all
select N'0201122',N'01',3652145 union all
select N'0201123',N'01',3678214 union all
select N'0201152',N'01',6248612 union all
select N'0201141',N'02',6238415 union all
select N'0201165',N'02',6912542 union all
select N'0201777',N'02',2514521 union all
select N'0201324',N'01',2151125
Go
Select * from tb t
where
not exists(select * from tb where [LB]=t.[LB] and cast(right([ZSH],3)as int)>cast(right(t.[ZSH],3)as int))
or
not exists(select * from tb where [LB]=t.[LB] and cast(right([ZSH],3)as int)<cast(right(t.[ZSH],3)as int))
order by LB,cast(right([ZSH],3)as int)
/*
ZSH LB XH
------- ---- -----------
0201121 01 3585214
0201324 01 2151125
0201141 02 6238415
0201777 02 2514521(4 個資料列受到影響)
*/
declare @tb table([ZSH] varchar(7),[LB] varchar(2),[XH] int)
insert @tb
select '0201121','01',3585214 union all
select '0201122','01',3652145 union all
select '0201123','01',3678214 union all
select '0201152','01',6248612 union all
select '0201141','02',6238415 union all
select '0201165','02',6912542 union all
select '0201777','02',2514521 union all
select '0201324','01',2151125select * from @tb a where not exists(select 1 from @tb where [LB]=a.[LB] and right(ZSH,3)<right(a.ZSH,3))
union all
select * from @tb a where not exists(select 1 from @tb where [LB]=a.[LB] and right(ZSH,3)>right(a.ZSH,3))
order by lb
/*
ZSH LB XH
------- ---- -----------
0201121 01 3585214
0201324 01 2151125
0201141 02 6238415
0201777 02 2514521(4 行受影响)*/