数据库多个表的数据导出

  因为做一些商场某个会员今年的消费分析,所以对sql中时间的获取进行了判断。

兄弟部门需要做半年业务总结, 需要研发部门提供一份详细的原始数据. 以便于他们进行业务分析. 记录一次数据导出的过程. (以前一直在做移动端开发, 现在开始慢慢接触数据库的工作.)

 例如获取今年(即当前年的第一天到昨天0时之间)的消费总额。


 如果需要时间戳转换,用UNIX_TIMESTAMP()函数。

要导出的数据要求如下

 

流水号, 工单号, 工单总额,提单时间,状态, 审批时间, 会员姓名, 会员等级, 手机号码, 开卡时间. 会员卡号, 消费项目 , 消费车辆, 分店名 ,保险开始时间, 保险结束时间 , 车型, 购车时间, 客户地址,

 一 、下面是一些mysql的时间获取语句:


#当年第一天:
SELECT DATE_SUB(CURDATE(),INTERVAL dayofyear(now())-1 DAY);

#当年最后一天:
SELECT concat(YEAR(now()),'-12-31');  

#当前week的第一天:  
select date_sub(curdate(),INTERVAL WEEKDAY(curdate()) + 1 DAY);

#当前week的最后一天:  
select date_sub(curdate(),INTERVAL WEEKDAY(curdate()) - 5 DAY);

#前一week的第一天:  
select date_sub(curdate(),INTERVAL WEEKDAY(curdate()) + 8 DAY);

#前一week的最后一天:  
select date_sub(curdate(),INTERVAL WEEKDAY(curdate()) + 2 DAY);

#前两week的第一天:  
select date_sub(curdate(),INTERVAL WEEKDAY(curdate()) + 15 DAY);

#前两week的最后一天:  
select date_sub(curdate(),INTERVAL WEEKDAY(curdate()) + 9 DAY);

#当前month的第一天:  
SELECT concat(date_format(LAST_DAY(now()),'%Y-%m-'),'01');

#当前month的最后一天:  
SELECT  LAST_DAY(now());

#前一month的第一天:  
SELECT concat(date_format(LAST_DAY(now() - interval 1 month),'%Y-%m-'),'01');

#前一month的最后一天:  
SELECT LAST_DAY(now() - interval 1 month);

#前两month的第一天:  
SELECT concat(date_format(LAST_DAY(now() - interval 2 month),'%Y-%m-'),'01');

#前两month的最后一天:  
SELECT  LAST_DAY(now() - interval 2 month);

#当前quarter的第一天:  
select concat(date_format(LAST_DAY(MAKEDATE(EXTRACT(YEAR FROM  CURDATE()),1) + interval QUARTER(CURDATE())*3-3 month),'%Y-%m-'),'01'); 

#当前quarter的最后一天:  
select LAST_DAY(MAKEDATE(EXTRACT(YEAR  FROM CURDATE()),1) + interval QUARTER(CURDATE())*3-1 month);

#前一quarter的第一天:  
select concat(date_format(LAST_DAY(MAKEDATE(EXTRACT(YEAR FROM CURDATE()),1) + interval QUARTER(CURDATE())*3-6 month),'%Y-%m-'),'01');

#前一quarter的最后一天:  
select  LAST_DAY(MAKEDATE(EXTRACT(YEAR FROM CURDATE()),1) + interval QUARTER(CURDATE())*3-4 month);

#前两quarter的第一天:  
select concat(date_format(LAST_DAY(MAKEDATE(EXTRACT(YEAR FROM CURDATE()),1) + interval QUARTER(CURDATE())*3-9 month),'%Y-%m-'),'01');

#前两quarter的最后一天:  
select LAST_DAY(MAKEDATE(EXTRACT(YEAR FROM CURDATE()),1) + interval QUARTER(CURDATE())*3-7 month);

sql 语句

 

