mysql - ईवेंट-कैलेंडर बनाने के लिए दो तालिकाओं से डेटा प्राप्त करें




calendar (3)

मेरे पास दो टेबल हैं

तालिका Users :

user_id     Name
1          John
2          Alice
3          Tom
4          Charles

टेबल Events :

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

मैंने पहले से ही "इनपुट" भाग बना दिया है और उसी दिन एक ही उपयोगकर्ता के लिए दो घटनाएं संभव नहीं हैं (ओवरलैपिंग नहीं)।

मैं महीने और वर्ष को चर के रूप में प्रदान करता हूँ

मैं इस तरह से परिणाम के साथ एक mysql क्वेरी करना चाहूंगा:

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

धन्यवाद!


Answers

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 डेमो है


मैंने यह प्रयास किया:

SELECT user_id,
   COALESCE(MIN(CASE WHEN '2013-03-01' BETWEEN start_date AND end_date THEN event_id END), 0) `1`,
   COALESCE(MIN(CASE WHEN '2013-03-02' BETWEEN start_date AND end_date THEN event_id END), 0) `2`,
   COALESCE(MIN(CASE WHEN '2013-03-03' BETWEEN start_date AND end_date THEN event_id END), 0) `3`,
   COALESCE(MIN(CASE WHEN '2013-03-04' BETWEEN start_date AND end_date THEN event_id END), 0) `4`,
   COALESCE(MIN(CASE WHEN '2013-03-05' BETWEEN start_date AND end_date THEN event_id END), 0) `5`,
   COALESCE(MIN(CASE WHEN '2013-03-06' BETWEEN start_date AND end_date THEN event_id END), 0) `6`,
   COALESCE(MIN(CASE WHEN '2013-03-07' BETWEEN start_date AND end_date THEN event_id END), 0) `7`,
   COALESCE(MIN(CASE WHEN '2013-03-08' BETWEEN start_date AND end_date THEN event_id END), 0) `8`,
   COALESCE(MIN(CASE WHEN '2013-03-09' BETWEEN start_date AND end_date THEN event_id END), 0) `9`,
   COALESCE(MIN(CASE WHEN '2013-03-10' BETWEEN start_date AND end_date THEN event_id END), 0) `10`,
   COALESCE(MIN(CASE WHEN '2013-03-11' BETWEEN start_date AND end_date THEN event_id END), 0) `11`,
   COALESCE(MIN(CASE WHEN '2013-03-12' BETWEEN start_date AND end_date THEN event_id END), 0) `12`,
   COALESCE(MIN(CASE WHEN '2013-03-13' BETWEEN start_date AND end_date THEN event_id END), 0) `13`,
   COALESCE(MIN(CASE WHEN '2013-03-14' BETWEEN start_date AND end_date THEN event_id END), 0) `14`,
   COALESCE(MIN(CASE WHEN '2013-03-15' BETWEEN start_date AND end_date THEN event_id END), 0) `15`
FROM events 
GROUP BY user_id

मुझे पता है कि यह एक बहुत पुराना सवाल है, लेकिन मैं कुछ हफ्ते पहले ऐसा कुछ ढूंढ रहा था और मुझे यहां कुछ भी नहीं मिला। तो, मैं क्या उपयोग कर रहा हूँ केंडो शेड्यूलर है । इसमें एक क्षैतिज समूह, लंबवत समूह, समयरेखा और सिग्नलआर के खिलाफ बाध्य जैसी कुछ अन्य रोचक चीजें हैं।

इसके अलावा, फुल कैलेंडर announcement कि यह जल्द ही एक संसाधन / समयरेखा दृश्य होगा । लेकिन शायद यह एक वाणिज्यिक लाइसेंस के तहत जारी किया जाएगा।





mysql calendar