Mysql查询两张表&以日期方式或用户方式获取数据 [英] Mysql query two tables & get data in date wise or user wise

问题描述

我有两张桌子.第一个包含有关营业时间的信息:

I have two tables. The first one holds information about Hours:

ID   |  activitydate   |  userid  |  phaseid  |  activityid  |  time  |
 3   |  2011-04-11     |    1     |     1     |      1       |  120   |
 4   |  2011-04-12     |    1     |     1     |      1       |  180   |
 5   |  2011-04-12     |    3     |     1     |      1       |  360   |
 6   |  2011-04-22     |    2     |     2     |      3       |  300   |

保存用户数据的第二个表:

The 2nd table holding the data of Users:

  ID   |    userid       |  lastname |
   1   |      abc        |   ABC     |
   2   |      xyz        |   XYZ     |
   3   |      asd        |   ASD     |
   4   |      qwe        |   QWE     |

我需要一个生成以下给定格式的查询:

I need a query which generate below given format:

  Date     | ABC | XYZ | ASD | QWE |
2011-04-11 | 120 |  0  |  0  |  0  |
2011-04-12 | 180 |  0  | 360 |  0  |
2011-04-22 |  0  | 300 |  0  |  0  |

这可以是以下格式:

  User  | 2011-04-11 | 2011-04-12 | 2011-04-13| .... | 2011-04-22 |...
  ABC   |    120     |    180     |     0     | .....|     0      |...
  XYZ   |     0      |     0      |     0     | .....|    300     |...
  ASD   |     0      |    360     |     0     | .....|     0      |...
  QWE   |     0      |     0      |     0     | .....|     0      |...

非常感谢任何帮助！

推荐答案

这样做:

SELECT 
    u.lastname AS `User`
  , SUM(CASE WHEN activitydate='2011-04-01' THEN time ELSE 0 END) AS `2011-04-01`
  , SUM(CASE WHEN activitydate='2011-04-02' THEN time ELSE 0 END) AS `2011-04-02`
  , ...
  , SUM(CASE WHEN activitydate='2011-04-30' THEN time ELSE 0 END) AS `2011-04-30`
FROM Hours h
  JOIN Users u
    ON h.userid = u.userid
WHERE h.activitydate BETWEEN '2011-04-01' AND '2011-04-30'
GROUP BY h.userid
ORDER BY h.userid

这篇关于Mysql查询两张表&以日期方式或用户方式获取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！