विशिष्ट तालिका के लिए उसी तालिका के भीतर दिनांक सीमा ओवरलैप ढूंढें, MySQL




database alias (2)

यहां पहला भाग है: प्रति उपयोगकर्ता ओवरलैपिंग कार ...

SQLFiddle - सहसंबद्ध प्रश्न और प्रश्न जुड़ें

दूसरा हिस्सा - एक समय में एक से अधिक उपयोगकर्ता एक ही बार में: SQLFiddle - सहसंबद्ध प्रश्न और जुड़ें प्रश्न नीचे प्रश्न ...

मैं सहसंबद्ध प्रश्नों का उपयोग करता हूं:

आपको यूज़र आईडी और 'कार' पर अनुक्रमण की आवश्यकता होगी। हालांकि - कृपया यह देखने के लिए 'योजना समझा' की जांच करें कि यह कैसे डाटाबेस तक पहुंच रहा है। और बस इसे प्रयास करें :)

उपयोगकर्ता प्रति ओवरलैपिंग कारें

पूछताछ:

SELECT `allCars`.`userid`  AS `allCars_userid`, 
       `allCars`.`car`     AS `allCars_car`, 
       `allCars`.`From`    AS `allCars_From`, 
       `allCars`.`To`      AS `allCars_To`,
       `allCars`.`tableid` AS `allCars_id`
 FROM  
       `cars` AS `allCars`
 WHERE 
     EXISTS  
         (SELECT 1       
          FROM `cars` AS `overlapCar`            
          WHERE 
               `allCars`.`userid` = `overlapCar`.`userid` 
           AND `allCars`.`tableid` <> `overlapCar`.`tableid`          
           AND NOT (   `allCars`.`From`  >= `overlapCar`.`To`      /* starts after outer ends  */  
                    OR `allCars`.`To`    <= `overlapCar`.`From`))  /* ends before outer starts */
 ORDER BY
        `allCars`.`userid`, 
        `allCars`.`From`, 
        `allCars`.`car`;      

परिणाम:

allCars_userid  allCars_car  allCars_From  allCars_To  allCars_id  
--------------  -----------  ------------  ----------  ------------
             1  Navara       2015-03-01    2015-03-31             3
             1  GTR          2015-03-28    2015-04-30             4
             1  Skyline      2015-04-29    2015-05-31             9
             2  Aygo         2015-03-01    2015-03-31             7
             2  206          2015-03-29    2015-04-30             8
             2  Skyline      2015-04-29    2015-05-31            10

यह काम क्यों करता है? या इसके बारे में मैं कैसे सोचता हूं:

मैं सहसंबद्ध क्वेरी का उपयोग करता हूं इसलिए मेरे पास निपटने के लिए डुप्लिकेट नहीं हैं और संभवतः मेरे लिए समझना सबसे आसान है। क्वेरी को व्यक्त करने के अन्य तरीके हैं प्रत्येक के फायदे और कमियां हैं मुझे कुछ चाहिए जो मैं आसानी से समझ सकता हूँ

आवश्यकता: प्रत्येक उपयोगकर्ता के लिए यह सुनिश्चित करें कि उनके पास एक ही समय में दो या अधिक कार नहीं हैं।

इसलिए, प्रत्येक उपयोगकर्ता अभिलेख (ऑलकार्स) के लिए पूरी तालिका (ओवरलैपकार्ड) को देखने के लिए जांचें कि क्या आपको एक अलग रिकॉर्ड मिल सकता है जो वर्तमान रिकॉर्ड के समय के लिए ओवरलैप करता है। अगर हम एक मिलते हैं तो वर्तमान रिकॉर्ड का चयन करें (सभी कार में)

