sql多表查询以及时间的处理

多表查询

SELECT * FROM t_postulant_files t LEFT JOIN t_file t1 ON t.file_id = t1.id WHERE t.postulant_id =10;

 select t1.id, t1.name, t1.fileurl, t1.filetype, t1.size, t1.bucket, t1.access_type, t1.file_status, t1.user_id, 

t1.type, t1.stuus from t_postulant_files t LEFT JOIN t_file t1 ON t.file_id = t1.id WHERE t.postulant_id = ? 

三表查询：

  t_goods:

 t_order:

 t_user:

 结果：

 SELECT

  t1.id,

  t.time,

  t1.username,

  t1.address,

  t1.tel,

  t1.name,

  t2.name,

  t2.price

FROM t_order t

  INNER JOIN t_user t1

    ON t.user_id = t1.id

  INNER JOIN t_goods t2

    ON t.goods_id = t2.id

where t1.id = 1

查询时间

 

SELECT user_id,user_name ,SUM(billing) FROM sale WHERE user_id = 0 AND diyDate = 2016-09-12

SELECT user_id,user_name ,SUM(billing) FROM sale WHERE DATE_FORMAT(diyDate,'%Y-%m') = '2016-09'

查询当日的数据以及数量：

SELECT

  SUM(1)    accessFromNum,

  DATE_FORMAT(from_date, '%m-%d')    from_date

FROM za_activity_access

WHERE DATE_FORMAT(from_date, '%m-%d') = DATE_FORMAT(NOW(), '%m-%d')

GROUP BY DATE_FORMAT(from_date, '%m-%d')

查询本周的数据以及数量SELECT

  SUM(1)    accessFromNum,

  DATE_FORMAT(from_date, '%U')    from_date

FROM za_activity_access

WHERE DATE_FORMAT(from_date, '%U') = DATE_FORMAT(NOW(), '%U')

GROUP BY DATE_FORMAT(from_date, '%U')