从两个表中获取数据以生成事件日历 [英] Get data from two tables to make an event-calendar
本文介绍了从两个表中获取数据以生成事件日历的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有两个表
表用户
:
user_id Name
1 John
2 Alice
3 Tom
4 Charles
表活动
:
id event_id start_date end_date user_id
1 1 2013-03-02 2013-03-03 1
2 2 2013-03-02 2013-03-03 3
3 3 2013-03-04 2013-03-04 1
4 2 2013-03-10 2013-03-15 2
我已经创建了输入部分,并且不能在同一天为同一用户创建两个事件(不重叠)。
I already made the "input" part and is not possible to have two events for the same user in the same day (no overlapping).
我提供月份和年作为变量。
我想有一个mysql查询其结果如下:
I would like to have a mysql query with results like this:
user_id 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
1 0 1 1 3 0 0 0 0 0 0 0 0 0 0 0
2 0 0 0 0 0 0 0 0 0 2 2 2 2 2 2
3 0 2 2 0 0 0 0 0 0 0 0 0 0 0 0
谢谢!
推荐答案
UPDATED
SELECT user_id,
COALESCE(MIN(CASE WHEN 1 BETWEEN DAY(start_date) AND DAY(end_date) THEN event_id END), 0) `1`,
COALESCE(MIN(CASE WHEN 2 BETWEEN DAY(start_date) AND DAY(end_date) THEN event_id END), 0) `2`,
COALESCE(MIN(CASE WHEN 3 BETWEEN DAY(start_date) AND DAY(end_date) THEN event_id END), 0) `3`,
COALESCE(MIN(CASE WHEN 4 BETWEEN DAY(start_date) AND DAY(end_date) THEN event_id END), 0) `4`,
COALESCE(MIN(CASE WHEN 5 BETWEEN DAY(start_date) AND DAY(end_date) THEN event_id END), 0) `5`,
COALESCE(MIN(CASE WHEN 6 BETWEEN DAY(start_date) AND DAY(end_date) THEN event_id END), 0) `6`,
COALESCE(MIN(CASE WHEN 7 BETWEEN DAY(start_date) AND DAY(end_date) THEN event_id END), 0) `7`,
COALESCE(MIN(CASE WHEN 8 BETWEEN DAY(start_date) AND DAY(end_date) THEN event_id END), 0) `8`,
COALESCE(MIN(CASE WHEN 9 BETWEEN DAY(start_date) AND DAY(end_date) THEN event_id END), 0) `9`,
COALESCE(MIN(CASE WHEN 10 BETWEEN DAY(start_date) AND DAY(end_date) THEN event_id END), 0) `10`,
COALESCE(MIN(CASE WHEN 11 BETWEEN DAY(start_date) AND DAY(end_date) THEN event_id END), 0) `11`,
COALESCE(MIN(CASE WHEN 12 BETWEEN DAY(start_date) AND DAY(end_date) THEN event_id END), 0) `12`,
COALESCE(MIN(CASE WHEN 13 BETWEEN DAY(start_date) AND DAY(end_date) THEN event_id END), 0) `13`,
COALESCE(MIN(CASE WHEN 14 BETWEEN DAY(start_date) AND DAY(end_date) THEN event_id END), 0) `14`,
COALESCE(MIN(CASE WHEN 15 BETWEEN DAY(start_date) AND DAY(end_date) THEN event_id END), 0) `15`
FROM
(
SELECT id, event_id,
CASE WHEN start_date < '2013-03-01' THEN '2013-03-01' ELSE start_date END start_date,
CASE WHEN end_date > '2013-03-31' THEN '2013-03-31' ELSE end_date END end_date,
user_id
FROM events
WHERE (start_date >= '2013-03-01' AND end_date <= '2013-03-31')
OR (start_date < '2013-03-01' AND end_date > '2013-03-31')
OR (start_date < '2013-03-01' AND end_date BETWEEN '2013-03-01' AND '2013-03-31')
OR (start_date BETWEEN '2013-03-01' AND '2013-03-31' AND end_date > '2013-03-31')
) q
GROUP BY user_id
输出:
| USER_ID | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 |
-----------------------------------------------------------------------------
| 1 | 0 | 1 | 1 | 3 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2 | 2 | 2 | 2 | 2 | 2 |
| 3 | 0 | 2 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
这是 SQLFiddle 演示
Here is SQLFiddle demo
这篇关于从两个表中获取数据以生成事件日历的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文