如果一条记录满足特定条件，postgres 会对其计数两次
我认为下面的查询自然会执行我解释的操作，但显然不会...
我的表看起来像这样：
id | name | g | partner | g2
1 | John | M | Sam | M
2 | Devon | M | Mike | M
3 | Kurt | M | Susan | F
4 | Stacy | F | Bob | M
5 | Rosa | F | Rita | F
我试图获取 id，其中 g 或 g2 值等于“M”...但是，a记录 g 和 g2 值均为“M”的地方应该返回两行，而不是 1。
因此，在上面的示例数据中，我尝试返回：
$q = pg_query("SELECT id FROM mytable WHERE ( g = 'M' OR g2 = 'M' )");
1
1
2
2
3
4
但是，它总是返回：
1
2
3
4
I thought that the query below would naturally do what I explain, but apparently not...
My table looks like this:
id | name | g | partner | g2
1 | John | M | Sam | M
2 | Devon | M | Mike | M
3 | Kurt | M | Susan | F
4 | Stacy | F | Bob | M
5 | Rosa | F | Rita | F
I'm trying to get the id where either the g or g2 value equals 'M'... But, a record where both the g and g2 values are 'M' should return two lines, not 1.
So, in the above sample data, I'm trying to return:
$q = pg_query("SELECT id FROM mytable WHERE ( g = 'M' OR g2 = 'M' )");
1
1
2
2
3
4
But, it always returns:
1
2
3
4
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您的查询不起作用，因为无论每一行是否匹配一个或两个条件，每一行都只返回一次。要获得您想要的结果，请使用两个查询并使用
UNION ALL组合结果：结果：
Your query doesn't work because each row is returned only once whether it matches one or both of the conditions. To get what you want use two queries and use
UNION ALLto combine the results:Result:
你可以尝试这样的 UNION：
“SELECT id FROM mytable WHERE (g = 'M') UNION SELECT id FROM mytable WHERE (g2 = 'M')”
希望这有帮助，Martin
you might try a UNION along these lines:
"SELECT id FROM mytable WHERE ( g = 'M') UNION SELECT id FROM mytable WHERE ( g2 = 'M')"
Hope this helps, Martin