mysql - नेस्टेड VIEW द्वारा अनदेखा आदेश



nested subquery (2)

इस एक के समान प्रश्न: MySQL: एफआरएम क्लॉज सीमा में उपकुंजी के साथ देखें

मेरे पास निम्न shows तालिका है:

DROP TABLE IF EXISTS `shows`;
CREATE TABLE `shows` (
  `show_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `show_type` int(11) unsigned DEFAULT NULL,
  `show_year` int(11) unsigned DEFAULT NULL,
  PRIMARY KEY (`show_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
INSERT INTO `shows` VALUES
(NULL, 1, 2014), -- 1
(NULL, 1, 2015), -- 2
(NULL, 2, 2015), -- 3
(NULL, 2, 2014); -- 4

मैं एक दृश्य बनाना चाहता हूं जो show_id लिए show_year को प्रत्येक show_type लिए उच्चतम show_year show_type । यहाँ नेस्टेड क्वेरी है जो काम करती है - 2 और 3 देता है:

SELECT s.show_id, s.show_year
FROM ( -- subquery for applying ORDER BY before GROUP BY
    SELECT *
    FROM shows
    ORDER BY show_year DESC
) s
GROUP BY show_type;
/*
+---------+-----------+
| show_id | show_year |
+---------+-----------+
|       2 |      2015 |
|       3 |      2015 |
+---------+-----------+
*/

संदर्भ के लिए, मैं निम्नलिखित प्रश्नों की कोशिश करता हूं जो पहली बार मेरे लिए स्वाभाविक लग रहा था, लेकिन मेरे मामले में यह कुछ भी अच्छा नहीं रहा जैसा कि नीचे दिखाया गया है:

SELECT s.show_id, MAX(s.show_year)
FROM shows s
GROUP BY show_type;
/*
+---------+------------------+
| show_id | MAX(s.show_year) |
+---------+------------------+
|       1 |             2015 | <== show_id=1 does NOT have show_year=2015
|       3 |             2015 |
+---------+------------------+
*/

अब एक दृश्य बना रहा है - ऊपर की ओर की नेस्टेड क्वेरी के आधार पर (पहला चयन) समस्या ये है कि एक दृश्य एक सबक्व्री को स्वीकार नहीं करेगा

इसलिए मैं इसके बजाय दो विचारों का उपयोग कर रहा हूं। एक दूसरे के अंदर

पहले एक बस show_year डीईएससी द्वारा तालिका को व्यवस्थित करता है:

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `shows_desc` AS
SELECT `s1`.`show_id` AS `show_id`,
    `s1`.`show_type` AS `show_type`,
    `s1`.`show_year` AS `show_year`
FROM `shows` `s1`
ORDER BY `s1`.`show_year` DESC;

दूसरा, पहले समूह पर ग्रुप BY करना चाहिए:

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `shows_grouped` AS
SELECT `s2`.`show_id` AS `show_id`,
    `s2`.`show_year` AS `show_year`
FROM `shows_desc` `s2`
GROUP BY `s2`.`show_type`;

हालांकि, मेरे आश्चर्य से यह गलत पंक्तियां लौटाता है। जैसे कि यह सबव्यू के आदेश की अनदेखी कर रहा है:

+---------+-----------+
| show_id | show_year |
+---------+-----------+
|       3 |      2015 |
|       1 |      2014 | <== why?
+---------+-----------+

सुराग?

पीएस: एसक्यूएल बेला को पकड़ा गया है: http://sqlfiddle.com/#!2/e506d4/5


Answer #1

मैं एक दृश्य बनाना चाहता हूं जो शो शो के लिए show_id को प्रत्येक show_type के लिए उच्चतम show_year देगा।

select s.show_id
from shows s
where s.show_year=
    (select max(st.show_year)
    from shows st
    where st.show_type=s.show_type)

( गॉर्डन लिनॉफ के जवाब में ।)

सामान्य तौर पर दिए गए show_type के अधिकतम show_year के साथ कई show_ids हो सकते हैं (जैसा कि उन्होंने एक टिप्पणी में भी बताया।)

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

select s.show_id
from shows s
where s.show_year=
    (select max(st.show_year)
    from shows st
    where st.show_type=s.show_type)
and s.show_id=
    (select max(st.show_id)
    from shows st
    where st.show_type=s.show_type
    and st.show_year=s.show_year);

प्रति वर्ष अधिकतम प्रकार के आईडी के लिए नेस्टेड दृश्यों का उपयोग करना:

CREATE VIEW `shows_1` AS
SELECT `show_type` AS `show_type`,
    MAX(`show_year`) AS `show_year`
FROM `shows`
GROUP BY `show_type`;

CREATE VIEW `shows_ids` AS
SELECT `s`.`show_id`
FROM `shows` `s`
JOIN `shows_1` `s1`
ON `s`.`show_type`=`s1`.`show_type`
AND `s`.`show_year`=`s1`.`show_year`;

या यदि आप show_type और अधिकतम show_year के लिए अधिकतम show_id चाहते हैं:

CREATE VIEW `shows_id` AS
SELECT MAX(`s`.`show_id`) AS `show_id`
FROM `shows` `s`
JOIN `shows_1` `s1`
ON `s`.`show_type`=`s1`.`show_type`
AND `s`.`show_year`=`s1`.`show_year`
GROUP BY `s`.`show_type`,`s`.`show_year`;

तो किसी दृश्य में ग्रुप द्वारा पहले कैसे लागू किया जा सकता है?

यदि आप किसी सीलेक्ट के परिणाम को ऑर्डर करना चाहते हैं तो आपको इसके क्रम में इसके ग्रुप BY द्वारा करना चाहिए। तो आपके द्वारा पहले ग्रुप द्वारा आपके द्वारा एक बाहरी चयन का उपयोग करके एक ग्रुप BY का उपयोग करना होगा। यदि वह चयन के बाहर की ग्रुप को एक दृश्य में है, तो पहले के नाम पर एक अभिभावक को दूसरे दृश्य में होना चाहिए।

लेकिन एक क्वेरी में नामित तालिका या दृश्य का क्रम इसके परिणाम की गणना के माध्यम से नहीं रखा जाता है। तो आप समूह से पहले क्यों आदेश चाहते हैं? आपको लगता है कि आपके प्रश्न के नेस्टेड विचारों को अधिकतम show_year प्रति show_type क्यों करना चाहिए?


Answer #2

मुझे इस पर ध्यान दें:

मैं एक दृश्य बनाना चाहता हूं जो शो शो के लिए show_id को प्रत्येक show_type के लिए उच्चतम show_year देगा। यहाँ नेस्टेड क्वेरी है जो काम करती है - 2 और 3 देता है:

यह एक तरीका है, यह मानते हुए कि show_id वृद्धि तो सबसे बड़ा show_id नवीनतम वर्ष में है:

select show_type, max(show_year) as show_year, max(show_id)
from shows
group by show_type;

यदि नहीं, तो यह प्रयास करें:

select show_type, max(show_year) as show_year,
       substring_index(group_concat(show_id order by show_year desc), ',', 1) as show_id
from shows
group by show_type;

इसका कारण यह है कि आपकी अन्य क्वेरी काम नहीं करती हैं क्योंकि आपके पास "इच्छाधारी" समझ है कि कैसे MySQL काम करता है आप MySQL के लिए एक विस्तार का उपयोग कर रहे हैं, जो स्पष्ट रूप से कहा गया है कि दस्तावेज़ में काम न करें। यही है, select में कॉलम एकत्रीकरण कार्य में नहीं हैं और group by खंड में नहीं हैं (और कार्यात्मक रूप से निर्भर नहीं हैं, लेकिन यह एक बहुत उन्नत अवधारणा है जो इस चर्चा के लिए प्रासंगिक नहीं है):

इस मामले में, सर्वर प्रत्येक समूह से किसी भी मान को चुनने के लिए स्वतंत्र है, इसलिए जब तक कि वे एक समान न हों, तब तक चुना गया मान अनिश्चितता हो, जो संभवतः आप नहीं चाहते हैं।

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

निम्न को एक दृश्य के लिए भी काम करना चाहिए:

select s.*
from shows s
where s.show_year = (select max(s2.show_year) from shows s2 where s2.show_type = s.show_type);

संपादित करें द्वितीय:

यदि आप प्रत्येक show_type और show_id लिए केवल एक पंक्ति चाहते हैं, तो यह काम करना चाहिए:

select s.*
from shows
where not exists (select 1
                  from shows s2
                  where s2.show_type = s.show_type and
                        (s2.show_year > s.show_year or
                         s2.show_year = s.show_year and s2.show_id > s.show_id
                        )
                 );




greatest-n-per-group