在一个Android游戏中,我将从不同社交网络中检索到的用户信息存储在SQLite表中-
create table table_social (
id text primary key,
social integer not null, /* Facebook = 1, Google+ = 2, Twitter = 3, ... */
first_name text not null,
photo text null,
city text null,
stamp datetime default current_timestamp /* indicates if it's "main" profile */
);
并通过最新时间戳指示“主要”玩家资料(显示在应用导航抽屉中并显示给远程游戏合作伙伴的资料):
是否可以从这样的表中选择某个记录,并指出它是否具有最新的时间戳-在同一SQL命令中?
(我想使用后一种信息在我的应用中启用/禁用“设为主要个人资料”按钮)。
这是我在Android SDK的sqlite3.exe提示符下尝试过的操作-
首先创建表并用3条记录填充它:
C:\Users\user1\AppData\Local\Android\Sdk\platform-tools>sqlite3.exe
SQLite version 3.8.10.2 2015-05-20 18:17:19
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table table_social (
...> id text primary key,
...> social integer not null, /* Facebook = 1, Google+ = 2, Twitter = 3, ... */
...> first_name text not null,
...> photo text null,
...> city text null,
...> stamp datetime default current_timestamp
...> );
sqlite> insert into table_social (id, social, first_name) values (1, 1, "Alex on Facebook");
sqlite> insert into table_social (id, social, first_name) values (2, 2, "Alex on Google+");
sqlite> insert into table_social (id, social, first_name) values (3, 3, "Alex on Twitter");
然后,我可以检索“主要”玩家个人资料:
sqlite> select * from table_social order by stamp desc limit 1;
3|3|Alex on Twitter|||2015-10-21 10:47:40
而且我能够检索Facebook用户数据:
sqlite> select *, stamp=max(stamp) from table_social where social=1;
1|1|Alex on Facebook|||2015-10-21 10:47:18|1
但是,请问如何结合这两个命令?
我已经尝试了以下方法,但是它返回1(而它应该返回false):
sqlite> select *, max(stamp)=stamp from table_social where social=1;
1|1|Alex on Facebook|||2015-10-21 10:47:18|1
我也尝试了子选择查询:
sqlite> select *, stamp=(select * from table_social order by stamp desc limit 1) from table_social where social=1;
Error: only a single result allowed for a SELECT that is part of an expression
标量子查询必须返回单个值。(错误消息已经告诉您了。)因此,请替换(select * ...)
为(select stamp ...)
。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句