SQLite 从多个表中选择所有并从游标读取

huangapple go评论43阅读模式
英文:

SQLite selecting all from multiple tables and reading from cursor

问题

I want to retrieve the date_created from order table" can be translated as:

我想从订单表中获取date_created日期。

英文:

I have a query which retrieves data from different tables

select * from customer c, order o where c.id = o.pID 

both tables have a column 'date_created' and i want to retrieve the date_created from order table

I tried Cursor c = db.execute(sql); String dateCreated = c.getString("date_created") which returned the customer date and not the order date. when i try c.getString("o.date_created") my app crashes and returns "E/SQLiteCursor: requesting column name with table name -- o.date_created
java.lang.Exception"

can someone please guide me on how i can get the order date_created?
I

答案1

得分: 1

这个SQL查询语句中,将客户和订单表连接,使用客户的ID和订单的pID进行匹配,然后选择所有列,并将客户的创建日期重命名为customer_date_created,订单的创建日期重命名为order_date_created。然后,可以使用c.getString("order_date_created")来获取"order_date_created"列的值。

英文:

How about this:

select *, c.date_created as customer_date_created, o.date_created as order_date_created from customer c, order o where c.id = o.pID

then just use:

c.getString("order_date_created");

答案2

得分: 1

你可以使用别名来区分列名,如下所示:

select c.date_created as cust_date, o.date_created as order_date from customer c, order o where c.id = o.pID

然后尝试:

c.getString("order_date");
英文:

You can differentiate the column name by using Alias name as below:

select c.date_created as cust_date, o.date_created as order_date from customer c, order o where c.id = o.pID 

Then try:

c.getString("order_date");

答案3

得分: 1

你的查询结果集包含两列具有相同的名称,因此当你尝试通过名称 date_created 检索列时,无法确定你会得到哪个列。这就是为什么你必须为所有具有相同名称的列使用别名的原因:

select c.column1, c.column2, c.date_created as cust_date_created, .....
       o.column1, o.column2, o.date_created as ord_date_created, ....
from customer c inner join [order] o 
on c.id = o.pID  

你可以省略不想要的列。另外,请使用适当的连接语法。

现在你可以通过以下方式获取列的值:

String dateCreated = c.getString(c.getColumnIndex("ord_date_created"))
英文:

The resultset of your query contains 2 columns with the same name, so when you try to retrieve the column by the name date_created you can't be sure which one you get.<br/>
This is why you must use aliases for all columns that have the same name:

select c.column1, c.column2, c.date_created as cust_date_created, .....
       o.column1, o.column2, o.date_created as ord_date_created, ....
from customer c inner join [order] o 
on c.id = o.pID  

You can omit the columns that you don't want in the results.<br/>
Also use proper join syntax.<br/>

Now you can get the column's value by:

String dateCreated = c.getString(c.getColumnIndex(&quot;ord_date_created&quot;))

huangapple
  • 本文由 发表于 2020年7月31日 19:59:46
  • 转载请务必保留本文链接:https://go.coder-hub.com/63191440.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定