免费注册 查看新帖 |

Chinaunix

  平台 论坛 博客 文库
最近访问板块 发新帖
查看: 2411 | 回复: 1

有没有更好的 SQL 语句完成该查询(LEFT JOIN 相关) [复制链接]

论坛徽章:
0
发表于 2009-07-02 23:51 |显示全部楼层
假设有下面的表和数据:

  1. use test;

  2. drop table if exists LeftJoinTest_QATemplate;
  3. create table LeftJoinTest_QATemplate (`考核项` varchar(20), `结果` enum('好','差'), `得分` int, primary key (`考核项`, `结果`)) comment '考核项模板';

  4. drop table if exists LeftJoinTest_QAResult;
  5. create table LeftJoinTest_QAResult (`姓名` varchar(10), `考核项` varchar(20), `结果` enum('好','差'), primary key (`姓名`, `考核项`)) comment '考核结果';

  6. insert into LeftJoinTest_QATemplate (`考核项`, `结果`, `得分`) values ('工作态度', '好', 10);
  7. insert into LeftJoinTest_QATemplate (`考核项`, `结果`, `得分`) values ('工作态度', '差', -10);
  8. insert into LeftJoinTest_QATemplate (`考核项`, `结果`, `得分`) values ('工作效率', '好', 20);
  9. insert into LeftJoinTest_QATemplate (`考核项`, `结果`, `得分`) values ('工作效率', '差', -20);
  10. insert into LeftJoinTest_QATemplate (`考核项`, `结果`, `得分`) values ('工作能力', '好', 30);
  11. insert into LeftJoinTest_QATemplate (`考核项`, `结果`, `得分`) values ('工作能力', '差', -30);


  12. insert into LeftJoinTest_QAResult (`姓名`, `考核项`, `结果`) values ('赵', '工作态度', '差');
  13. --insert into LeftJoinTest_QAResult (`姓名`, `考核项`, `结果`) values ('赵', '工作效率', null);
  14. --insert into LeftJoinTest_QAResult (`姓名`, `考核项`, `结果`) values ('赵', '工作能力', null);

  15. insert into LeftJoinTest_QAResult (`姓名`, `考核项`, `结果`) values ('钱', '工作态度', '差');
  16. --insert into LeftJoinTest_QAResult (`姓名`, `考核项`, `结果`) values ('钱', '工作效率', null);
  17. insert into LeftJoinTest_QAResult (`姓名`, `考核项`, `结果`) values ('钱', '工作能力', '好');

  18. --insert into LeftJoinTest_QAResult (`姓名`, `考核项`, `结果`) values ('钱', '工作态度', null);
  19. insert into LeftJoinTest_QAResult (`姓名`, `考核项`, `结果`) values ('孙', '工作效率', '差');
  20. --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. --SQL:
  17. select '赵' 姓名,t.考核项,r1.结果
  18. from
  19.         (select distinct 考核项 from LeftJoinTest_QATemplate) t
  20.         left join
  21.         (select * from LeftJoinTest_QAResult where 姓名='赵') r1 on t.考核项=r1.考核项

  22.         union

  23. select '钱',t.考核项,r2.结果
  24. from
  25.         (select distinct 考核项 from LeftJoinTest_QATemplate) t
  26.         left join
  27.         (select * from LeftJoinTest_QAResult where 姓名='钱') r2 on t.考核项=r2.考核项

  28.         union

  29. select '孙',t.考核项,r3.结果
  30. from
  31.         (select distinct 考核项 from LeftJoinTest_QATemplate) t
  32.         left join
  33.         (select * from LeftJoinTest_QAResult where 姓名='孙') r3 on t.考核项=r3.考核项
  34. ;
复制代码



问题:由于 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. insert into LeftJoinTest_QAResult (`姓名`, `考核项`, `结果`) values ('钱', '工作效率', null);

  6. insert into LeftJoinTest_QAResult (`姓名`, `考核项`, `结果`) values ('钱', '工作态度', null);
  7. insert into LeftJoinTest_QAResult (`姓名`, `考核项`, `结果`) values ('钱', '工作能力', null);

  8. -- 预期结果:
  9. select * from LeftJoinTest_QAResult order by 姓名;
  10. */
复制代码


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

论坛徽章:
0
发表于 2009-07-03 00:33 |显示全部楼层
呃,这样写似乎可以,明天在实际环境下测试一下……
  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. select r1.姓名,t.考核项,r.结果
  2. from
  3.         (select distinct 考核项 from LeftJoinTest_QATemplate) t
  4.         cross join
  5.         (select distinct 姓名 from LeftJoinTest_QAResult) r1
  6.         left join
  7.         LeftJoinTest_QAResult r on r1.姓名=r.姓名 and t.考核项=r.考核项
  8. ;
复制代码

[ 本帖最后由 lovetide 于 2009-7-3 10:07 编辑 ]
您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

  

北京盛拓优讯信息技术有限公司. 版权所有 京ICP备16024965号-6 北京市公安局海淀分局网监中心备案编号:11010802020122 niuxiaotong@pcpop.com 17352615567
未成年举报专区
中国互联网协会会员  联系我们:huangweiwei@itpub.net
感谢所有关心和支持过ChinaUnix的朋友们 转载本站内容请注明原作者名及出处

清除 Cookies - ChinaUnix - Archiver - WAP - TOP