SELECT  r.SERIAL_NUMBER AS 流水号, 
        r.WORK_ORDER_NUMBER AS 工单号 , 
        r.WORK_ORDER_MONEY AS 工单总额,
        CONVERT(varchar(100), r.SUBMIT_DATE, 23) AS 提单时间,
        r.STATE AS 状态,
        CONVERT(varchar(100),r.OPERATE_DATE) AS 审批时间, 
        m.USER_NAME AS 姓名,
        m.LEVEL AS 会员等级,
        m.PHONE AS 手机号码,
        CONVERT(varchar(100), m.CREATE_DATE, 23) AS 开卡时间,
        m.CARD_NUMBER AS 会员卡号,
        r.CONSUMED_ITEM AS 消费项目,
        ci.LICENSE_PLATE AS 消费车辆,
        company.COMPANY_NAME AS 分店名,
        CONVERT(varchar(100), ci.IS_START_DATE, 23) AS 保险开始时间,
        CONVERT(varchar(100), ci.IS_OVER_DATE, 23) AS 保险结束时间,
        ci.CAR_MODEL AS 车型,
        CONVERT(varchar(100), ci.BUY_DATE, 23) AS 购车时间,
        customer.ADDRESS AS 客户地址
FROM
        CM_MEMBER_CARD_CONSUME_RECORD r 
        INNER JOIN CM_MEMBER_CARD m ON r.MEMBER_CARD_ID = m.ID
        LEFT JOIN CM_MEMBER_CARD_CONSUME_RECORD_CAR_RELATION rcr ON rcr.CONSUME_RECORD_ID = r.ID
        LEFT JOIN CM_CARINFO ci ON rcr.CAR_ID = ci.ID
        LEFT JOIN CM_COMPANY company ON company.COMPANY_ID=m.COMPANY_ID 
        LEFT JOIN CM_CUSTOMER customer ON customer.CARD_ID=ci.CARD_ID
        WHERE company.COMPANY_ID = 'gzjlqcmyyxgs00000006'
        ORDER BY r.COMPANY_ID ,r.OPERATE_DATE desc

二、举几个某会员的消费分析的sql语句:

语句解读:

-- 今年的消费总额
SELECT IFNULL(SUM(a.trade_amount)/100,0)
FROM mob_checkout_counter.checkout_record a  -- 消费记录表
WHERE card_code = '某会员的标识'
AND checkout_status = ‘消费状态’AND trade_time >= UNIX_TIMESTAMP(DATE_SUB(CURDATE(),INTERVAL dayofyear(now())-1 DAY)) -- 当前年的第一天的时间戳
AND trade_time <= UNIX_TIMESTAMP(CAST(SYSDATE()AS DATE) - INTERVAL 1 DAY) -- 昨天的0时的时间戳

-- 今年的消费笔数
SELECT COUNT(*)
FROM mob_checkout_counter.checkout_record a 
WHERE card_code = '2396998881100009965'
AND checkout_status = 1
AND trade_time >= UNIX_TIMESTAMP(DATE_SUB(CURDATE(),INTERVAL dayofyear(now())-1 DAY))
AND trade_time <= UNIX_TIMESTAMP(CAST(SYSDATE()AS DATE) - INTERVAL 1 DAY)

-- 今年的客单价
SELECT IFNULL(SUM(a.trade_amount)/100,0)/COUNT(*) 
FROM mob_checkout_counter.checkout_record a
WHERE card_code = '2396998881100009965'
AND checkout_status = 1
AND trade_time >= UNIX_TIMESTAMP(DATE_SUB(CURDATE(),INTERVAL dayofyear(now())-1 DAY))
AND trade_time <= UNIX_TIMESTAMP(CAST(SYSDATE()AS DATE) - INTERVAL 1 DAY)
  • 别名: AS 后面 可以跟别名 如: r.SERIAL_NUMBER AS 流水号
    也可以省略 AS 如: r.SERIAL_NUMBER AS 流水号

  • 左链接: LEFT JOIN (ON) , 左边的表全部显示, 右边的表可能只显示一部分

  • 右链接: RIGHT JOIN (ON),

  • 内链接: INNER JOIN (ON),

  • WHERE : 条件筛选

  • ORDER BY: 排序方式, 默认是升序, DESC 是逆序
    条件语句自定义赋值:
    case when 条件1 then 结果1
    when 条件2 then 结果2
    when 条件3 then 结果3
    else 结果4
    end

 

本文由金沙官网线上发布于数据库,转载请注明出处:数据库多个表的数据导出

您可能还会对下面的文章感兴趣: