表:test (salbatnum,emp_id)是联合主键salbatnum(int) emp_id(int) name(varchar)
2720080601 11 傅鹏宇
2720080604 11 傅鹏宇
2720080707 11 傅鹏宇
2720080701 11 傅鹏宇
----------------->要求:查询结果2720080707
2720080604提示:本人用以下sql查出数据不完整,仅得出:2720080707 select max(salbatnum) from test where emp_id=11 and
(salbatnum like '27200806%' or salbatnum like '27200807%') group by salbatnum----请指教:
2720080601 11 傅鹏宇
2720080604 11 傅鹏宇
2720080707 11 傅鹏宇
2720080701 11 傅鹏宇
----------------->要求:查询结果2720080707
2720080604提示:本人用以下sql查出数据不完整,仅得出:2720080707 select max(salbatnum) from test where emp_id=11 and
(salbatnum like '27200806%' or salbatnum like '27200807%') group by salbatnum----请指教:
select left(salbatnum ,8),max(salbatnum) from test where emp_id=11 group by left(salbatnum ,8)
declare @test table([salbatnum] nvarchar(20),[emp_id] int,[name] nvarchar(3))
Insert @test
select 2720080601,11,N'傅鹏宇' union all
select 2720080604,11,N'傅鹏宇' union all
select 2720080707,11,N'傅鹏宇' union all
select 2720080701,11,N'傅鹏宇'
select max(salbatnum) as salbatnum from @test where [emp_id]=11 group by left(salbatnum ,8)
/*
salbatnum
--------------------
2720080604
2720080707*/
(salbatnum bigint ,emp_id int ,userName varchar(30))
insert into userInfo1select 2720080601,11,'傅鹏宇' union all
select 2720080604,11,'傅鹏宇' union all
select 2720080707,11,'傅鹏宇' union all
select 2720080701,11,'傅鹏宇'
select * from userInfo1
select max(salbatnum) from userInfo1 where emp_id=11 group by left(salbatnum ,8)//
2720080604
2720080707
(salbatnum bigint ,emp_id int ,userName varchar(30))
insert into userInfo1select 2720080601,11,'傅鹏宇' union all
select 2720080604,11,'傅鹏宇' union all
select 2720080707,11,'傅鹏宇' union all
select 2720080701,11,'傅鹏宇'
select * from userInfo1
select max(salbatnum) from userInfo1 where emp_id=11 group by left(salbatnum ,8)或者select max(salbatnum) from userInfo1 where emp_id=11 group by substring(salbatnum ,1,8)
set nocount on;
declare @T table([salbatnum] bigint,[emp_id] int,[name] nvarchar(3))
Insert @T
select 2720080601,11,N'傅鹏宇' union all
select 2720080604,11,N'傅鹏宇' union all
select 2720080707,11,N'傅鹏宇' union all
select 2720080701,11,N'傅鹏宇'Select
*
from
@T a
where
[salbatnum]=(select max([salbatnum]) from @T where checksum(left([salbatnum],8),[emp_id],[name])=checksum(left(a.[salbatnum],8),a.[emp_id],a.[name]))salbatnum emp_id name
-------------------- ----------- ----
2720080604 11 傅鹏宇
2720080707 11 傅鹏宇