SQL-在一个字段中选择与另一字段中记录最高的不同记录
在我有一个像这样的表的场景中:
int id (PK)
int staff_id
int skill_id
bit mainskill
我只想为每个员工(由 Staff_id 表示)选择一条记录,列出他们的主要技能,如 mainskill 中的 (1) 表示。如果不存在主要技能,我想返回该员工的任何技能记录。例如:
id staff_id skill_id mainskill
1 1 24 1
2 1 55 0
3 1 7 0
4 4 24 0
5 4 18 0
6 6 3 0
7 6 18 1
查询应该返回:
id staff_id skill_id mainskill
1 1 24 1
4 4 24 0
7 6 18 1
我已经尝试了分组、DISTINCT 等的各种组合,但无法获得我想要的输出。任何帮助表示赞赏。
In a scenario where I have a table like so:
int id (PK)
int staff_id
int skill_id
bit mainskill
I want to select only ONE record for each staff member (represented by staff_id) listing their main skill as represented by a (1) in mainskill. If no main skill is present, I want to return any of the skill records for that staff member. For example:
id staff_id skill_id mainskill
1 1 24 1
2 1 55 0
3 1 7 0
4 4 24 0
5 4 18 0
6 6 3 0
7 6 18 1
The query should return:
id staff_id skill_id mainskill
1 1 24 1
4 4 24 0
7 6 18 1
I've tried various combinations of grouping, DISTINCT etc but can't get the output I'm after. Any help appreciated.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(4)
SQL Server 2005+,使用 CTE:
SQL Server 2005+,非 CTE 等效项:
两者都使用 ROW_NUMBER,仅自 SQL Server 2005 起可用。
SQL Server 2005+, Using CTE:
SQL Server 2005+, Non-CTE Equivalent:
Both use ROW_NUMBER, which is only available since SQL Server 2005.
如果您将 mainskill 连接到 Skillid 的前面,则 max 将为您提供 mainskill 或 mainskill 不存在的其他技能。
选择t.id,
t.staff_id,
t.skill_id,
t.主要技能,
来自表 t 哪里
CAST(t.mainskill As Varchar(5))+'-'+ Cast(t.skill_id as varchar(5))
在
(SELECT MAX(CAST(t.mainskill As Varchar(5))+'-'+ Cast(t.skill_id as varchar(5))) FROM
表 t 按 t.staff_id 分组)
If you concatenate the mainskill on the front of the skillid, max will give you either the mainskill or one other where mainskill doesn't exist.
SELECT t.id,
t.staff_id,
t.skill_id,
t.mainskill,
FROM TABLE t WHERE
CAST(t.mainskill As Varchar(5))+'-'+ Cast(t.skill_id as varchar(5))
IN
(SELECT MAX(CAST(t.mainskill As Varchar(5))+'-'+ Cast(t.skill_id as varchar(5))) FROM
TABLE t GROUP BY t.staff_id)
MySQL
- Ian
MySQL
- Ian
Oracle
怎么样:
(staff_skill 是你的表)
Oracle
how about:
(staff_skill is your table)