try select * from Personnel t where not exists(select 1 from Personnel where UserSN=t.UserSN and Section<t.Section)
select *,[Group]=(select top 1 [Group] from Personnel where usersn=a.usersn order by jobout desc), Section=(select top 1 Section from Personnel where usersn=a.usersn order by jobout desc), Jobin=(select top 1 Jobin from Personnel where usersn=a.usersn order by jobout desc), jobout=(select top 1 jobout from Personnel where usersn=a.usersn order by jobout desc) from (select DISTINCT UserSN from Personnel) a---->>>select UserSN,max([Group])[Group],max(Section) Section,max(Jobin) as Jobin,max(jobout)jobout from Personnel group by UserSN
select * from (select DISTINCT UserSN from Personnel) a貌似就等于查select USERSN from (select DISTINCT UserSN from Personnel) a其它的都是查USRESN相同时最大的值 最后就等同于上面的写法
最终结果的那个表有点乱Copy的问题 UserSN Group Section Jobin jobout 1 1 1 xxxx xxxx 1 1 2 xxxx xxxx 2 1 1 xxxx xxxx 2 1 3 xxxx xxxx 3 1 1 xxxx xxxx 4 1 4 xxxx xxxx 5 1 4 xxxx xxxx 5 1 1 xxxx xxxx我没有说清楚,怪我!! 我想区分出来 UserSN 和 Group 一至时,过滤掉Section的相同项,只取JobOut最大值的那一项显示 select UserSN,[Group],max(jobout) as jobout from Personnel a group by UserSN,[Group] order by jobout desc 这是小做修改的,后边不知道要怎么写了 只找出JobOut的最大值,以及过滤掉了没有用的 但我不知道怎么才能将最大值的这一行的其它列也对应的显示出来... 不知道要是加上max(Jobin) 那样取得JobOut 和 Jobin 会是一行的吗?至少我希望是同一行的!当然还要有对应的Section值
--> 生成测试数据表: [Personnel] IF OBJECT_ID('[Personnel]') IS NOT NULL DROP TABLE [Personnel] GO CREATE TABLE [Personnel] ([UserSN] [int],[Group] [int],[Section] [int],[Jobin] [nvarchar](10),[jobout] [nvarchar](10)) INSERT INTO [Personnel] SELECT '1','1','1','1','1' UNION ALL SELECT '1','1','2','2','2' UNION ALL SELECT '2','1','1','3','3' UNION ALL SELECT '2','1','3','4','4' UNION ALL SELECT '3','1','1','5','5' UNION ALL SELECT '4','1','4','6','6' UNION ALL SELECT '5','1','4','7','7' UNION ALL SELECT '5','1','1','8','8'--SELECT * FROM [Personnel]-->SQL查询如下:SELECT * FROM Personnel t WHERE jobout = ( SELECT MAX(jobout) FROM Personnel WHERE UserSN = t.UserSN AND [Group] = t.[Group] ) ORDER BY jobout DESC /* UserSN Group Section Jobin jobout ----------- ----------- ----------- ---------- ---------- 5 1 1 8 8 4 1 4 6 6 3 1 1 5 5 2 1 3 4 4 1 1 2 2 2(5 行受影响) */
我修改了下select UserSN,[Group],Section,Jobin,jobout from Personnel t where not exists(select 1 from Personnel where UserSN=t.UserSN and [Group]=t.[Group] and jobout<t.jobout) order by usersn 会是我需要的吗?
select * from Personnel a WHERE jobout=(SELECT max(jobout) FROM Personnel WHERE UserSN=A.UserSN AND [Group]=A.[Group])
select UserSN,[Group],max(jobout) as jobout from Personnel a group by UserSN,[Group]select * from Personnel a WHERE jobout=(SELECT max(jobout) FROM Personnel WHERE UserSN=A.UserSN AND [Group]=A.[Group])以上的二条命令查找出来的应是同一个思路的东西吧 我看的思路好像是一样的
谢谢2楼的抛砖引玉! 我需要的写出来了!select UserSN,[Group],Section,Jobin,jobout from Personnel t where not exists(select 1 from Personnel where UserSN=t.UserSN and [Group]=t.[Group] and jobout<t.jobout) order by usersn 只是不知道运行的效率上有没有更好的!! 但的确是得到了我想要的结果!!
select *
from Personnel t
where not exists(select 1 from Personnel where UserSN=t.UserSN and Section<t.Section)
Section=(select top 1 Section from Personnel where usersn=a.usersn order by jobout desc),
Jobin=(select top 1 Jobin from Personnel where usersn=a.usersn order by jobout desc),
jobout=(select top 1 jobout from Personnel where usersn=a.usersn order by jobout desc)
from
(select DISTINCT UserSN from Personnel) a---->>>select UserSN,max([Group])[Group],max(Section) Section,max(Jobin) as Jobin,max(jobout)jobout from Personnel group by UserSN
(select DISTINCT UserSN from Personnel) a貌似就等于查select USERSN from
(select DISTINCT UserSN from Personnel) a其它的都是查USRESN相同时最大的值
最后就等同于上面的写法
UserSN Group Section Jobin jobout
1 1 1 xxxx xxxx
1 1 2 xxxx xxxx
2 1 1 xxxx xxxx
2 1 3 xxxx xxxx
3 1 1 xxxx xxxx
4 1 4 xxxx xxxx
5 1 4 xxxx xxxx
5 1 1 xxxx xxxx我没有说清楚,怪我!!
我想区分出来 UserSN 和 Group 一至时,过滤掉Section的相同项,只取JobOut最大值的那一项显示
select UserSN,[Group],max(jobout) as jobout
from Personnel a
group by UserSN,[Group]
order by jobout desc
这是小做修改的,后边不知道要怎么写了
只找出JobOut的最大值,以及过滤掉了没有用的
但我不知道怎么才能将最大值的这一行的其它列也对应的显示出来...
不知道要是加上max(Jobin)
那样取得JobOut 和 Jobin 会是一行的吗?至少我希望是同一行的!当然还要有对应的Section值
IF OBJECT_ID('[Personnel]') IS NOT NULL
DROP TABLE [Personnel]
GO
CREATE TABLE [Personnel] ([UserSN] [int],[Group] [int],[Section] [int],[Jobin] [nvarchar](10),[jobout] [nvarchar](10))
INSERT INTO [Personnel]
SELECT '1','1','1','1','1' UNION ALL
SELECT '1','1','2','2','2' UNION ALL
SELECT '2','1','1','3','3' UNION ALL
SELECT '2','1','3','4','4' UNION ALL
SELECT '3','1','1','5','5' UNION ALL
SELECT '4','1','4','6','6' UNION ALL
SELECT '5','1','4','7','7' UNION ALL
SELECT '5','1','1','8','8'--SELECT * FROM [Personnel]-->SQL查询如下:SELECT *
FROM Personnel t
WHERE jobout = (
SELECT MAX(jobout)
FROM Personnel
WHERE UserSN = t.UserSN
AND [Group] = t.[Group]
)
ORDER BY jobout DESC
/*
UserSN Group Section Jobin jobout
----------- ----------- ----------- ---------- ----------
5 1 1 8 8
4 1 4 6 6
3 1 1 5 5
2 1 3 4 4
1 1 2 2 2(5 行受影响)
*/
我修改了下select UserSN,[Group],Section,Jobin,jobout
from Personnel t
where not exists(select 1 from Personnel where UserSN=t.UserSN and [Group]=t.[Group] and jobout<t.jobout)
order by usersn
会是我需要的吗?
from Personnel a WHERE jobout=(SELECT max(jobout) FROM Personnel WHERE UserSN=A.UserSN AND [Group]=A.[Group])
select UserSN,[Group],max(jobout) as jobout
from Personnel a
group by UserSN,[Group]select *
from Personnel a
WHERE jobout=(SELECT max(jobout) FROM Personnel WHERE UserSN=A.UserSN AND [Group]=A.[Group])以上的二条命令查找出来的应是同一个思路的东西吧
我看的思路好像是一样的
存在一个小问题,就是如果max(jobout)不是一条的话,查询出来的结果将会出多条
而我只想要一条记录,最大的一条,最大的任意一条或最大的(第二条件)的一条
我需要的写出来了!select UserSN,[Group],Section,Jobin,jobout
from Personnel t
where not exists(select 1 from Personnel where UserSN=t.UserSN and [Group]=t.[Group] and jobout<t.jobout)
order by usersn
只是不知道运行的效率上有没有更好的!!
但的确是得到了我想要的结果!!