इसलिए ओवरलैप चेक है:

  • allCars userid और overLap userid एक समान होना चाहिए
  • सभी कार कार रिकॉर्ड और overlap कार रिकॉर्ड अलग होना चाहिए
  • allCars समय सीमा और ओवरलैप समय सीमा को ओवरलैप करना चाहिए।

    समय सीमा की जांच:

    अतिव्यापी समय की जांच करने के बजाय सकारात्मक परीक्षण का उपयोग करें सबसे आसान तरीका यह है, यह जांचना है कि यह ओवरलैप नहीं है, और इसे लागू NOT करता है।

एक ही समय में एक से अधिक उपयोगकर्ता के साथ एक कार ...

पूछताछ:

SELECT  `allCars`.`car`     AS `allCars_car`,
        `allCars`.`userid`  AS `allCars_userid`,  
        `allCars`.`From`    AS `allCars_From`, 
        `allCars`.`To`      AS `allCars_To`, 
        `allCars`.`tableid` AS `allCars_id`

 FROM  
       `cars` AS `allCars`
 WHERE 
     EXISTS  
        (SELECT 1       
         FROM `cars` AS `overlapUser`            
         WHERE 
              `allCars`.`car` = `overlapUser`.`car` 
          AND `allCars`.`tableid` <> `overlapUser`.`tableid`          
          AND NOT (    `allCars`.`From`  >= `overlapUser`.`To`       /* starts after outer ends  */  
                   OR  `allCars`.`To`    <= `overlapUser`.`From`))  /* ends before outer starts */
 ORDER BY
        `allCars`.`car`,      
        `allCars`.`userid`, 
        `allCars`.`From`;

परिणाम:

allCars_car  allCars_userid  allCars_From  allCars_To    allCars_id  
-----------  --------------  ------------  ----------  ------------
Skyline                   1  2015-04-29    2015-05-31             9
Skyline                   2  2015-04-29    2015-05-31            10

संपादित करें:

टिप्पणियों को ध्यान में रखते हुए, @ फिलिपक्सी द्वारा, समय के बारे में, 'मैं या इससे अधिक के बराबर' चेक की आवश्यकता है, जो मैंने यहां कोड अपडेट किया है। मैंने SQLFiddles परिवर्तित नहीं किया है।

मैं किसी भी तरह से एक MySQL विशेषज्ञ नहीं हूँ, इसलिए मैं इस मामले पर किसी भी मदद की तलाश कर रहा हूं।

मुझे एक साधारण परीक्षण (सिद्धांत रूप में) करने की आवश्यकता है, मेरे पास यह (सरलीकृत) तालिका है:

tableid | userid  | car      | From        | To
--------------------------------------------------------
1       | 1       |  Fiesta  |  2015-01-01 | 2015-01-31
2       | 1       |  MX5     |  2015-02-01 | 2015-02-28
3       | 1       |  Navara  |  2015-03-01 | 2015-03-31
4       | 1       |  GTR     |  2015-03-28 | 2015-04-30
5       | 2       |  Focus   |  2015-01-01 | 2015-01-31
6       | 2       |  i5      |  2015-02-01 | 2015-02-28
7       | 2       |  Aygo    |  2015-03-01 | 2015-03-31
8       | 2       |  206     |  2015-03-29 | 2015-04-30
9       | 1       |  Skyline |  2015-04-29 | 2015-05-31
10      | 2       |  Skyline |  2015-04-29 | 2015-05-31

मुझे यहां दो चीजों को ढूँढ़ने की आवश्यकता है:

  1. अगर किसी भी उपयोगकर्ता की एक दिन से अधिक की अपनी कार की कार्यकाल में ओवरलैप हो जाता है (असाइनमेंट का अंत उसी दिन हो सकता है जैसे नया काम शुरू हो)।
  2. क्या कोई भी दो प्रयोक्ताओं ने एक ही कार को उसी तिथि पर सौंपने का प्रयास किया, या एक ही कार पर तिथि सीमा उनके लिए ओवरलैप करता है

इसलिए क्वेरी (या क्वेरी) मैं उन पंक्तियों को लौटना चाहूंगा:

