php - 如何检查数据库中的多行

Question

假设我有 2 列，大小和颜色，表格的内容是这样的:

sizes  colors

large  red
large  green
small  green

如果我想检索在 sizes 表中有 large 和在 colors 表中有 green 的记录我会做这样的事情:

$q = "SELECT * FROM tablename WHERE sizes='large' and colors='green'";

但是，如果我只想在 large 行中同时具有 red 和 green 的情况下检索它怎么办，如上所示？

Answer 1

您似乎需要检查匹配的 2 行。自连接可能是最简单的方法:-

SELECT *
FROM SomeTable a
INNER JOIN SomeTable b
ON a.sizes = b.sizes
WHERE a.sizes = 'large'
AND a.colors = 'red'
AND b.colors = 'green'

避免使用 JOIN，但这只会返回大小(您是否有其他要返回的列？):-

SELECT sizes, COUNT(DISTINCT colors) AS colour_count
FROM SomeTable
WHERE sizes = 'large'
AND colors IN ('red', 'green')
GROUP BY sizes
HAVING colour_count = 2