依旧是排名问题

[IntoWindy]

比如说我这里是一个企业的招考，每个职位招考数不同，我必须标记出每个职位的所要的人数的3倍进入面试，即如果该职位计划数为3，则前9名进入下一轮测试，要特别标记出来，可以增加一个字段吗？
比如说名次排名如下。
表一：
科目一 科目二 职位代码 总分 名次
75 70 303 145 1
65 74 303 139 2
68 69.5 303 137.5 3
66.5 65 303 131.5 4
68.5 62.5 303 131 5
66.5 64.5 303 131 6
65.5 65.5 303 131 7
65.5 65 303 130.5 8
74 66 304 140 1
75.5 63.5 304 139 2
71.5 65.5 304 137 3
68 63.5 304 131.5 4
65.5 66 304 131.5 5
63 68.5 304 131.5 6
67.5 63 304 130.5 7

表二：
职位代码 计划数
303 2
304 1

要达到的效果表。
科目一 科目二 职位代码 总分 名次 是否进入第二轮考试
75 70 303 145 1 1
65 74 303 139 2 1
68 69.5 303 137.5 3 1
66.5 65 303 131.5 4 1
68.5 62.5 303 131 5 1
66.5 64.5 303 131 6 1
65.5 65.5 303 131 7
65.5 65 303 130.5 8
74 66 304 140 1 1
75.5 63.5 304 139 2 1
71.5 65.5 304 137 3 1
68 63.5 304 131.5 4
65.5 66 304 131.5 5
63 68.5 304 131.5 6
67.5 63 304 130.5 7

[ai_li7758521]

select a.*,[in]=(case when a.名次<=b.计划数*3 then '1' else '')
from tb1 a join tb2 b 
	on a.职位代码=b.职位代码

[ai_li7758521]

改下：

select a.*,[in]=(case when a.名次<=b.计划数*3 then '1' else '' end)
from tb1 a join tb2 b 
	on a.职位代码=b.职位代码

[sdhdy]

select * ,是否进入第二轮考试=case when 名次<=计划数*3 then '1' else '' end 
from t1,t2 where t1.职位代码=t2.职位代码

[--小F--]

--就和楼上的一样 用一个JOIN搞定
select a.*,[是否进入第二轮考试]=(case when a.名次<=b.计划数*3 then '1' else '' end)
from tb1 a join tb2 b 
    on a.职位代码=b.职位代码

[sdhdy]

select t1.* ,是否进入第二轮考试=case when 名次<=计划数*3 then '1' else '' end 
from t1,t2 where t1.职位代码=t2.职位代码

[张家可]

declare @t1 table(科目1 float,科目2 float,职位代码 int,分数 float,名次 int)
insert @t1
select 75,    70,    303,  145,    1 union all
select 65,    74,    303,  139 ,   2 union all
select 68,    69.5 ,   303,  137.5,  3 union all
select 66.5,  65 ,   303 , 131.5 , 4 union all
select 68.5,  62.5 ,   303,  131,    5 union all
select 66.5,  64.5 ,   303,  131,    6 union all
select 65.5 , 65.5 ,   303,  131,    7 union all
select 65.5,  65 ,     303 , 130.5 , 8 union all
select 74 ,   66 ,     304,  140 ,   1 union all
select 75.5,  63.5 ,   304,  139 ,   2 union all
select 71.5 , 65.5 ,   304,  137,    3 union all 
select 68 ,   63.5 ,   304,  131.5 , 4 union all
select 65.5,  66,      304,  131.5,  5 union all
select 63 ,   68.5,    304,  131.5,  6 union all
select 67.5,  63,      304 , 130.5,  7 

declare @t2 table(职位代码 int,计划数 int)
insert into @t2
select 303,      2 union all
select 304,      1 

select a.*,是否进入第二轮考试=(case when a.名次<= b.计划数*3 then '1' else '' end)
from @t1 a join @t2 b
    on a.职位代码=b.职位代码

结果：
如题

[Jamin_Liu]

--測試數據
declare @table1 table
(
[科目一] int
,[科目二] int
,[职位代码] char(3)
,[总分] int
,[名次] int
);

declare @table2 table
(
[职位代码] char(3)
,[计划数] int
);