tableid | userid  | car      | From        | To
--------------------------------------------------------
3       | 1       |  Navara  |  2015-03-01 | 2015-03-31
4       | 1       |  GTR     |  2015-03-28 | 2015-04-30
7       | 2       |  Aygo    |  2015-03-01 | 2015-03-31
8       | 2       |  206     |  2015-03-29 | 2015-04-30
9       | 1       |  Skyline |  2015-04-29 | 2015-05-31
10      | 2       |  Skyline |  2015-04-29 | 2015-05-31 

मुझे लगता है कि मैं दीवार के सामने मेरे सिर को बाँध रहा हूं, मुझे अलग पूछताछों में ये तुलना करने में सक्षम होने से खुशी होगी। मुझे उन्हें एक तालिका में प्रदर्शित करने की जरूरत है लेकिन मैं फिर परिणामों के साथ जुड़ सकता हूं।

मैंने अनुसंधान और परीक्षण के कुछ घंटों के लिए किया है, लेकिन मैं चाहता हूं कि नतीजे के पास कहीं न कहीं।

उपरोक्त परीक्षण डेटा के साथ SQLFiddle

मैंने इन पोस्ट्स बीटीडब्ल्यू की कोशिश की है (वे वास्तव में मेरी क्या ज़रूरत नहीं थीं, लेकिन काफी करीब थे, या तो मैंने सोचा):

समान तालिका में दो दिनांक सीमाओं की तुलना करना

एक ही तालिका से टेक्स्ट कॉलम के मूल्यों की तुलना कैसे करें

यह सबसे निकटतम समाधान था जो मुझे मिल सकता था, लेकिन जब मैंने इसे एक मेज पर ले जाया था (खुद तालिका में शामिल हो रहा था) मैं पागल परिणाम प्राप्त कर रहा था: समय ओवरलैप के लिए एक टेबल की जाँच करना?

संपादित करें

एक अस्थायी समाधान के रूप में मैंने एक अलग दृष्टिकोण को रूपांतरित किया है, जैसा कि मेरे शोध (ऊपर) के दौरान मिल चुके पदों के समान है I मैं अब जांचूंगा कि नई कार किराए पर लेने / असाइनमेंट की तारीख तालिका के भीतर किसी भी तारीख सीमा के साथ ओवरलैप हो। यदि ऐसा है तो मैं उन पंक्तियों के आईडी (एस) को सहेज कर दूँगा जो तारीख को ओवरलैप करती है। इस तरह कम से कम मैं ओवरलैप ध्वज करने में सक्षम हो जाएगा और उपयोगकर्ता को फ्लैग की गई पंक्तियों को देखने और किसी भी ओवरलैप को मैन्युअल रूप से हल करने की अनुमति देगा।

इस के साथ उनकी मदद करने वाले हर किसी के लिए धन्यवाद, मैं चुने हुए एक (अगले 24 घंटों में) फिलिप के उत्तर को ध्वज करेगा जब तक कि कोई इस को हासिल करने का बेहतर तरीका न हो। मुझे इसमें कोई संदेह नहीं है कि उनके जवाब के बाद मैं अंततः परिणामों की जरूरत के मुताबिक पहुंच सकूंगा फिलहाल मुझे ऐसे किसी भी समाधान को अपनाने की जरूरत है, जो काम करती है क्योंकि मुझे अगले कुछ दिनों में अपनी परियोजना पूरी करने की जरूरत है, इसलिए दृष्टिकोण में बदलाव।

# 2 संपादित करें

