SQL编写

//用户表,用户ID,用户名称
create table t_user (user_id int,username varchar(20));
//用户帐户表,用户ID,用户余额(单位分)
create table t_account(user_id int,account_balance int);
insert into t_user values(1,'u1');
insert into t_user values(2,'u2');
insert into t_account values(1,10);
insert into t_account values(2,0);
insert into t_user values(3,'u3');
//用户交易表,用户ID,流水创建时间,交易金额(单位分),交易类型(0 POS支付;1 网上转账)
create table t_account_info (user_id int,create_time datetime,amount int,type int);
insert into t_account_info values(1,datetime('now'),100,0);
insert into t_account_info values(2,datetime('now'),200,0);
insert into t_account_info values(2,datetime('now'),300,0);
insert into t_account_info values(2,datetime('now'),300,1);
问题1 查询所有余额为0的用户信息,输出用户ID,用户名
左外连接
SELECT A.* FROM T_USER A LEFT JOIN T_ACCOUNT B ON A.USER_ID = B.USER.ID WHERE B.ACCOUNT_BALANCE = 0
直接连表查询
SELECT A.* FROM T_USER A,T_ACCOUNT B WHERE A.USER_ID = B.USER_ID AND B.ACCOUNT_BALANCE = 0;
问题2 查询当天的交易记录
SELECT A.USERNAME,B.CREATE_TIME,B.AMOUNT,B.TYPE FROM T_USER A,T_ACCOUNT_INFO B WHERE A.USER_ID = B.USER_ID AND B.CREATE_TIME BETWEEN date('2015-06-09') and date('2015-06-10')
问题3 查询所有用户的网上转账交易类型
SELECT A.USERNAME,'WangShangZhuanZhang'as TypeName from T_USER A,T_ACCOUNT_INFO B WHERE A.USER_ID = B.USER_ID AND B.TYPE = 1;

问题4 查询每个用户每种类型的交易汇总,只列出每种类型交易额之和大于5000的
SELECT * FROM (SELECT A.USERNAME,SUM(B.AMOUNT) AS TOTAL,B.TYPE FROM T_USER A,T_ACCOUNT_INFO B WHERE A.USER_ID = B.USER_ID GROUP BY B.USER_ID,B.TYPE) WHERE TOTAL > 5000
或者
SELECT A.USERNAME,SUM(B.AMOUNT) AS TOTAL,B.TYPE FROM T_USER A,T_ACCOUNT_INFO B WHERE A.USER_ID = B.USER_ID GROUP BY B.USER_ID,B.TYPE HAVING TOTAL > 5000

 

 

难点

1 时间函数,获取当天的.

2 分组时对sum的值有条件限制,一开始用的子查询,忘了还有having这个条件可以用