Sql行转列的两个小例子

SQL行转列

所谓行转列就是将某一个category类型的列(nx1的矩阵,实质是有m类),目标是将多行转换成为多列(新增m列)。

1. 例1：模拟顺丰SQL小例子

##顺丰面试题(SQLite编码实现题目)
import sqlite3
conn=sqlite3.connect('C:/Users/Administrator/Kaggle_in/SQLite/country_state.sqlite')
curs=conn.cursor()
curs.execute('create table waybill_constype(Consign_day int,waybill_no int,Class varchar(20),Userid varchar(20))')
curs.execute("insert into waybill_constype(Consign_day,waybill_no,Class,Userid) values(20180401,755200201,'文件','A001')")
curs.execute("insert into waybill_constype(Consign_day,waybill_no,Class,Userid) values(20180403,755200202,'娱乐','A001')")
curs.execute("insert into waybill_constype(Consign_day,waybill_no,Class,Userid) values(20180405,755200203,'食品','A001')")
curs.execute("insert into waybill_constype(Consign_day,waybill_no,Class,Userid) values(20180407,755299911,'文件','A001')")
curs.execute("insert into waybill_constype(Consign_day,waybill_no,Class,Userid) values(20180401,755200204,'娱乐','A003')")
curs.execute("insert into waybill_constype(Consign_day,waybill_no,Class,Userid) values(20180401,755200205,'文件','')")
curs.execute("insert into waybill_constype(Consign_day,waybill_no,Class,Userid) values(20180401,755200207,'食品','A002')")
curs.execute("insert into waybill_constype(Consign_day,waybill_no,Class,Userid) values(20180411,755288819,'文件','A002')")
curs.execute("insert into waybill_constype(Consign_day,waybill_no,Class,Userid) values(20180401,755200247,'食品','A002')")
curs.execute("insert into waybill_constype(Consign_day,waybill_no,Class,Userid) values(20180401,755200241,'文件','A003')")
curs.execute("insert into waybill_constype(Consign_day,waybill_no,Class,Userid) values(20180403,755200232,'娱乐','A003')")
curs.execute("insert into waybill_constype(Consign_day,waybill_no,Class,Userid) values(20180405,755200233,'食品','A003')")
curs.execute("insert into waybill_constype(Consign_day,waybill_no,Class,Userid) values(20180507,755299931,'文件','A004')")
curs.execute("insert into waybill_constype(Consign_day,waybill_no,Class,Userid) values(20180501,755200234,'娱乐','A003')")
curs.execute("insert into waybill_constype(Consign_day,waybill_no,Class,Userid) values(20180501,755200235,'文件','')")
curs.execute("insert into waybill_constype(Consign_day,waybill_no,Class,Userid) values(20180501,755200237,'食品','A004')")
curs.execute("insert into waybill_constype(Consign_day,waybill_no,Class,Userid) values(20180511,755288829,'文件','A002')")
curs.execute("insert into waybill_constype(Consign_day,waybill_no,Class,Userid) values(20180501,755200217,'食品','A002')")
curs.execute("insert into waybill_constype(Consign_day,waybill_no,Class,Userid) values(20190401,755200234,'娱乐','A003')")
curs.execute("insert into waybill_constype(Consign_day,waybill_no,Class,Userid) values(20190401,7552002