SQl 连接的使用

SQl 连接的使用

SQl 连接主要是内连接(inner join)、左连接(left join)、右连接(right join)和全连接(cross join，即：笛卡尔积)。数据库如SQLite支持的主要是：inner join、left join、cross join三种。具体参看连接，分别对应两个表对象A和B的交集，A(A left join B,此时A常常表记录会多于表B)，B。

##顺丰SQL语句第二题：挑选出所有八月份=开始的项目
#table1:task_arrive_transfer
curs.execute('create table task_arrive_transfer(task_no varchar(20),arrive_time date,transfer_fee double(10))')
curs.execute("insert into task_arrive_transfer(task_no,arrive_time,transfer_fee) values('tsk0820001','2018-08-20 10:20:55',800)")
curs.execute("insert into task_arrive_transfer(task_no,arrive_time,transfer_fee) values('tsk0820001','2018-08-20 10:00:10',700)")
curs.execute("insert into task_arrive_transfer(task_no,arrive_time,transfer_fee) values('tsk0820001','2018-08-20 10:50:55',656)")
curs.execute("insert into task_arrive_transfer(task_no,arrive_time,transfer_fee) values('tsk0820002','2018-08-10 10:20:55',300)")
curs.execute("insert into task_arrive_transfer(task_no,arrive_time,transfer_fee) values('tsk0820003','2018-08-11 10:20:55',450)")
curs.execute("insert into task_arrive_transfer(task_no,arrive_time,transfer_fee) values('tsk0820003','2018-08-13 10:20:55',575)")
curs.execute("insert into task_arrive_transfer(task_no,arrive_time,transfer_fee) values('tsk0820002','2018-08-10 16:20:55',250)")
curs.execute("insert into task_arrive_transfer(task_no,arrive_time,transfer_fee) values('tsk0820003','2018-08-11 16:20:55',190)")
curs.execute("insert into task_arrive_transfer(task_no,arrive_time,transfer_fee) values('tsk0820003','2018-08-13 16:20:55',720)")
curs.execute("insert into task_arrive_transfer(task_no,arrive_time,transfer_fee) values('tsk0820004','2018-08-11 16:20:55',190)")
curs.execute("insert into task_arrive_transfer(task_no,arrive_time,transfer_fee) values('tsk0820004','2018-08-13 16:20:55',720)")

curs.execute("insert into task_arrive_transfer(task_no,arrive_time,transfer_fee) values('tsk0820003','2018-09-11 16:20:55',190)")
curs.execute("insert into task_arrive_transfer(task_no,arrive_time,transfer_fee) values('tsk0820003','2018-09-13 16:20:55',720)")
curs.execute("insert into task_arrive_transfer(task_no,arrive_time,transfer_fee) values('tsk0820004','2018-09-11 16:20:55',190)")
curs.execute("insert into task_arrive_transfer(task_no,arrive_time,transfer_fee) values('tsk0820004','2018-09-13 16:20:55',720)")
curs.execute("select * from task_arrive_transfer")
data4=curs.fetchall()


#table2:task_operate
curs.execute('create table task_operate(task_no varchar(20),start_time,end_time,emp_no varchar(20))')
curs.execute("insert into task_operate(task_no,start_time,end_time,emp_no) values('tsk0820001','2018-08-20 10:10:33','2018-08-20 10:25:55','H0001')")
curs.execute("insert into task_operate(task_no,start_time,end_time,emp_no) values('tsk0820002','2018-08-10 10:03:05','2018-08-12 10:25:23','H0001')")
curs.execute("insert into task_operate(task_no,start_time,end_time,emp_no) values('tsk0820003','2018-08-10 10:20:55','2018-08-14 10:20:55','H0002')")
curs.execute("insert into task_operate(task_no,start_time,end_time,emp_no) values('tsk0820004','2018-08-15 13:21:55','2018-08-15 14:40:07','H0003')")
curs.execute("select * from task_operate")
data4=curs.fetchall()