दोनों उत्तर शानदार हैं और किसी को भी, जो इस पोस्ट को उसी मुद्दे के रूप में देखता है जैसा मैंने किया था, उन्हें दोनों पढ़ा और मिथ्या को देखो! :) बहुत बढ़िया दिमाग काम उन में चला गया! अस्थायी रूप से मुझे मेरा समाधान # 1 में सम्मिलित करने वाले समाधान के साथ जाना था, लेकिन मैं आरआईएन विन्सेन्ट दृष्टिकोण के साथ जाने के लिए अपने प्रश्नों का अनुकूलन कर रहा हूं + @ फिलिपीसी संपादन / शुरुआती एक दिवसीय ओवरलैप की अनदेखी के बारे में टिप्पणियां


प्रत्येक इनपुट और आउटपुट तालिका के लिए इसका अर्थ मिलता है । Ie एक बयान टेम्पलेट, स्तंभ नाम, उर्फ विधेय द्वारा पैरामीटर, कि एक पंक्ति एक सच्ची या झूठी कथन में उर्फ प्रस्तावित करता है । एक तालिका पंक्तियों को रखती है जो इसकी वास्तविकता में एक वास्तविक प्रस्ताव बनाते हैं। Ie पंक्तियाँ जो एक सच्चे प्रस्ताव बनाते हैं, एक तालिका में जाते हैं और पंक्तियाँ जो झूठी प्रस्ताव बनाते हैं, बाहर रहती हैं। आपकी इनपुट तालिका के लिए उदाहरण:

rental [tableid] was user [userid] renting car [car] from [from] to [to]

फिर इनपुट तालिका के मामले में आउटपुट तालिका का अर्थ विधेयिक होता है। अपने 1 और 2 जैसे विवरणों का उपयोग करें:

  1. अगर किसी भी उपयोगकर्ता की एक दिन से अधिक की अपनी कार की कार्यकाल में ओवरलैप हो जाता है (असाइनमेंट का अंत उसी दिन हो सकता है जैसे नया काम शुरू हो)।

इसके बजाय, उस विधेय को ढूंढें कि एक मनमाना पंक्ति बताती है कि तालिका में कब:

rental [tableid] was user [user] renting car [car] from [from] to [to]
    in self-conflict with some other rental

डीबीएमएस के लिए यह सच्चाई बनाने वाली पंक्तियों की गणना करने के लिए हमें इसे हमारे दिए गए विधेयकों और लिखित शब्दों और शर्तों के संदर्भ में व्यक्त करना चाहिए:

-- query result holds the rows where
FOR SOME t2.tableid, t2.userid, ...:
    rental [t1.tableid] was user [t1.userid] renting car [t1.car] from [t1.from] to [t1.to]
AND rental [t2.tableid] was user [t2.userid] renting car [t2.car] from [t2.from] to [t2.to]
AND [t1.userid] = [t2.userid] -- userids id the same users
AND [t1.to] > [t2.from] AND ...  -- tos/froms id intervals with overlap more than one day
...

(एक एसक्यूएल SELECT कथन के अंदर JOIN एड टेबल के क्रॉस प्रॉडक्ट में फॉर्म alias column कॉलम नाम हैं। कॉलम के नामों में एक अन्य वर्ण की अनुमति दी जाती है। अंत में SELECT क्लॉज alias ।)

हम क्वेरी क्वेरी को एक एसक्यूएल क्वेरी में कनवर्ट करते हैं जो पंक्तियों की गणना करता है जो इसे सही बनाती हैं:

  • एक तालिका का विचलित तालिका उपनाम द्वारा प्रतिस्थापित किया जाता है
  • उसी ख़राब / तालिका का उपयोग करने के लिए कई बार उपनाम बनाते हैं
  • old वस्तु में new old कॉलम बदलना AND old = new
  • AND पूर्वजों को JOIN द्वारा प्रतिस्थापित किया जाता है।
  • OR भविष्यवाणियों को UNION द्वारा प्रतिस्थापित किया जाता है
  • AND NOT का AND NOT , MINUS , MINUS या उचित LEFT JOIN द्वारा प्रतिस्थापित किया जाता है।
  • AND condition WHERE या condition हो जाती है।
  • FOR SOME columns to drop लिए एक वास्तविकता के FOR SOME या जब columns to drop , तो SELECT DISTINCT columns to keep SELECT DISTINCT
  • आदि। (इसे देखें।)

