代码之家 › 专栏 › 技术社区 › Sasha Grievus

在mysql中,如何在一个字段中选择一组值为0或1的记录中,只有值为1的记录才存在?

mysql

Sasha Grievus · 技术社区 · 6 年前

我有一个“wordstyped”表,其中包含“idBook”、“guidUser”、“bookmarkLetter”、“letter”、“attemptWrong”和“isCorrect”。

idBook  guidUser  bookmarkLetter  letter  attemptWrong   isCorrect
-------------------------------------------------------------------
1       1         100             a       2              0
1       1         100             a       3              0
1       1         100             a       3              1
1       1         101             b       6              0
1       1         101             b       2              0
2       2         101             b       3              0
2       3         152             d       7              0
3       3         153             e       2              0

我想选择所有记录,其中所有字段都包含“attemptWrong”的最大数目,但有不同的三元组“idBook”、“guidUser”和“bookmarkLetter”。

SELECT DISTINCT w1.bookmarkletter, w1.attemptsWrong
FROM wordstyped w1 INNER JOIN ( SELECT bookmarkLetter, guidUser, idBook, 
MAX(attemptsWrong) AS maxAttemptsWrong 
FROM wordstyped 
GROUP BY bookmarkLetter, guidUser, idBook ) w2 
ON w1.bookmarkLetter = w2.bookmarkLetter AND w1.guidUser = w2.guidUser 
AND w1.guidUser = '1' 
AND w1.idBook ='1'
AND w1.attemptsWrong = w2.maxAttemptsWrong AND w1.bookmarkLetter >=0
AND w1.bookmarkLetter <=200 ORDER BY `w1`.`bookmarkletter` ASC

就像这把小提琴 http://sqlfiddle.com/#!9/238794/2

如果将w1.isCorrect添加到示例中的第一个select语句喜欢

SELECT DISTINCT w1.bookmarkletter, w1.attemptsWrong, w1.isCorrect
FROM wordstyped w1 INNER JOIN ( SELECT bookmarkLetter, guidUser, idBook, 
MAX(attemptsWrong) AS maxAttemptsWrong 
FROM wordstyped 
GROUP BY bookmarkLetter, guidUser, idBook ) w2 
ON w1.bookmarkLetter = w2.bookmarkLetter AND w1.guidUser = w2.guidUser 
AND w1.guidUser = '1' 
AND w1.idBook ='1'
AND w1.attemptsWrong = w2.maxAttemptsWrong AND w1.bookmarkLetter >=0
AND w1.bookmarkLetter <=200 ORDER BY `w1`.`bookmarkletter` ASC

( http://sqlfiddle.com/#!9/238794/1

idBook  guidUser  bookmarkLetter  letter  attemptWrong   isCorrect
-------------------------------------------------------------------
1       1         101             b       6              0
1       1         101             b       2              0
2       2         101             b       3              0

正确返回

bookmarkLetter    attemptWrong    isCorrect
-------------------------------------------------------------------
101               6               0

而这组记录

idBook  guidUser  bookmarkLetter  letter  attemptWrong   isCorrect
-------------------------------------------------------------------
1       1         100             a       2              0
1       1         100             a       3              0
1       1         100             a       3              1

退货

bookmarkLetter    attemptWrong    isCorrect
-------------------------------------------------------------------
100               3               0
100               3               1

bookmarkLetter    attemptWrong    isCorrect
-------------------------------------------------------------------
100               3               1

我怎样才能达到第二个结果?

1 回复 | 直到 6 年前

Fahmi 6 年前

尝试使用聚合和分组方式

http://sqlfiddle.com/#!9/238794/4

SELECT w1.bookmarkletter, max(w1.attemptsWrong), max(w1.isCorrect) 
  FROM wordstyped w1 INNER JOIN 
  ( SELECT bookmarkLetter, guidUser, idBook, MAX(attemptsWrong) AS maxAttemptsWrong 
    FROM wordstyped 
    GROUP BY bookmarkLetter, guidUser, idBook ) w2 
ON w1.bookmarkLetter = w2.bookmarkLetter AND w1.guidUser = w2.guidUser 
AND w1.guidUser = '1' 
AND w1.idBook ='1'
AND w1.attemptsWrong = w2.maxAttemptsWrong AND w1.bookmarkLetter >=0
AND w1.bookmarkLetter <=200 
group by w1.bookmarkletter
ORDER BY `w1`.`bookmarkletter` ASC