sql 语句使用

jy166102

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