一：
我的表结构，desc contest;
+----------+-------------+------+-----+---------+-------+
| Field ? ?| Type ? ? ? ?| Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| datatime | varchar(20) | YES ?| ? ? | NULL ? ?| ? ? ? |
| result ? | varchar(20) | YES ?| ? ? | NULL ? ?| ? ? ? |
+----------+-------------+------+-----+---------+-------+
表内数据:
?
+------------+--------+
| datatime ? | result |
+------------+--------+
| 2005-05-09 | fu ? ? |
| 2005-05-09 | fu ? ? |
| 2005-05-09 | 胜 ? ? ?|
| 2005-05-09 | 胜 ? ? ?|
| 2005-05-10 | fu ? ? |
| 2005-05-10 | fu ? ? |
| 2005-05-10 | 胜 ? ? ?|
+------------+--------+
如果要生成下列结果, 该如何写sql语句?
?? ? ? ? ? 胜 负
2005-05-09 2 ?2
2005-05-10 1 ?2
?
1、?select datatime,sum(case when result='胜' then 1 else 0 end) as '胜', sum(case when result='fu' then 1 else 0 end) as '负' from contest group by datatime;
?
2、?select m.datatime,m.ceshi as '胜',n.ceshi as '败' from (select datatime,count(*) as ceshi from contest where result='胜' group by datatime) m inner join?(select datatime,count(*) as ceshi from contest where result='fu' group by datatime) n on m.datatime=n.datatime;
?
二：面试题：一个日期判断的sql语句？
请取出tb_send表中日期(SendTime字段)为当天的所有记录?(SendTime字段为datetime型，包含日期与时间)
------------------------------------------
select * from tb where datediff(dd,SendTime,getdate())=0
?
注意：上面适用于sqlserver，如果是mysql的话要去掉第一个参数，如下：
?select * from test2 where datediff(now(),time)=0;
?
?
三、表中有A B C三列,用SQL语句实现：当A列大于B列时选择A列否则选择B列，当B列大于C列时选择B列否则选择C列。
select (case when A>B then A else B end) as value1,(case when B>C then Belse C end) as value2 from test3;
?
四 inner join 、left outer join 和right outer join的区别
?
表test2：
?
+----+---------------------+-------+
| id | time ? ? ? ? ? ? ? ?| name ?|
+----+---------------------+-------+
| ?1 | 2011-08-07 21:20:43 | name1 |
| ?2 | 2011-08-07 22:20:17 | name2 |
| ?3 | 2011-08-07 22:20:25 | name3 |
+----+---------------------+-------+
表test3：
+------+------+------+
| A ? ?| B ? ?| C ? ?|
+------+------+------+
| ? ?3 | ? ?4 | ? ?5 |
| ? ?5 | ? ?4 | ? ?6 |
| ? ?6 | ? ?4 | ? ?2 |
+------+------+------+
?
mysql> select a.id, a.time,b.B from test2 a inner join test3 b on a.id=b.A;
+----+---------------------+------+
| id | time ? ? ? ? ? ? ? ?| B ? ?|
+----+---------------------+------+
| ?3 | 2011-08-07 22:20:25 | ? ?4 |
+----+---------------------+------+
1 row in set (0.03 sec)
?
mysql> select a.id, a.time,b.B from test2 a left outer join test3 b on a.id=b.A;
?
+----+---------------------+------+
| id | time ? ? ? ? ? ? ? ?| B ? ?|
+----+---------------------+------+
| ?1 | 2011-08-07 21:20:43 | NULL |
| ?2 | 2011-08-07 22:20:17 | NULL |
| ?3 | 2011-08-07 22:20:25 | ? ?4 |
+----+---------------------+------+
3 rows in set (0.00 sec)
?
mysql> select a.id, a.time,b.B from test2 a right outer join test3 b on a.id=b.A
;
+------+---------------------+------+
| id ? | time ? ? ? ? ? ? ? ?| B ? ?|
+------+---------------------+------+
| ? ?3 | 2011-08-07 22:20:25 | ? ?4 |
| NULL | NULL ? ? ? ? ? ? ? ?| ? ?4 |
| NULL | NULL ? ? ? ? ? ? ? ?| ? ?4 |
+------+---------------------+------+
?
?
?
?