zsjz-ai/sql/aasd.sql

SELECT id,
       excel_file_id AS fileId,
       sheet_id      AS sheetId,
       xm            AS personName,
       sfzmhm        AS personCertNo,
       sfzmhm        AS personCertName,
       xb            AS gender,
       zzssxq        AS addressCity,
       zzxq          AS addressDetail,
       ldmc          AS hotelName,
       ldxxdz        AS hotelAddress,
       rzfh          AS roomNo,
       rzsj          AS inDateTime,
       tfsj          AS outDateTime
FROM gat_lgzs_1683362389912;

SELECT *
FROM ` clue_analysis_jw `.` gat_tzfw_1683362389945 `
LIMIT 0,1000;
SELECT *
FROM ` clue_analysis_jw `.` gat_czrk_1683362389811 `
LIMIT 0,1000;

SELECT id,
       excel_file_id AS fileId,
       sheet_id      AS sheetId,
       cjrq          as flightDateTime,
       ddrq          as depDateTime,
       hbh           as flightNo,
       sfzm          as origin,
       zdzm          as destination,
       zwh           as seatNo,
       ch            as cabinClass,
       zjlx          as personCertType,
       sfzh          as personCertNo,
       txrxm         as companionName

FROM ` clue_analysis_jw `.` gat_thbfw_1683362389924 `
LIMIT 0,1000;



SELECT id,
       excel_file_id AS fileId,
       sheet_id      AS sheetId,
       cc            as trainNo,
       djsj          as depDateTime,
       qszm          as origin,
       zdzm          as destination,
       cxh           as carNo,
       zwh           as seatNo,
       xm            as personName,
       txrxm         as companionName,
       zjlx          as personCertType,
       sfzh          as personCertNo


FROM ` clue_analysis_jw `.` gat_thcfw_1683362389934 `
LIMIT 0,1000;


SELECT id,
       excel_file_id AS fileId,
       sheet_id      AS sheetId,
       xm            as personName,
       zjhm          as personCertNo,
       ch            as trainNo,
       fz            as origin,
       dz            as destination,
       fcrq          as depDateTime,
       cxh           as carNo,
       zwh           as seatNo,
       cpzt          as ticketStatus,
       tprq          as refundDate,
       gqrq          as changeDate

FROM ` clue_analysis_jw `.` gat_tldp_1683362389863 `
LIMIT 0,1000;


SELECT id,
       excel_file_id AS fileId,
       sheet_id      AS sheetId,
       ddcfsj        as depDateTime,
       ddddsj        as arrDateTime,
       qfjcdm        as depAirport,
       ddjcdm        as arrAirport,
       lkzwm         as personName,
       lkywx         as surname,
       lkywm         as personNameEn,
       zjhm          as personCertNo,
       hbh           as flightNo,
       cyhkgsdm      as carrier,
       jlbh          as pnr,
       hbh           as ticketNo,
       pj            as fare,
       cprq          as issueDate,
       hdcw          as agent,
       ph            as flightClass,
       hdzt          as status

FROM ` clue_analysis_jw `.` gat_mhdp_1683362389874 `
LIMIT 0,1000;


SELECT id,
       excel_file_id AS fileId,
       sheet_id      AS sheetId,
       hbh           as flightNo,
       hbhz          as flightSuffix,
       hkgsdm        as carrier,
       hbrq          as flightDateTime,
       lgsj          as depDateTime,
       jgsj          as arrDateTime,
       qfgzszdm      as depCode,
       ddhzszdm      as arrCode,
       lkx           as surname,
       lkxm          as givenName,
       lkzjm         as middleName,
       lkzwxm        as personName,
       lkzwxx        as personNameCn,
       xb            as gender,
       csrq          as birthDate,
       csd           as birthPlace,
       zjhm          as personCertNo,
       lkzwxx        as seatNo

FROM ` clue_analysis_jw `.` gat_mhlg_1683362389899 `
LIMIT 0,1000;



SELECT id,
       excel_file_id AS fileId,
       sheet_id      AS sheetId,
       bccx,
       bch,
       bclx,
       ccrxm         as personName,
       ccrzjlx       as personCertType,
       cph,
       cpjg,
       cplx,
       cpzt,
       fcsj          as depDateTime,
       gpcgsj        as buyDateTime,
       jsdxsjhm,
       mddmc         as destination,
       qpsj,
       sfczmc        as origin,
       tpje,
       tpsj          as refundDateTime,
       xsqdbm,
       zjhm          as personCertNo,
       zwh


FROM ` clue_analysis_jw `.` jt_ygpw_1683363125047 `
LIMIT 0,1000;



SELECT id,
       excel_file_id AS fileId,
       sheet_id      AS sheetId,
       tzrxm         AS togetherName,
       tzrsfzh       AS togetherCertNo,
       tzrzjlx       AS togetherCertType,
       tzrxb         AS togetherGender,
       tzrnl         AS togetherAge,
       tzrmz         AS togetherEthnicity,
       tzrhjd        AS togetherOrigin,
       tzrjdmc       AS hotelName,
       tzrfjh        AS roomNo,
       tzrrzsj       AS inDateTime,
       tzrtfsj       AS outDateTime,
       tzlx          AS coType,
       tzcs          AS stayCount,
       bz            AS remark,
       cxrsfzh       AS queryCertNo
FROM gat_tzfw_1683362389945;


truncate hotel_stay_info;
truncate together_live_info;
truncate together_flight_info;
truncate together_train_info;
truncate track_car_ticket_info;
truncate train_ticket_info;
truncate flight_departure_info;
truncate flight_ticket_info;

copy hotel_stay_info from 'C:\Users\cc\Desktop\旅馆住宿.csv';

copy together_live_info from 'C:\Users\cc\Desktop\同住服务.csv';


copy together_flight_info from 'C:\Users\cc\Desktop\gat_thbfw_1683362389924.csv';
copy together_train_info from 'C:\Users\cc\Desktop\gat_thcfw_1683362389934.csv';
copy track_car_ticket_info from 'C:\Users\cc\Desktop\jt_ygpw_1683363125047.csv';
copy train_ticket_info from 'C:\Users\cc\Desktop\gat_tldp_1683362389863.csv';
-- copy flight_departure_info from 'C:\Users\cc\Desktop\gat_mhlg_1683362389899.csv';
copy flight_ticket_info from 'C:\Users\cc\Desktop\gat_mhdp_1683362389874.csv';