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!

1. 这是否会返回零结果，因为我要求该行的值同时具有蓝色和红色？ （这是不可能的）


2. 我的另一个担心是，如果我使用OR而不是AND，那么我将包含蓝色或红色用户的行，但不会更改在这两种颜色之间。
   

1. 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)
2. 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屋！