insert into @table1
values(75,70,'303',145,1)
,(65,74,'303',139,2)
,(68,69.5,'303',137.5,3)
,(66.5,65,'303',131.5,4)
,(68.5,62.5,'303',131,5)
,(66.5,64.5,'303',131,6)
,(65.5,65.5,'303',131,7)
,(65.5,65,'303',130.5,8)
,(74,66,'304',140,1)
,(75.5,63.5,'304',139,2)
,(71.5,65.5,'304',137,3)
,(68,63.5,'304',131.5,4)
,(65.5,66,'304',131.5,5)
,(63,68.5,'304',131.5,6)
,(67.5,63,'304',130.5,7);

insert into @table2
values('303',2)
,('304',1);
--查詢結果
select t1.*,(case when t1.名次<=t2.计划数*3 then '1' else '' end) [是否進入第二輪面試]
from @table1 t1 join @table2 t2
on t1.职位代码=t2.职位代码

[feixianxxx]

[Quote=引用 6 楼 llxlett 的回复:]
SQL code

declare @t1 table(科目1 float,科目2 float,职位代码 int,分数 float,名次 int)
insert @t1
select 75, 70, 303, 145, 1 union all
select 65, 74, 303, 139 , 2 union all
select 68, 69.5 , 303, 137.5, 3 union all
select 66.5, 65 , 303 , 131.5 , 4 union all
select 68.5, 62.5 , 303, 131, 5 union all
select 66.5, 64.5 , 303, 131, 6 union all
sel…
[/Quote]

出错。。。

[feixianxxx]

[Quote=引用 8 楼 feixianxxx 的回复:]
引用 6 楼 llxlett 的回复:
SQL code

declare @t1 table(科目1 float,科目2 float,职位代码 int,分数 float,名次 int)
insert @t1
select 75, 70, 303, 145, 1 union all
select 65, 74, 303, 139 , 2 union all
select 68, 69.5 , 303, 137.5, 3 union all
select 66.5, 65 , 303 , 131.5 , 4 union all
select 68.5, 62.5 , 303, 131, 5 union all
select 66.5, 64.5 , …
[/Quote]

看错了 是正确的。。。。

[usher_gml]

[Quote=引用 6 楼 llxlett 的回复:]
SQL code

declare @t1 table(科目1 float,科目2 float,职位代码 int,分数 float,名次 int)
insert @t1
select 75,    70,    303,  145,    1 union all
select 65,    74,    303,  139 ,   2 union all
select 68,    69.5 ,   303,  137.5,  3 union all
select 66.5,  65 ,   303 , 131.5 , 4 union all
select 68.5,  62.5 ,   303,  131,    5 union all
select 66.5,  64.5 ,   303,  131,    6 union all
sel…
[/Quote]

UP

[swot2ly_100]

declare @tab table(f_item1 float,f_item2 float,f_career int,f_score float,f_no int)
insert into @tab(f_item1,f_item2,f_career,f_score,f_no)
select 75,70,303,145,1 union all
select 65,74,303,139,2 union all
select 68,69.5,303,137.5,3 union all
select 66.5,65,303,131.5,4 union all
select 68.5,62.5,303,131,5 union all
select 66.5,64.5,303,131,6 union all
select 65.5,65.5,303,131,7 union all
select 65.5,65,303,130.5,8 union all
select 74,66,304,140,1 union all
select 75.5,63.5,304,139,2 union all
select 71.5,65.5,304,137,3 union all
select 68,63.5,304,131.5,4 union all
select 65.5,66,304,131.5,5 union all
select 63,68.5,304,131.5,6 union all
select 67.5,63,304,130.5,7

declare @tab2 table(f_career int,f_plan int)
insert into @tab2(f_career,f_plan)
select 303,2 union
select 304,1

select a.f_item1,a.f_item2,a.f_career,a.f_score,a.f_no,case when a.f_no <= b.f_plan * 3 then 1 else 0 end as f_in
from @tab as a
inner join
@tab2 as b
on a.f_career = b.f_career

结果如下：

f_item1	f_item2	f_career	f_score	f_no	f_in
75	70	303	145	1	1
65	74	303	139	2	1
68	69.5	303	137.5	3	1
66.5	65	303	131.5	4	1
68.5	62.5	303	131	5	1
66.5	64.5	303	131	6	1
65.5	65.5	303	131	7	0
65.5	65	303	130.5	8	0
74	66	304	140	1	1
75.5	63.5	304	139	2	1
71.5	65.5	304	137	3	1
68	63.5	304	131.5	4	0
65.5	66	304	131.5	5	0
63	68.5	304	131.5	6	0
67.5	63	304	130.5	7	0

[IntoWindy]

非常感谢！受益匪浅！