sql常用查询语句-----表连接查询

一：

我的表结构，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 |

+------+---------------------+------+