求一条较复杂的select语句(牵扯到判断问题)

Question

我的问题可以抽象成这样:
假如有一群学生去出游,这些学生中有男生也有女生,他们也来自不同的学校,然后到晚上他们可以去主办方那里去领取补助.但每个学校发的补助是不一样的,就是同一个学校也对男生和女生的补助标准也不一样.问题是,怎样能一次性取出能给哪个学生多少补助?假设有以下两个表:
1.学生信息表(tab_stu_info)stu_name  stu_school
-------------
boy_john   001
boy_jim    002
gir_kate   001
gir_ann    003其中stu_name表示学生姓名,前三个字母表示学生的性别,表中没有表示"性别"的这一字段.
stu_school是学校编号.2.学校信息表(tab_school)sch_id   boy_pay   gir_pay
------------------------
001      33       19
002      11       21
003      22       32我想取得的结果是:
stu_name    pay
---------------
boy_john   33
boy_jim    11
gir_kate   19
gir_ann    32就是说,从tab_stu_info中取出学生姓名和学校编号,靠姓名来判断学生的性别,然后靠学校编号和判断出来的性别,从tab_school中查出应该给此学生多少补助.请大家帮我想一想吧,谢谢了啊!!

Answer 1

create table tab_stu_info(stu_name varchar2(100), stu_school varchar2(100) );insert into tab_stu_info values ('boy_john','001');
insert into tab_stu_info values ('boy_jim','002');
insert into tab_stu_info values ('gir_kate','001');
insert into tab_stu_info values ('gir_ann','003');create table tab_school(sch_id varchar2(100), boy_pay int, gir_pay int);
insert into tab_school values ('001',33,19);
insert into tab_school values ('002',11,21);
insert into tab_school values ('003',22,32);
select stu_name,case when substr(stu_name,1,3)='boy' then boy_pay
                     else gir_pay end pay
from tab_stu_info inner join tab_school on stu_school=sch_id
boy_jim         11
boy_john 33
gir_ann         32
gir_kate 19

Answer 2

楼上正解！SELECT stu_name,
       CASE
          WHEN SUBSTR (stu_name, 1, 3) = 'boy'
             THEN boy_pay
          ELSE gir_pay
       END pay
  FROM tab_stu_info INNER JOIN tab_school ON stu_school = sch_id
STU_NAME PAYboy_john 33
boy_jim 11
gir_kate 19
gir_ann 32

Answer 3

除了inner join还有别的方法吗? 因为我可能还要连别的表啊.

Answer 4

多谢 hongqi162(失踪的月亮) 可是还有没有别的方法,比如不用inner join

Answer 5

用decode函数也可以：
select stu_name, decode(substr(stu_name, 1, 3), 'boy', boy_pay, gir_pay)
  from tab_stu_info t1, tab_school t2
 where t1.stu_school = t2.sch_id;

Answer 6

如果你还需要连别的表加到from后边就可以了，但是要注意关联条件。
刚才的语句重写一下，加个别名pay
select stu_name,
       decode(substr(stu_name, 1, 3), 'boy', boy_pay, gir_pay) pay
  from tab_stu_info t1, tab_school t2
 where t1.stu_school = t2.sch_id;

Answer 7

不用inner join就这样咯。
SELECT stu_name,
       CASE
          WHEN SUBSTR (stu_name, 1, 3) = 'boy'
             THEN boy_pay
          ELSE gir_pay
       END pay
  FROM tab_stu_info t, tab_school tt
 where t.stu_school = tt.sch_id

Answer 8

worldly_wind() 
解得不错!

Answer 9

inner join ,left join ,right join.....只有9i以后的版本才支持，楼上的应该是8i以前的版本的写法，呵呵

Answer 10

