有没有更好的 SQL 语句完成该查询（LEFT JOIN 相关）

lovetide

假设有下面的表和数据：

1. 
   
2. use test;
   
3. 
   
4. drop table if exists LeftJoinTest_QATemplate;
   
5. create table LeftJoinTest_QATemplate (`考核项` varchar(20), `结果` enum('好','差'), `得分` int, primary key (`考核项`, `结果`)) comment '考核项模板';
   
6. 
   
7. drop table if exists LeftJoinTest_QAResult;
   
8. create table LeftJoinTest_QAResult (`姓名` varchar(10), `考核项` varchar(20), `结果` enum('好','差'), primary key (`姓名`, `考核项`)) comment '考核结果';
   
9. 
   
10. insert into LeftJoinTest_QATemplate (`考核项`, `结果`, `得分`) values ('工作态度', '好', 10);
    
11. insert into LeftJoinTest_QATemplate (`考核项`, `结果`, `得分`) values ('工作态度', '差', -10);
    
12. insert into LeftJoinTest_QATemplate (`考核项`, `结果`, `得分`) values ('工作效率', '好', 20);
    
13. insert into LeftJoinTest_QATemplate (`考核项`, `结果`, `得分`) values ('工作效率', '差', -20);
    
14. insert into LeftJoinTest_QATemplate (`考核项`, `结果`, `得分`) values ('工作能力', '好', 30);
    
15. insert into LeftJoinTest_QATemplate (`考核项`, `结果`, `得分`) values ('工作能力', '差', -30);
    
16. 
    
17. 
    
18. insert into LeftJoinTest_QAResult (`姓名`, `考核项`, `结果`) values ('赵', '工作态度', '差');
    
19. --insert into LeftJoinTest_QAResult (`姓名`, `考核项`, `结果`) values ('赵', '工作效率', null);
    
20. --insert into LeftJoinTest_QAResult (`姓名`, `考核项`, `结果`) values ('赵', '工作能力', null);
    
21. 
    
22. insert into LeftJoinTest_QAResult (`姓名`, `考核项`, `结果`) values ('钱', '工作态度', '差');
    
23. --insert into LeftJoinTest_QAResult (`姓名`, `考核项`, `结果`) values ('钱', '工作效率', null);
    
24. insert into LeftJoinTest_QAResult (`姓名`, `考核项`, `结果`) values ('钱', '工作能力', '好');
    
25. 
    
26. --insert into LeftJoinTest_QAResult (`姓名`, `考核项`, `结果`) values ('钱', '工作态度', null);
    
27. insert into LeftJoinTest_QAResult (`姓名`, `考核项`, `结果`) values ('孙', '工作效率', '差');
    
28. --insert into LeftJoinTest_QAResult (`姓名`, `考核项`, `结果`) values ('钱', '工作能力', null);
    

复制代码

现在想查出每个人、每个/所有考核项的结果（如果 LeftJoinTest_QAResult 表中不存在某人的某个考核项，则该某人的该考核项的结果必须为 NULL，而不能不存在这条记录 ）
--得到预期结果如下：

1. /*
   
2. +------+----------+------+
   
3. | 姓名 | 考核项   | 结果 |
   
4. +------+----------+------+
   
5. | 赵   | 工作态度 | 差   |
   
6. | 赵   | 工作效率 | NULL |
   
7. | 赵   | 工作能力 | NULL |
   
8. | 钱   | 工作态度 | 差   |
   
9. | 钱   | 工作效率 | NULL |
   
10. | 钱   | 工作能力 | 好   |
    
11. | 孙   | 工作态度 | NULL |
    
12. | 孙   | 工作效率 | 差   |
    
13. | 孙   | 工作能力 | NULL |
    
14. +------+----------+------+
    
15. */
    
16. 
    
17. --SQL:
    
18. select '赵' 姓名,t.考核项,r1.结果
    
19. from
    
20.         (select distinct 考核项 from LeftJoinTest_QATemplate) t
    
21.         left join
    
22.         (select * from LeftJoinTest_QAResult where 姓名='赵') r1 on t.考核项=r1.考核项
    
23. 
    
24.         union
    
25. 
    
26. select '钱',t.考核项,r2.结果
    
27. from
    
28.         (select distinct 考核项 from LeftJoinTest_QATemplate) t
    
29.         left join
    
30.         (select * from LeftJoinTest_QAResult where 姓名='钱') r2 on t.考核项=r2.考核项
    
31. 
    
32.         union
    
33. 
    
34. select '孙',t.考核项,r3.结果
    
35. from
    
36.         (select distinct 考核项 from LeftJoinTest_QATemplate) t
    
37.         left join
    
38.         (select * from LeftJoinTest_QAResult where 姓名='孙') r3 on t.考核项=r3.考核项
    
39. ;
    

复制代码

问题：由于 LeftJoinTest_QAResult  表中的“姓名”具有不确定性，所以上面根据固定姓名写出的 SQL 语句无法在实际中应用，所以希望能有一个通用的 SQL 语句来得到预期结果，但不知道如何实现。

另外：
如果在 LeftJoinTest_QAResult  表中把每个人缺少的考核项补充进去，然后用一个简单的 SELECT 语句查询出来，也能得到预期的结果，但这不是我希望的，因为
1、实际应用中，考核项很多，而需要保存的考核项很少，这种方法就导致大量无用的数据存放在数据库中
2、实际应用中，考核项是会变更的（考核模板变更），当加入了新的考核项后，依然是会出现考核项结果缺少的情况

1. /*
   
2. -- 补充行
   
3. insert into LeftJoinTest_QAResult (`姓名`, `考核项`, `结果`) values ('赵', '工作效率', null);
   
4. insert into LeftJoinTest_QAResult (`姓名`, `考核项`, `结果`) values ('赵', '工作能力', null);
   
5. 
   
6. insert into LeftJoinTest_QAResult (`姓名`, `考核项`, `结果`) values ('钱', '工作效率', null);
   
7. 
   
8. insert into LeftJoinTest_QAResult (`姓名`, `考核项`, `结果`) values ('钱', '工作态度', null);
   
9. insert into LeftJoinTest_QAResult (`姓名`, `考核项`, `结果`) values ('钱', '工作能力', null);
   
10. 
    
11. -- 预期结果：
    
12. select * from LeftJoinTest_QAResult order by 姓名;
    
13. */
    

复制代码

[ 本帖最后由 lovetide 于 2009-7-2 23:54 编辑 ]

lovetide

呃，这样写似乎可以，明天在实际环境下测试一下……

1. select t.姓名,t.考核项,r.结果
   
2. from
   
3.         (
   
4.         select *
   
5.         from
   
6.                 (select distinct 考核项 from LeftJoinTest_QATemplate) t
   
7.                 cross join
   
8.                 (select distinct 姓名 from LeftJoinTest_QAResult) r
   
9.         ) t
   
10.         left join LeftJoinTest_QAResult r on t.姓名=r.姓名 and t.考核项=r.考核项
    
11. ;

复制代码

再精简一下：

1. 
   
2. select r1.姓名,t.考核项,r.结果
   
3. from
   
4.         (select distinct 考核项 from LeftJoinTest_QATemplate) t
   
5.         cross join
   
6.         (select distinct 姓名 from LeftJoinTest_QAResult) r1
   
7.         left join
   
8.         LeftJoinTest_QAResult r on r1.姓名=r.姓名 and t.考核项=r.考核项
   
9. ;
   

复制代码

[ 本帖最后由 lovetide 于 2009-7-3 10:07 编辑 ]