分享两道今天的笔试题，关于MySQL和php字符串处理的...

本帖最后由 sanalex 于 2009-11-19 22:33 编辑

题目是这样的，有如下两张表，求aaa表与bbb表中相同type的num值之差。
表aaa：[code] +----+------+------+
| id | type | num |
+----+------+------+
|  1 | A | 1024 |
|  2 | B | 2048 |
+----+------+------+
[/code]表bbb：[code]+----+------+-----+
| id | type | num |
+----+------+-----+
| 1 | A | 32 |
| 2 | A | 64 |
| 3 | A | 128 |
| 4 | B | 256 |
| 5 | B | 512 |
+----+------+-----+[/code]写出sql语句，求差部分只能用一句代码...汗死，笔试没电脑只能靠蒙了，当时没写对...

建立数据库，写入并查看数据部分：[code] mysql> create database sum;
Query OK, 1 row affected (0.01 sec)

mysql> use sum;
Database changed
mysql> create table aaa(
    -> id int(11) not null primary key,
    -> type char(1) not null,
    -> num int(11) not null
    -> );
Query OK, 0 rows affected (0.43 sec)

mysql> create table bbb(
    -> id int(11) not null primary key,
    -> type char(1) not null,
    -> num int(11) not null
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert aaa values
    -> ("1","A","1024"),
    -> ("2","B","2048");
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert bbb values
    -> ("1","A","32"),
    -> ("2","A","64"),
    -> ("3","A","128"),
    -> ("4","B","256"),
    -> ("5","B","512");
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from aaa;
+----+------+------+
| id | type | num  |
+----+------+------+
|  1 | A    | 1024 |
|  2 | B    | 2048 |
+----+------+------+
2 rows in set (0.00 sec)

mysql> select * from bbb;
+----+------+-----+
| id | type | num |
+----+------+-----+
|  1 | A    |  32 |
|  2 | A    |  64 |
|  3 | A    | 128 |
|  4 | B    | 256 |
|  5 | B    | 512 |
+----+------+-----+
5 rows in set (0.00 sec)
[/code]要想求差，得先把每张表先求和啊：[code] mysql> select an from
    -> (select aaa.type,sum(aaa.num) as an from aaa group by aaa.type)
    -> as a;
+------+
| an   |
+------+
| 1024 |
| 2048 |
+------+
2 rows in set (0.00 sec)

mysql> select bn from
    -> (select bbb.type,sum(bbb.num) as bn from bbb group by bbb.type)
    -> as b;
+------+
| bn   |
+------+
|  224 |
|  768 |
+------+
2 rows in set (0.00 sec)
[/code]求和完事该求差了：[code] mysql> select (a.an-b.bn) from
    -> (select aaa.type,sum(aaa.num) as an from aaa group by aaa.type) as a,
    -> (select bbb.type,sum(bbb.num) as bn from bbb group by bbb.type) as b
    -> where a.type=b.type;
+-------------+
| (a.an-b.bn) |
+-------------+
|         800 |
|        1280 |
+-------------+
2 rows in set (0.00 sec)
[/code]回来后的感想，一共5道题，时间30分钟，对于不熟练的新手来说，一下子就卡死了，我回来后在电脑上写了半个小时才把这道题写完，有些公司笔试对新手应聘者是不是太苛刻了...

新手成长也是需要项目锻炼的呀，发发牢骚，自己技艺不精也没办法，怨不得别人.....

虽说这次面试肯定是又失败了，但自己回来还是把所有的题都做好了...

无话可说了，笔试给台电脑用也行啊...

本帖最后由 sanalex 于 2009-11-20 20:13 编辑

给定字符串，求其中每个字符出现的次数，不分大小写：
原题变量还是写错的，变量不变量数组不数组的，闷...
（下面是我改好了的，字符串已经记不清了，随便写点啥，呵呵）[code]$str = "My name is hahahaha, who are you?";[/code]过程是这样：[code] <?php
$str = "My name is hahahaha, who are you?";
$str = strtolower($str);//先都转化成小写
$str = str_split($str);//把字符串拆开成数组
$str = array_count_values($str);//计算数组中每个元素出现的次数，这个函数好像不常用，呵呵，考官还问我有这个函数没，汗...
//print_r ($str);//输出一下就看到了，或者下面直接显示
foreach ($str as $key => $value) {
    echo "其中".$key."出现的次数为：".$value."<br />";
}
?>
[/code]结果：[code] Array
(
    [m] => 2
    [y] => 2
    [ ] => 6
    [n] => 1
    [a] => 6
    [e] => 2
    => 1
    => 1
    [h] => 5
    [,] => 1
    [w] => 1
    [o] => 2
    [r] => 1
    => 1
    [?] => 1
)
[/code]显示的话，这样：[code] 其中m出现的次数为：2
其中y出现的次数为：2
其中 出现的次数为：6
其中n出现的次数为：1
其中a出现的次数为：6
其中e出现的次数为：2
其中i出现的次数为：1
其中s出现的次数为：1
其中h出现的次数为：5
其中,出现的次数为：1
其中w出现的次数为：1
其中o出现的次数为：2
其中r出现的次数为：1
其中u出现的次数为：1
其中?出现的次数为：1
[/code]

统计字符窜出现次数而不是字符出现的次数：

$str      = strtolower($str);
$tmpArr = explode(' ',$str);
$numArr = array_count_values($tmpArr);
print_r($numArr);

SQL贼差的飘过

其实第一道题用right join 就可以，没有必要写那么麻烦的语句

本帖最后由 lxylxy888666 于 2009-11-20 14:32 编辑

从你注册时间看，我也仅仅比你早半年认识PHP啊

回复 liaofuqian


    啊，是我写错，是字符出现次数，嘿嘿~

指点一下，留下代码~

回复 sanalex


select a.id as id,(a.name-b.name) as cou
from (select sum(name) as name,id as id from testa group by id )  a
right join (select sum(name) as name, id as id from testb group by id ) b
on a.id=b.id ;

SELECT (
a.num - sum( b.num )
)
FROM aaa AS a
LEFT JOIN bbb AS b ON a.type = b.type
WHERE a.type = b.type
GROUP BY a.type

题目还算一般吧

刚看了一下第一题

SELECT  * , (a.num - b.num
) AS totalnum
FROM aa AS a
LEFT  JOIN bb AS b ON a.type = b.type


LIMIT 0 , 30

呵呵，先睡觉，有的语句不对哦~

Example #1 count_chars() example

<?php $data = "Two Ts and one F."; foreach (count_chars($data, 1) as $i => $val) { echo "There were $val instance(s) of \"" , chr($i) , "\" in the string.\n"; } ?>

SELECT a.num-SUM(b.num) FROM aaa a LEFT JOIN bbb b ON a.type=b.type GROUP BY b.type

恩哪。学习了。。现在面试的有些自己都搞不懂就拿来考别人。有的就干跪跟你屁谈一顿。

我忘记说...

第一题里面，两张表不只是仅有那几条数据，aaa里面可以有很多type为A的记录，同样bbb里面也有很多type为B的记录

第二题看到一个很好玩的解法，收了拿回去研究一下