browser icon
You are using an insecure version of your web browser. Please update your browser!
Using an outdated browser makes your computer unsafe. For a safer, faster, more enjoyable user experience, please update your browser today or try a newer browser.

鸡兔同笼:一个 SQL 的优化案例

Posted by on 2013 年 04 月 23 日

你可以任意转载本文,但请在转载后的文章中注明作者和原始链接。
媒体约稿请联系 titilima_AT_163.com(把“_AT_”换成“@”)。

一个学校 MIS 的数据库中有一张保存学生数据的表,大致结构如下:

Name Sex Class
张三 0 1
李四 1 1
王五 0 2

要求:统计出 1 班的男生数和女生数。

解 1:

1
2
SELECT COUNT(*) FROM Students WHERE Class=1 AND Sex=0;
SELECT COUNT(*) FROM Students WHERE Class=1 AND Sex=1;

解 2:

SELECT Sex FROM Students WHERE Class=1;
1
2
3
4
5
6
7
8
for (int i = 0; i < nRecordCount; ++i) {
    if (0 == Sex)
        ++nMaleCount;
    else if (1 == Sex)
        ++nFemaleCount;
    else
        assert(0 == Sex || 1 == Sex);
}

当然,接下来是我的解。

SELECT COUNT(*), SUM(Sex) FROM Students WHERE Class=1;
1
2
nFemaleCount = Sum;
nMaleCount = Count - nFemaleCount;

仅列代码,不予评论。当然,如果你对这个话题感兴趣,可以点这里看我另一篇类似的博文

订阅本站

没有评论

(Required)
(Required, will not be published)