表一person字段
id
sex
name
age
其中sex中男用“M”,女用“F”表示;
表二relation
字段
id1
id2
relation
其中relation用以下规则表示:
是子女关系,则为“0”,id1是父母,id2是子女;是夫妻关系,则“1”,id1是夫,id2是妻。
问1.有子女的母亲姓名
2.有夫妻关系的夫和妻的名字,如果没有妻子,则显示空格
3.夫妻年龄之和大于65岁的夫妻中丈夫的姓名答案:
1.
SELECT person.name mother,person.id id
FROM person
WHERE person.id IN (select id1 from relation where relation = 0) AND person.sex = 'F';2.
注:第一个简单我写上答案了,测试正确,但是第二个就死活写不出来!
id
sex
name
age
其中sex中男用“M”,女用“F”表示;
表二relation
字段
id1
id2
relation
其中relation用以下规则表示:
是子女关系,则为“0”,id1是父母,id2是子女;是夫妻关系,则“1”,id1是夫,id2是妻。
问1.有子女的母亲姓名
2.有夫妻关系的夫和妻的名字,如果没有妻子,则显示空格
3.夫妻年龄之和大于65岁的夫妻中丈夫的姓名答案:
1.
SELECT person.name mother,person.id id
FROM person
WHERE person.id IN (select id1 from relation where relation = 0) AND person.sex = 'F';2.
注:第一个简单我写上答案了,测试正确,但是第二个就死活写不出来!
create table person (
id int not null primary,
sex char(1) not null,
name char(20) not null,
age int not null);create table relation(
id1 int not null,
id2 int not null,
relation bool not null);intert into person values ('1','M','zhang','35');
intert into person values ('2','M','li','43');
intert into person values ('3','F','zhang_wife','30');
intert into person values ('4','M','qiao','30');
intert into person values ('5','F','qiao_wife','31');
intert into person values ('6','F','li_wife','31');
intert into person values ('7','F','huang_wife','48');
intert into person values ('8','M','huang','50');
intert into person values ('9','F','yang_wife','46');
intert into person values ('10','M','wang','45');
intert into person values ('11','F','yang_dau','16');
intert into person values ('12','F','qiao_dau','10');
intert into person values ('13','M','qiao_son','10');
intert into person values ('14','M','huang_son','13');intert into relation values ('1','3','1');
intert into relation values ('2','6','1');
intert into relation values ('4','5','1');
intert into relation values ('4','12','0');
intert into relation values ('4','13','0');
intert into relation values ('9','11','0');
intert into relation values ('8','14','0');
我自己想的思路是:
1.先建立临时表
create table t2 as select * from person join relation where person.id=relation.id1 or person.id=relation.id2;//建立临时表;+----+-----+------------+-----+-----+-----+----------+
| id | sex | name | age | id1 | id2 | relation |
+----+-----+------------+-----+-----+-----+----------+
| 1 | M | zhang | 35 | 1 | 3 | 1 |
| 3 | F | zhang_wife | 30 | 1 | 3 | 1 |
| 2 | M | li | 43 | 2 | 6 | 1 |
| 6 | F | li_wife | 31 | 2 | 6 | 1 |
| 4 | M | qiao | 30 | 4 | 5 | 1 |
| 5 | F | qiao_wife | 31 | 4 | 5 | 1 |
| 4 | M | qiao | 30 | 4 | 11 | 0 |
| 11 | F | qiao_dau | 10 | 4 | 11 | 0 |
| 4 | M | qiao | 30 | 4 | 12 | 0 |
| 12 | M | qiao_son | 10 | 4 | 12 | 0 |
| 9 | F | yang_wife | 46 | 9 | 10 | 0 |
| 10 | F | yang_dau | 16 | 9 | 10 | 0 |
| 8 | M | huang | 50 | 8 | 13 | 0 |
| 13 | M | huang_son | 13 | 8 | 13 | 0 |
+----+-----+------------+-----+-----+-----+----------+
2.检索relation=1的记录;
select * from t2 where relation = 1;但现在有个问题是:实际的表中有一个“wang”记录在relation表中没有记录,因为此人既没结婚也没儿女,那这样的记录怎么也显示出来,并且其他的属性列为空就行。
第二个问题:SELECT a.name 丈夫姓名,b.name 妻子姓名 FROM(SELECT * FROM PERSON a,PERSON b) tmpperson INNER JOIN relation tmprelation ON a.id=tmprelation.id1 AND b.id=tmprelation.id2 AND tmprelation.relation=1;关于你的WANG的记录,用外连接LEFT JOIN...ON是可以的啊:
SELECT a.name 丈夫姓名,b.name 妻子姓名 FROM(SELECT * FROM PERSON a,PERSON b) tmpperson LEFT JOIN relation tmprelation ON a.id=tmprelation.id1 AND b.id=tmprelation.id2 AND tmprelation.relation=1;
第三个问题:
SELECT a.name 丈夫姓名,FROM((SELECT * FROM PERSON a,PERSON b) tmpperson LEFT JOIN relation tmprelation ON a.id=tmprelation.id1 AND b.id=tmprelation.id2 AND tmprelation.relation=1) tmp WHERE (a.age+b.age)>65;