इसलिए (अंडाकार को पूरा करना):

SELECT DISTINCT t1.*
FROM t t1 JOIN t t2
ON t1.userid = t1.userid -- userids id the same users
WHERE t1.to > t2.from AND t2.to > t1.from -- tos/froms id intervals with overlap more than one day
AND t1.tableid <> t2.tableid -- tableids id different rentals
  1. क्या कोई भी दो प्रयोक्ताओं ने एक ही कार को उसी तिथि पर सौंपने का प्रयास किया, या एक ही कार पर तिथि सीमा उनके लिए ओवरलैप करता है

तालिका में जब एक मनमाना पंक्ति बताती है, उस ख़ुफ़िया को ढूंढना:

rental [tableid] was user [user] renting car [car] from [from] to [to]
    in conflict with some other user's rental

हमारे दिए गए विधेयकों और शब्दों के संदर्भ में

-- query result holds the rows where
FOR SOME t2.*
    rental [t1.tableid] was user [t1.userid] renting car [t1.car] from [t1.from] to [t1.to]
AND rental [t2.tableid] was user [t2.userid] renting car [t2.car] from [t2.from] to [t2.to]
AND [t1.userid] <> [t2.userid] -- userids id different users
AND [t1.car] = [t2.car] -- .cars id the same car
AND [t1.to] >= [t2.from] AND [t2.to] >= [t1.from] -- tos/froms id intervals with any overlap
AND [t1.tableid] <> [t2.tableid] -- tableids id different rentals

1 और 2 की भविष्यवाणी के लिए प्रश्नों का UNION उन पंक्तियों को देता है, जिनके लिए predicate 1 predicate 2 OR predicate 2

पूर्वानुमानों को व्यक्त करने की कोशिश करें - पंक्तियों में कहानियां कहां हैं - यदि केवल सहज (उप) क्वेरी के लक्ष्य के लिए

पीएस। यह हमेशा अच्छा होता है कि कंडीशनिंग और गैर-बढ़त वाले मामलों की जानकारी हमेशा सही और गलत होने के लिए होती है। उदाहरण 1 जीटीआर के साथ क्वेरी 1 की कोशिश करें, जो 31 वें दिन से शुरू होता है, केवल एक दिन का ओवरलैप, जो कि स्वयं-संघर्ष नहीं होना चाहिए।

डुप्लिकेट पंक्तियों के साथ पीपीएस की पूछताछ के अनुसार, नाउल्स के पास काफी जटिल क्वेरी अर्थ हैं। यह कहना कठिन है कि जब ट्यूपल एक टेबल में रहता है या एक टेबल से बाहर रहता है और कितनी बार मेरे पत्राचार के अनुसार सरल सहज ज्ञान युक्त अर्थ के लिए उनके पास डुप्लिकेट नहीं हो सकते यहाँ SQL दुर्भाग्य से संबंधपरक मॉडल से अलग है अभ्यास में लोगों को मुहावरों पर भरोसा है जब गैर-विशिष्ट पंक्तियों की अनुमति होती है और वे सीमाओं पर निर्भर करते हैं क्योंकि बाधाओं के कारण अलग-अलग होते हैं। उदाहरण के लिए अद्वितीय प्रति स्तंभों, पीके और एफके से मिलते-जुलते कॉलम उदाहरण: एक अंतिम अंतर चरण केवल एक संस्करण से अलग समय पर काम कर रहा है जिसे इसकी आवश्यकता नहीं है; समय हो सकता है या शायद एक महत्वपूर्ण कार्यान्वयन समस्या जो कि किसी दिए गए विधेय / परिणाम के लिए चुना गया वाक्यांश को प्रभावित करता है।







intervals