MySQL选择不同的where子句 [英] mysql select distinct where clause

问题描述

我有此表，我想选择所有具有event ='AAAAAAAAAA'和event ='XXXXXXXXXX'的不同的user_id值(即，唯一的结果应该是user_id = 123456789)

I have this table, and I want to select all distinct user_id values where there is both an event='AAAAAAAAAA' and event ='XXXXXXXXXX' (i.e. only result should be user_id=123456789)

unique_row_id  user_id          event       event_timestamp
----------------------------------------------------------------
0              123456789        AAAAAAAAAA  2010-01-20 15:00:00
1              123456789        abcdefghij  2010-01-20 15:00:05
2              123456789        XXXXXXXXXX  2010-01-20 15:00:15
3              987654321        AAAAAAAAAA  2010-01-20 16:00:00
4              987654321        abcdefghij  2010-01-20 16:00:05
5              987654321        abcdefghij  2010-01-20 16:00:15
6              111111111        XXXXXXXXXX  2010-01-20 16:01:00
7              111111111        XXXXXXXXXX  2010-01-20 16:01:05
8              111111111        XXXXXXXXXX  2010-01-20 16:01:15

我都尝试过:

SELECT distinct user_id from mydata where event='AAAAAAAAAA' and event='XXXXXXXXXX'

这给了我一个空的结果集，并且

which gives me an empty result set, and

SELECT distinct user_id from mydata where event='AAAAAAAAAA' or event='XXXXXXXXXX'

这给了我一切.有什么建议-我猜我需要沿着加入"的路走吗?

which gives me everything. Any suggestions - I'm guessing I need to go down the 'join' road here do I?

推荐答案

尝试一下:

select user_id from mydata where event='AAAAAAAAAAA'
union
select user_id from mydata where event='XXXXXXXXXXX';

这将选择两个列表(具有AAA的用户，具有XXX的用户)，然后将它们合并在一起，从而删除重复的行.

This will select both lists (users w/ AAA, users w/ XXX), and then merge them together, removing duplicate rows.

希望有帮助！

谢谢， 乔

这篇关于MySQL选择不同的where子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！