मूल्यों के साथ MySQL गणना - mysql

मैं गणना कर रहा हूँ MIN, MAX और यह AVG खिलाड़ी स्तर का:

SELECT
MIN(`p`.`level`) AS "min",
MAX(`p`.`level`) AS "max",
AVG(`p`.`level`) AS "avg"
FROM `dfh`.`players` `p`

मैं कैसे प्राप्त कर सकता हूं p.name फ़ील्ड मान जो संदर्भित करता है MIN तथा MAX?

फे। मुझे यह मूल्य मेरे पास मिला है p तालिका:

Lp. | Name | Level
1   | Test | 12
2   | Tesx | 15
3   | Anot | 150

और क्वेरी निम्नलिखित को वापस ले जाएगी:

MIN: 12
MAX: 150

लेकिन मैं इसे भी वापस लेना चाहता हूं Name के अनुरूप MIN तथा MAX मान:

MIN: 12 Test
MAX: 150 Anot

मैं उसे कैसे कर सकता हूँ?

उत्तर:

जवाब के लिए 2 № 1

इस प्रश्न का प्रयास करें -

SELECT "MIN" property, GROUP_CONCAT(p1.name) name, p1.level FROM players p1
JOIN (SELECT MIN(level) level FROM players) p2
ON p1.level = p2.level
UNION
SELECT "MAX" property, GROUP_CONCAT(p1.name) name, p1.level FROM players p1
JOIN (SELECT MAX(level) level FROM players) p2
ON p1.level = p2.level

उच्चतम "अनुभव" मान और किसी भी "आईडी" के साथ क्वेरी -

SELECT * FROM

(SELECT "MIN" property, p1.id, p1.level, p1.name FROM players p1
JOIN (
SELECT
level, MAX(experience) experience
FROM
players,
(SELECT @level:=MIN(level) FROM players) t
WHERE level = @level
) p2
ON p1.experience = p2.experience AND p1.level = p2.level
ORDER BY id LIMIT 1) t_min

UNION

SELECT * FROM

(SELECT "MAX" property, p1.id, p1.level, p1.name FROM players p1
JOIN (
SELECT
level, MAX(experience) experience
FROM
players,
(SELECT @level:=MAX(level) FROM players) t
WHERE level = @level
) p2
ON p1.experience = p2.experience AND p1.level = p2.level
ORDER BY id LIMIT 1) t_max

किसी अन्य तालिका में शामिल होने के लिए इस डेटासेट को सबक्वायरी के रूप में उपयोग करें -

SELECT * FROM
(
SELECT * FROM

(SELECT "MIN" property, p1.id, p1.level, p1.name FROM players p1
JOIN (
SELECT
level, MAX(experience) experience
FROM
players,
(SELECT @level:=MIN(level) FROM players) t
WHERE level = @level
) p2
ON p1.experience = p2.experience AND p1.level = p2.level
ORDER BY id LIMIT 1) t_min

UNION

SELECT * FROM

(SELECT "MAX" property, p1.id, p1.level, p1.name FROM players p1
JOIN (
SELECT
level, MAX(experience) experience
FROM
players,
(SELECT @level:=MAX(level) FROM players) t
WHERE level = @level
) p2
ON p1.experience = p2.experience AND p1.level = p2.level
ORDER BY id LIMIT 1) t_max
) p
LEFT JOIN guild_member gm
ON gm.id = p.id

उत्तर № 2 के लिए 1

यह कम / उच्च नाम वापस करेगा कुशलता:

select "MIN", level, name from players order by level limit 1
union all
select "MAX", level, name from players order by level desc limit 1

Subqueries की कमी पर ध्यान दें ...


संबंधित सवाल
सबसे लोकप्रिय