[size=large]有两个表,分别为father表和son表。表结构如下:
father表
fid fname
1 a
2 b
3 c
son表
sid sname fid height money
100 s1 1 1.7 7000
101 s2 2 1.7 8000
102 s3 3 1.8 9000
建表代码:
create table father(
fid number primary key,
fname varchar(10)
);
create table son(
sid number primary key,
sname varchar(10),
fid number,
height number,
money number
);
select t.*, t.rowid from father t;
select t.*, t.rowid from son t;
问题用sqlserver或者Oracle查询:
1、查询sid、sname、fid
select sid,sname,fid from son;
2、查询各儿子涨价20%以后的新学费,注意,8000块以下的不涨价。
方法一:
select money*1.2 as new_money from son where money>=8000
union all
select money from son where money<8000;
方法二:
select '涨了' as 涨价情况,money*1.2 as new_money from son where money>=8000
union all
select '没涨',money from son where money<8000;
方法三:
select money,money*1.2 as new_money from son where money>=8000
union all
select money,null from son where money<8000;
select son.*,(case when money<8000 then money
when money>=8000 then money*1.2 end)as new_money
from son;
方法四(函数):
create or replace function f_get_new_money(p_money son.money%type)
return number
as
begin
if(p_money>=8000)then
return p_money*1.2;
else
return p_money;
end if;
end;
调用函数:select son.*,scott.f_get_new_money(money) as new_money from son;
3、查询sid、sname、fname。
--语法 from t1 join t2 on t1.id=t2.id
方法一:
SELECT s.sid,s.sname,f.fname
FROM father f JOIN son s ON f.fid=s.fid;
方法二(子查询):
SELECT s.sid,s.sname,s.fid,(SELECT fname FROM father WHERE father.fid=s.fid) AS fname
FROM son s;
4、fid、fname、儿子数(没有儿子的不显示)
方法一:
SELECT f.fid,f.fname,s.sname
FROM father f JOIN son s ON f.fid=s.fid;
方法二:
SELECT f.fid,f.fname,COUNT(*) x
FROM father f JOIN son s ON f.fid=s.fid
GROUP BY f.fid,f.fname
5、fid、fname、儿子数(没有儿子的个数显示为0)
方法3:以father表为准进行连接
SELECT f.fid,f.fname,s.sname
FROM father f LEFT JOIN son s ON f.fid=s.fid;
方法4:
SELECT f.fid,f.fname,COUNT(*) x
FROM father f LEFT JOIN son s ON f.fid=s.fid
GROUP BY f.fid,f.fname;
方法5:
SELECT f.fid,f.fname,COUNT(s.sid) x
FROM father f LEFT JOIN son s ON f.fid=s.fid
GROUP BY f.fid,f.fname;
方法6:
SELECT f.fid,f.fname,COUNT(s.sid) x
FROM son s RIGHT JOIN father f ON f.fid=s.fid
GROUP BY f.fid,f.fname;
方法7:
SELECT f.fid,f.fname,COUNT(s.sid) x
FROM father f , son s
WHERE f.fid=s.fid
GROUP BY f.fid,f.fname;
方法8:
--Oracle的特有方式
SELECT f.fid,f.fname,COUNT(s.sid) x
FROM father f , son s
WHERE f.fid=s.fid(+)
GROUP BY f.fid,f.fname;
--不是什么地方都 count(*)
6、找出不止有1个儿子的father信息:fid、fname、儿子数
方法一:sql思路:统计出所有父亲的儿子数,统计以后过滤,用having
SELECT f.fid,f.fname,COUNT(*) x
FROM father f JOIN son s ON f.fid=s.fid
GROUP BY f.fid,f.fname
HAVING COUNT(*)>1;
方法2:函数思路
CREATE OR REPLACE FUNCTION f_get_child_count(
p_fid father.fid%TYPE
)
RETURN NUMBER
AS
v_count NUMBER;
BEGIN
SELECT COUNT(*) INTO v_count FROM son WHERE son.fid=p_fid;
RETURN v_count;
END;
调用函数:
SELECT f.fid,f.fname,f_get_child_count(f.fid) AS x
FROM father f
WHERE f_get_child_count(f.fid)>1;
7、找出儿子最多的father信息:fid、fname、儿子数
--非相关子查询
SELECT f.fid,f.fname,COUNT(*) x
FROM father f JOIN son s ON f.fid=s.fid
GROUP BY f.fid,f.fname
HAVING COUNT(*)=(
SELECT max(COUNT(*))
FROM father f JOIN son s ON f.fid=s.fid
GROUP BY f.fid,f.fname
);
8、找出所有father中身高最高的儿子。
SELECT * FROM son
WHERE height = (SELECT MAX(height) FROM son);
9、找出各father中身高最高的儿子。
错误答案:
SELECT * FROM son
WHERE height IN(
SELECT MAX(height) FROM son GROUP BY fid
) ;
正确答案:
方法一:相关子查询
SELECT s1.*
FROM son s1
WHERE s1.height = (SELECT MAX(height) FROM son s2 WHERE s2.fid=s1.fid);
分析:
SELECT s1.* FROM son s1
SID SNAME FID HEIGHT MONEY
100 s1 1 1.7 7000 SELECT MAX(height) FROM son s2 WHERE s2.fid=1 -> 1.7=第一条记录的height
101 s2 2 1.7 8000 SELECT MAX(height) FROM son s2 WHERE s2.fid=2 -> 1.8!=第二条记录的height
102 s3 2 1.8 9000 SELECT MAX(height) FROM son s2 WHERE s2.fid=2 -> 1.8=第三条记录的height
方法2:函数
CREATE OR REPLACE FUNCTION f_get_max_height(
p_fid father.fid%TYPE
)
RETURN NUMBER
AS
v_height NUMBER;
BEGIN
SELECT MAX(height) INTO v_height FROM son WHERE son.fid=p_fid;
RETURN v_height;
END;
SELECT f.*,f_get_max_height(f.fid) ROM father f;
方法3:
SELECT s.sid,s.sname,s.height,f_get_max_height(s.fid)
FROM son s
WHERE s.height=f_get_max_height(s.fid);
10、找出身高在1.8到1.65之间的所有儿子及父亲信息。
SELECT f.fid,f.fname,s.sname,height
FROM father f LEFT JOIN son s ON f.fid=s.fid
WHERE height BETWEEN 1.65 AND 1.8;