SQL WHERE帮助:如何从一个查询中的多行中提取数据? [英] SQL WHERE Help: how do i pull data from multiple rows in one single query?
问题描述
我不确定这是否应该使用子查询或多个临时表......但是任何帮助都会很棒!我试图在一个查询拉取中执行以下操作。
I am not sure if this is supposed to use a subquery, or multiple temp tables...but any assistance would be great! i am trying to do the following in one query pull.
我想拉取用户具有Color = blue和Color = red的所有行。我有兴趣拉取这些多行,以确定哪些用户将其颜色从蓝色更改为红色,或者将红色更改为蓝色。
I want to pull, say, all rows where a User has Color=blue and Color=red. I am interested in pulling these multiple rows to determine which users CHANGED their Color from blue to red, or from red to blue.
我现在的一般查询是这样的。什么是错的,我该如何改进它?谢谢!
The general query i have now is this. What is wrong and how can i improve it? thank you!
- 这是否会返回零结果,因为我要求该行的值同时具有蓝色和红色? (这是不可能的)
- 我的另一个担心是,如果我使用OR而不是AND,那么我将包含蓝色或红色用户的行,但不会更改在这两种颜色之间。
- Does this return Zero results because I am asking that the row's value has BOTH blue and red at the same time? (which is impossible)
- my other worry, is that if I use OR instead of AND, that i will include rows for users that are color blue, or color red, but did NOT change between the two colors.
SELECT *
FROM Table a
WHERE a.color='blue'
AND a.color='red'
推荐答案
我假设userId是定义用户的id,您可以用表中的任何内容替换。问题被称为关系部门
。希望这可以帮助!
I'm assuming userId as the id which defines user, you can replace with whatever you have in the table. The problem is called Relational Division
. Hope this helps!
select * from table where color in ('blue','red') and userId in(select a.userId
from Table a
where a.color in ('blue','red')
group by a.userid //whatever defines user uniquely
having count(distinct a.color)=2)
这篇关于SQL WHERE帮助:如何从一个查询中的多行中提取数据?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!