sql 语句使用

1. 有一张表t_stu；其中三个字段:name,gender,grade；要求用一条sql语句查出男生前5名和女生前五名。

这里使用postgreSQL数据库

-- Create the table
CREATE TABLE
    intense.t_stu
    (
        id NUMERIC(24),
        name CHARACTER VARYING(20),
        gender NUMERIC(1),
        grade NUMERIC(4)
    );

-- Truncate the table
truncate table intense.t_stu;

-- Insert data
insert into intense.t_stu values(1,'John',1,93);     
insert into intense.t_stu values(2,'Lisa',0,94);    
insert into intense.t_stu values(3,'John2',1,83);     
insert into intense.t_stu values(4,'Lisa2',0,84);    
insert into intense.t_stu values(5,'John3',1,73);     
insert into intense.t_stu values(6,'Lisa3',0,74);    
insert into intense.t_stu values(7,'John4',1,63);     
insert into intense.t_stu values(8,'Lisa4',0,64);    
insert into intense.t_stu values(9,'John5',1,53);     
insert into intense.t_stu values(10,'Lisa5',0,54);    
insert into intense.t_stu values(11,'John6',1,103);     
insert into intense.t_stu values(12,'Lisa6',0,104);

-- Get the data needed
SELECT
    *
FROM
    intense.t_stu a
WHERE
    (
        SELECT
            COUNT(*)
        FROM
            intense.t_stu
        WHERE
            gender=a.gender
        AND grade > a.grade) < 5
ORDER BY
    a.grade DESC

a表按照grade由高到低排列，并和t_stu连接，条件是gender相等并且grade大于a表的grade(返回10条记录，count=4略去)：



如果不考虑gender，只求前5名：

select * from intense.t_stu a where (select count(*) from intense.t_stu where grade > a.grade) < 5 order by a.grade desc

2. 有一张表t_game，记录了游戏玩家的id，游戏玩家名name，和每个玩家玩的游戏game，请找出玩游戏最多的那个玩家。

-- Create the table  
CREATE TABLE intense.t_game(    
id numeric(24),
name character varying(20),    
game character varying(20)
);

-- Truncate the table
truncate table intense.t_game;

-- Insert data
insert into intense.t_game values(1,'John','game');
insert into intense.t_game values(2,'Lisa','game2');
insert into intense.t_game values(3,'Jack','game3');
insert into intense.t_game values(4,'Jim','game4');
insert into intense.t_game values(3,'Jack','game5');
insert into intense.t_game values(5,'Eric','game6');
insert into intense.t_game values(1,'John','game7');
insert into intense.t_game values(3,'Jack','game8');
insert into intense.t_game values(6,'Nicolas','game9');

-- Get the data needed
-- MySQL, postgreSQL
SELECT
    id,
    name
FROM
    intense.t_game
GROUP BY
    id,
    name
ORDER BY
    COUNT(*) DESC LIMIT 1

-- SQL Server
SELECT
    TOP 1 id,
    name
FROM
    intense.t_game
GROUP BY
    id,
    name
ORDER BY
    COUNT(*) DESC

-- Oracle

3. CASE WHEN 示例：

-- CASE后面跟字段
SELECT
    (
        CASE account
            WHEN 1
            THEN 0.5
            WHEN 0
            THEN 0
            ELSE FLOAT(account)
        END) AS account_val
FROM
    tb_account;

-- 如果判断表达式比较复杂，把字段放到每个判断表达式中
SELECT
    (
        CASE
            WHEN account IS NULL
            THEN 0
            WHEN account > 0
            THEN FLOAT(account)
            ELSE -1
        END) AS account_val
FROM
    tb_account;

注：这里的float函数是DB2数据库支持的，其它数据库请改成相应的函数。

4. 同步两个表中的某些字段：

表连接关键字(LEFT JOIN, INNER JOIN, RIGHT JOIN等)常常用于查询语句，其实也可以用于更新和删除语句：

在DB2和Oracle上：

UPDATE
    tableB b
SET
    (
        COL_1, COL_2, COL3
    )
    =
    (
        SELECT
            COL_4, COL_5, COL_6
        FROM
            tableA a
        WHERE
            a.ID = b.ID)
WHERE -- WHERE能限定范围，提高性能；如果数据量较大，需要给两张表的连接字段加上索引
    ID IN
    (
        SELECT
            ID
        FROM
            tableA a
        WHERE
            a.ID = b.ID)

如果tableA中有重复的记录，会导致更新失败，因为数据库不知道该更新为哪个值：
解决思路：限定匹配值只有一个：

-- DB2
UPDATE
    tableB b
SET
    (
        COL_1, COL_2, COL3
    )
    =
    (
        SELECT
            COL_4, COL_5, COL_6
        FROM
            tableA a
        WHERE
            a.ID = b.ID ORDER BY CREATE_TIME DESC FETCH FIRST 1 ROWS ONLY)
    ID IN
    (
        SELECT
            ID
        FROM
            tableA a
        WHERE
            a.ID = b.ID)

SQL Server上：

UPDATE
    b
SET
    COL_1=a.COL_4,
    COL_2=a.COL_5,
    COL_3=a.COL_6
FROM
    tableA a
LEFT JOIN
    tableB b
ON
    a.ID = b.ID

-- 也可以写成：
UPDATE
    b
SET
    COL_1=a.COL_4,
    COL_2=a.COL_5,
    COL_3=a.COL_6
FROM
    tableA a,
    tableB b
WHERE
    a.ID = b.ID