Question

I have a table containing users and locations where they were seen:

user_id  | latitude | longitude | date_seen
-------------------------------------------
1035     | NULL     | NULL      | April 25 2010
1035     | 127      | 35        | April 28 2010
1038     | 127      | 35        | April 30 2010
1037     | NULL     | NULL      | May 1 2010
1038     | 126      | 34        | May 21 2010
1037     | NULL     | NULL      | May 24 2010

这些日期是数据库中定期安装的时间;我在此简化了这些日期。

我需要获得一份使用者名单,其纬度和长度总是无效。因此,在上述例子中,用户1037-用户1035有一行,有相对/吨信息,1038有两行,有相对/吨信息,而用户1037,两行的信息均无效。

What query can I use to achieve this result?

Answer 1

select distinct user_id
from table_name t
where not exists(
    select 1 from table_name t1 
    where t.user_id = t1.user_id and 
    t1.latitude is not null and
    t1.longitude is not null
)

您可以读到这一询问:在表内的任何行文中,给我所有拥有比<代码>null<>>>>>> 长期不同的排位用户。我认为,在这种情况下(不存在)最好采用<代码>exists/code>,因为即使表格扫描被使用(不是找到浏览的最佳途径),它只是在发现具体行文之后停止使用(无需<编码>>> 参码>。

更多了解这个专题:http://sqlblog.com/blogs/andrew_kelly/archive/2007/12/15/exists-vs-count-the-battle-never-ends.aspx“rel=” Exists Vs. Nik(*) - The fight never end... 。

Answer 2

为此,它应当发挥作用。

    SELECT user_id, count(latitude), count(longitude) 
    FROM user_loc 
    GROUP BY user_id HAVING count(latitude)=0 AND count(longitude)=0;

在MySQL测试。

Answer 3

Try:

SELECT * FROM user WHERE latitude IS NULL AND longitude IS NULL;

--——————

第2次审判(未经测试,但是根据我以前使用的询问进行的):

SELECT user_id, CASE WHEN MIN(latitude) IS NULL AND MAX(latitude) IS NULL THEN 1 ELSE 0 END AS noLatLong FROM user GROUP BY user_id HAVING noLatLong = 1;

Answer 4

这项工作:

SELECT  DISTINCT user_id
FROM    table
WHERE   latitude  IS NULL
    AND longitude IS NULL
    AND NOT user_id IN
        (SELECT  DISTINCT user_id
         FROM    table
         WHERE   NOT latitude  IS NULL
             AND NOT longitude IS NULL)

结果:

(此处与SQL)

BUT:即使我在此不使用UNT,但我的发言必须扫描所有表线,因此Micha沃 Powaga的发言效率更高。

理由:

get list of user_ids with lat/lon records to compare against (you want to EXCLUDE these from final result) - optimization: use EXISTS here...
get list of user_ids without lat/lon records (that you re interested in)
reduce by all IDs, that exist in the first list - optimization: use EXISTS here...
make user_ids DISTINCT, because the example shows multiple entries per user_id (but you want just the unique IDs)

友情链接