我在MySQL
有一张桌子。在这里,我有两个名为device
和test_Id
的专栏。
+-------+---------+
|test_id| device|
+-------+---------+
| test_1| Win PC|
| test_1| Xbox|
| test_2| Windows|
| test_3| Win PC|
+-------+---------+
我想选择test_Id
,其中device
既是Win PC
又是Xbox
。我已经做了如下工作
select test_id from table where device in ('Win PC', 'Xbox');
我得到了低于结果。
+----------+
| test_id |
+----------+
| test_1 |
| test_3 |
| test_1 |
+----------+
但我希望结果是这样的
+----------+
| test_id |
+----------+
| test_1 |
| test_1 |
+----------+
我该怎么做呢?
发布于 2018-06-12 04:42:04
实现该结果的最简单查询是test_id的不同列表:
SELECT t.test_id
FROM mytable t
WHERE t.device IN ('Win PC','Xbox')
GROUP BY t.test_id
HAVING COUNT(DISTINCT t.device) = 2
如果我们想要返回单独的行,我们可以使用它作为内联视图,并连接到外部表。如下所示:
SELECT d.test_id
FROM ( SELECT t.test_id
FROM mytable t
WHERE t.device IN ('Win PC','Xbox')
GROUP BY t.test_id
HAVING COUNT(DISTINCT t.device) = 2
) q
JOIN mytable d
ON d.test_id = q.test_id
WHERE d.device IN ('Win PC','Xbox')
另一种替代方法是使用EXISTS相关子查询
SELECT d.test_id
FROM mytable d
WHERE d.device IN ('Win PC','Xbox')
AND EXISTS ( SELECT 1
FROM mytable t1
WHERE t1.test_id = d.test_id
AND t1.device = 'Win PC'
)
AND EXISTS ( SELECT 1
FROM mytable t2
WHERE t2.test_id = d.test_id
AND t2.device = 'Xbox'
)
还有其他查询模式可以实现相同的结果。
发布于 2018-06-12 04:30:14
select test_id
from table
where device = 'Win PC'
AND test_id IN (SELECT test_id
from table
where device = 'Xbox')
发布于 2018-06-12 04:31:50
如果您只想知道一台test_id是否同时具有Xbox和PC,您可以执行以下操作:
select test_id from
(select test_id, count(test_id) as cnt
from table
where device in ('Win PC', 'Xbox'), group by test_id)
where cnt > 1
https://stackoverflow.com/questions/50805510
复制相似问题