#先做一个笛卡尔积取出想要的列
curs.execute("select AA.task_no,AA.arrive_time,AA.start_time,AA.end_time,AA.transfer_fee,AA.emp_no from (select * from task_arrive_transfer as A cross join task_operate as B on  A.task_no==B.task_no) as AA")
data4=curs.fetchall()
print(data4)

结果

[('tsk0820001', '2018-08-20 10:20:55', '2018-08-20 10:10:33', '2018-08-20 10:25:55', 800.0, 'H0001'), 
('tsk0820001', '2018-08-20 10:00:10', '2018-08-20 10:10:33', '2018-08-20 10:25:55', 700.0, 'H0001'),
('tsk0820001', '2018-08-20 10:50:55', '2018-08-20 10:10:33', '2018-08-20 10:25:55', 656.0, 'H0001'),
('tsk0820002', '2018-08-10 10:20:55', '2018-08-10 10:03:05', '2018-08-12 10:25:23', 300.0, 'H0001'), 
('tsk0820003', '2018-08-11 10:20:55', '2018-08-10 10:20:55', '2018-08-14 10:20:55', 450.0, 'H0002'), 
('tsk0820003', '2018-08-13 10:20:55', '2018-08-10 10:20:55', '2018-08-14 10:20:55', 575.0, 'H0002'),
('tsk0820002', '2018-08-10 16:20:55', '2018-08-10 10:03:05', '2018-08-12 10:25:23', 250.0, 'H0001'), 
('tsk0820003', '2018-08-11 16:20:55', '2018-08-10 10:20:55', '2018-08-14 10:20:55', 190.0, 'H0002'), 
('tsk0820003', '2018-08-13 16:20:55', '2018-08-10 10:20:55', '2018-08-14 10:20:55', 720.0, 'H0002'),
('tsk0820004', '2018-08-11 16:20:55', '2018-08-15 13:21:55', '2018-08-15 14:40:07', 190.0, 'H0003'),
('tsk0820004', '2018-08-13 16:20:55', '2018-08-15 13:21:55', '2018-08-15 14:40:07', 720.0, 'H0003')]

#只要本质还是table就可以用()将select语句无限制的嵌套下去.
curs.execute("select BB.task_no,sum(BB.transfer_fee)  from (select AA.task_no,AA.arrive_time,AA.start_time,AA.end_time,AA.transfer_fee,AA.emp_no from (select * from task_arrive_transfer as A cross join task_operate as B on  A.task_no==B.task_no) as AA) as BB group by BB.task_no")
data4=curs.fetchall()
print(data4)

结果

[('tsk0820001', 2156.0), 
('tsk0820002', 550.0), 
('tsk0820003', 1935.0), 
('tsk0820004', 910.0)]

#挑选出所有八月份=开始的项目
curs.execute("select BB.task_no,sum(BB.transfer_fee)  \
from (select AA.task_no,AA.arrive_time,AA.start_time,AA.end_time,AA.transfer_fee,AA.emp_no from (select * from task_arrive_transfer as A cross join task_operate as B on  A.task_no==B.task_no) as AA) as BB where substr(strftime(BB.start_time),8,-2)=='08'  group by BB.task_no")
data4=curs.fetchall()

#测试时间返回的都是'08'即八月份.  #等号写一个或者写两个都一样。
curs.execute("select substr(strftime(start_time),8,-2) from task_operate  where substr(strftime(start_time),8,-2)='08'  group by task_no")
data4=curs.fetchall()

curs.execute("select BB.task_no,sum(BB.transfer_fee)  \
from (select AA.task_no,AA.arrive_time,AA.start_time,AA.end_time,AA.transfer_fee,AA.emp_no from (select * from task_arrive_transfer as A cross join task_operate as B on  A.task_no==B.task_no) as AA) as BB where substr(strftime(BB.start_time),8,-2)=='08'  group by BB.task_no")
data4=curs.fetchall()
print(data4)

结果

[('tsk0820001', 2156.0),
 ('tsk0820002', 550.0), 
 ('tsk0820003', 1935.0),
  ('tsk0820004', 910.0)]