计算天-时-分

SQL查询订单详情示例

转载于 2017-11-06 17:12:00 发布 · 81 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：https://yq.aliyun.com/articles/506967

本文提供了一个SQL查询示例，展示了如何从多个表中联接获取订单相关信息，包括消费代码、交易代码、公司名称等，并计算了订单发出至今的时间差。

SELECT
a.consume_code consumeCode,
s.trans_code transCode,
t.company_name companynName,
a.wlb_code wlbCode,
a.user_name userName,
a.consignee conSignee,
a.cons_city consCity,
FLOOR(
  HOUR (
   TIMEDIFF(NOW(), a.order_date)
  ) / 24
) ex_order_day,
MOD (
  HOUR (
   TIMEDIFF(NOW(), a.order_date)
  ),
  24
) ex_order_hour,
MINUTE (
  TIMEDIFF(NOW(), a.order_date)
) ex_order_minute,
a.order_date orderDate
FROM
rs_sale_order_base a
LEFT JOIN rs_sale_order_service s ON a.consume_code = s.consume_code
LEFT JOIN ru_company_trans t ON s.main_company_code = t.company_code
WHERE
1 = 1
AND a.consume_code NOT IN (
SELECT
  h.consume_code
FROM
  hf_info h

)

本文转自杨海龙的博客博客51CTO博客，原文链接http://blog.51cto.com/7218743/1643552如需转载请自行联系原作者

IT达仁