不知這樣行不：
select stu_name,
(select decode(substr(t.stu_name,1,3),'boy',boy_pay,gir_pay
  from tab_school v where v.sch_id=t.stu_school)
from stu_school t

Answer 11

create table tab_stu_info(
       stu_name varchar2(20),
       stu_school varchar2(20)
       );
       Insert into tab_stu_info(stu_name,stu_school)
       values('boy_john','001');
       Insert into tab_stu_info(stu_name,stu_school)
       values('boy_jim','002');
       Insert into tab_stu_info(stu_name,stu_school)
       values('gir_kate','001');
       Insert into tab_stu_info(stu_name,stu_school)
       values('gir_ann','003');
                                             
create table tab_school(
       sch_id varchar2(10),
       boy_pay number,
       gir_pay number
       );
       
       Insert into tab_school(sch_id,boy_pay,gir_pay)
       values('001',33,19);
       Insert into tab_school(sch_id,boy_pay,gir_pay)
       values('002',11,21);
       Insert into tab_school(sch_id,boy_pay,gir_pay)
       values('003',22,32);
       
select a.stu_name,decode(lpad(a.stu_name,3),'boy',(select b.boy_pay from tab_school b where b.sch_id=a.stu_school),
(select b.gir_pay from tab_school b where b.sch_id=a.stu_school)
)
from tab_stu_info a,tab_school b
where a.stu_school = b.sch_id
order by lpad(a.stu_name,3)经测试

Answer 12

其中stu_name表示学生姓名,前三个字母表示学生的性别,表中没有表示"性别"的这一字段.
=============================================================================
有个问题，这种设计算违反了“第一范式”吗？

Answer 13

http://community.csdn.net/Expert/topic/5428/5428970.xml?temp=.2512628
求解~！

Answer 14

真正开发的时候貌似不太考虑什么范式的吧.1楼的答案已经很好了.至于所谓的inner join...可以多连几个也没问题的,如有三张表可以用select a.*,b.*,c.* from a inner join b on a.id=b.id inner join c from a.id=c.id的方式来实现 或是按楼上的from多张表加上where条件来实现

Answer 15

create table tab_stu_info(stu_name varchar(100), stu_school varchar(100) )insert into tab_stu_info values ('boy_john','001')
insert into tab_stu_info values ('boy_jim','002')
insert into tab_stu_info values ('gir_kate','001')
insert into tab_stu_info values ('gir_ann','003')create table tab_school(sch_id varchar(100), boy_pay int, gir_pay int)
insert into tab_school values ('001',33,19)
insert into tab_school values ('002',11,21)
insert into tab_school values ('003',22,32)
select stu_name, case when charindex('boy',stu_name)>0 then boy_pay else gir_pay  end 
as pay_money from
tab_stu_info a,tab_school b
where a.stu_school =b.sch_id
stu_name                 pay_money   
------------------------ 
boy_john                      33
boy_jim                       11
gir_kate                      19
gir_ann                      32（所影响的行数为 4 行）

Answer 16

create table tab_stu_info(stu_name varchar(20), stu_school varchar(10))
create table tab_school(sch_id varchar(10), boy_pay int , gir_pay int)insert tab_stu_info select 'boy_john','001'
union all select 'boy_jim','002'
union all select 'gir_kate','001'
union all select 'gir_ann','003'insert tab_school select '001',33,19
union all select '002',11,21
union all select '003',22,32select * from tab_stu_info
select * from tab_schoolselect a.stu_name, case when substring(a.stu_name,1,3) = 'boy' then b.boy_pay
                        else b.gir_pay
                   end as pay from tab_stu_info a join tab_school b on a.stu_school = b.sch_id

Answer 17

我试过了：
select a.stu_name,pay = case 
                             when a.stu_name like 'boy_%' then b.boy_pay
                             when a.stu_name like 'gir_%' then b.gir_pay
                        end
from tab_stu_info a inner join tab_school b on a.stu_school=b.sch_id

Answer 18

select a.stu_name,case substring(a.stu_name,1,3)
    when 'boy' then b.boy_pay
    when 'gir' then b.gir_pay
end as payfrom tab_stu_info as a,tab_school as bwhere a.stu_school=b.sch_id

Answer 19

create table tab_stu_info(stu_name varchar(20), stu_school varchar(10))
create table tab_school(sch_id varchar(10), boy_pay int , gir_pay int)insert tab_stu_info select 'boy_john','001'
union all select 'boy_jim','002'
union all select 'gir_kate','001'
union all select 'gir_ann','003'insert tab_school select '001',33,19
union all select '002',11,21
union all select '003',22,32
--======================================================
select a.stu_name,case substring(a.stu_name,1,3) when 'boy' then b.boy_pay when 'gir' then b.gir_pay end as pay  from tab_stu_info as a,tab_school as b where a.stu_school=b.sch_id
go
--======================================================
boy_john 33
boy_jim 11
gir_kate 19
gir_ann 32

Answer 20

范式并非一定要严格遵守，但遵守范式总会带来一些便捷。
比如把
stu_name  stu_school
-------------
boy_john   001
boy_jim    002
gir_kate   001
gir_ann    003
拆分为
stu_name  stu_school  sex
-----------------------
john      001         M
jim       002         M
kate      001         F
ann       003         F
在某些情况下，你可以考虑在sex列上建bitmap索引，很有可能你会因此而提高查询效率。

Answer 21

select a.stu_name,--substring (a.stu_name,1,3), 
case 
when
substring (stu_name,1,3)='boy'then b.boy_pay
else gir_pay
end from tab_stu_info a , tab_school b where a.stu_school=b.sch_id
还可以
select a.stu_name,--substring (a.stu_name,1,3), 
case 
when
substring (stu_name,1,3)='boy'then b.boy_pay
else gir_pay
end from tab_stu_info a join tab_school b ona.stu_school=b.sch_id

Answer 22

Select A.stu_name,B.boy_pay as pay
From   tab_stu_info A, tab_school B
Where  A.stu_name like 'boy%' And A.stu_school = B.sch_id
Union
Select A.stu_name,B.gir_pay
From   tab_stu_info A, tab_school B
Where  A.stu_name like 'gir%' And A.stu_school = B.sch_id

Answer 23

其实用DECODE是最简单的方法了

Answer 24

select A.Stu_Name,B.gir_pay as pay from tab_stu_info A,tab_school B
where a.stu_school=b.sch_id
and substr(stu_name,1,3)='gir'
union 
select A.Stu_Name,B.gir_pay as pay from tab_stu_info A,tab_school B
where a.stu_school=b.sch_id
and substr(stu_name,1,3)='boy'先得到 男的，再得到女的，再合并，就ok了  用到一个substr

Answer 25

wangyouren(夕木双)  的更好