如果您想了解android–SQLite中一行与另一行的比较的相关知识，那么本文是一篇不可错过的文章，我们将对sqlplus一行显示进行全面详尽的解释，并且为您提供关于AndroidJavaSQLit
如果您想了解android – SQLite中一行与另一行的比较的相关知识，那么本文是一篇不可错过的文章，我们将对sqlplus一行显示进行全面详尽的解释，并且为您提供关于Android Java SQLite rowid 基于另一列中的数据、Android SQLite中的多主键、android SQLite中的更新功能不起作用、Android SQLite将表复制到另一个表中的有价值的信息。
本文目录一览：- android – SQLite中一行与另一行的比较（sqlplus一行显示）
- Android Java SQLite rowid 基于另一列中的数据
- Android SQLite中的多主键
- android SQLite中的更新功能不起作用
- Android SQLite将表复制到另一个表中
android – SQLite中一行与另一行的比较（sqlplus一行显示）
_id a b c d 1 1 1 1 0 2 0 1 1 1 3 1 0 0 1 4 0 1 0 1
我想为每一行计算：在当前或上一行中有一个1的所有列a,b,c和d,当前和上一行中有一个1的百分比是多少？输出看起来像这样：
_id a b c d result 1 1 1 1 0 NULL 2 0 1 1 1 50% 3 1 0 0 1 25% 4 0 1 0 1 33%
我可以在sqlite之外的Java中做到这一点,但我宁愿在sql中做到这一点;它会更整洁.我应该使用什么查询？
解决方法
Select CurAndNext.T1_id,Sum( Case When D1.Val + D2.Val = 1 Then 1 End ) As CntInEitherRow,Sum( Case When D1.Val + D2.Val = 2 Then 1 End ) / 4.000 As PercBoth
From (
Select T1._id As T1_id,Max( T2._id ) As T2_id
From MyTable As T1
Left Join MyTable As T2
On T2._id < T1._id
Group By T1._id
) As CurAndNext
Join (
Select _id,''a'' As Col,a As Val From MyTable As T1
Union All
Select _id,''b'',b From MyTable As T1
Union All
Select _id,''c'',c From MyTable As T1
Union All
Select _id,''d'',d From MyTable As T1
) As D1
On D1._id = CurAndNext.T1_id
Left Join (
Select _id,a As Val From MyTable As T1
Union All
Select _id,b From MyTable As T1
Union All
Select _id,c From MyTable As T1
Union All
Select _id,d From MyTable As T1
) As D2
On D2._id = CurAndNext.T2_id
And D2.Col = D1.Col
Group By CurAndNext.T1_Id
使查询困难的一个重要因素是数据被非规范化.因此,我必须将其标准化才能获得您所寻求的信息.
知道a,c和d列代表哪些列在世界上有所不同.上述查询的复杂性表明模式不能很好地映射到业务需求.知道他们代表学生出勤,我们可以设计一个替代模式.
Create Table Student
(
Id int not null Primary Key,Name varchar(50) not null
)
Create Table Class
(
Id int not null Primary Key,Name varchar(50) not null
)
-- if using dates,this would be the equivalent
-- of a calendar table
Create Table ClassDay
(
DayNum int not null Primary Key
)
-- ClassDayNum would be better as a Date
Create Table Attendence
(
StudentId int References Student( Id ),ClassId int References Class( Id ),ClassDayNum int not null References ClassDay( DayNum ),Unique( StudentId,ClassId,ClassDayNum )
)
Insert Student( Id,Name )
Select 1,''a''
Union All Select 2,''b''
Union All Select 3,''c''
Union All Select 4,''d''
Insert Class( Id,Name )
Values (1,''Some Class'' )
Insert ClassDay( DayNum )
Select 1
Union All Select 2
Union All Select 3
Union All Select 4
Insert Attendence( ClassId,StudentId,ClassDay )
Select 1,1,1
Union All Select 1,3
Union All Select 1,2,2
Union All Select 1,4
Union All Select 1,3,4,4
of all the columns a,c and d that have a 1 in EITHER the current or prevIoUs row
您的结果实际读取的方式实际上是请求在一天而不是之前或前一天参加的人数而不是当前人数.
Select Class.Id,ClassDay.DayNum,Count(distinct A.StudentId) As Attendence,Count(distinct A.StudentId) / 4.000 As Ratio From Class Cross Join Student Cross Join ClassDay Left Join Attendence As A On A.ClassId = Class.Id And A.StudentId = Student.Id And A.ClassDayNum = ClassDay.DayNum And A.ClassDayNum > 1 Left Join Attendence As A2 On A2.ClassId = Class.Id And A2.StudentId = Student.Id And A2.ClassDayNum = ClassDay.DayNum - 1 Where Not( A.StudentId Is Not Null And A2.StudentId Is Not Null ) Group By Class.Id,ClassDay.DayNum结果：
DayNum Attendence | Ratio 1 | 0 | 0 2 | 1 | .25 3 | 1 | .25 4 | 1 | .25what percentage have a 1 in BOTH the current and prevIoUs row
Select ClassDay.DayNum,Sum( Case When A.StudentId Is Not Null And A2.StudentId Is Not Null Then 1 End ),Sum( Case When A.StudentId Is Not Null And A2.StudentId Is Not Null Then 1 End ) / 4.000 From Class Cross Join Student Cross Join ClassDay Left Join Attendence As A On A.ClassId = Class.Id And A.StudentId = Student.Id And A.ClassDayNum = ClassDay.DayNum And A.ClassDayNum > 1 Left Join Attendence As A2 On A2.ClassId = Class.Id And A2.StudentId = Student.Id And A2.ClassDayNum = ClassDay.DayNum - 1 Group By ClassDay.DayNumDayNum | Attendence | Ratio 1 | NULL | NULL 2 | 2 | 0.500000 3 | 1 | 0.250000 4 | 1 | 0.250000
Android Java SQLite rowid 基于另一列中的数据
您可以在不首先获取其 rowid 的情况下更新表：
String where = "masNameCol = ?";
ContentValues newValues = new ContentValues();
newValues.put(KEY_MASNAMECOL,"somevaluehere"); // what value do you want this column to be updated?
newValues.put(KEY_MASTIMECOL,newTime);
return db.update(masterRecord,newValues,where,new String[] {name}) != 0;
请注意，如果列 masNameCol 不是唯一的，这将使用变量 name 的值更新所有行。
Android SQLite中的多主键
在sqlite for Android中,添加多主键的正确方法是什么？
目前我有：
String Create_table = "CREATE TABLE project ( keyId INTEGER PRIMARY KEY, keyName TEXT PRIMARY KEY)";
我想到的替代方法是：
String Create_table = "CREATE TABLE project (keyID INTEGER, keyName TEXT, PRIMARY KEY(keyID, keyName))";
两者都有效吗？如果是这样,哪个更好？另外我如何禁止NULL值？
解决方法:
不,不可能为单个表创建多个主键.这是任何sql的基本规则.但是,您可以使用其他约束(如带有索引的UNIQUE)来实现此目的.
这不是有效的sql语法：
String Create_table = “CREATE TABLE project ( keyId INTEGER PRIMARY KEY, keyName TEXT PRIMARY KEY)”;
在其他方面,您可以为多列创建主键,如下所示：
Create Table yourTableName (col1, col2, col3, PRIMARY KEY (col1, col2));
How do I disallow NULL values?
您可以使用NOT NULL约束,它不允许您输入NULL值.
android SQLite中的更新功能不起作用
public long updateEvent(String id,String title,String description,String reminder,String alarm,Date date) {
try {
int rowid = Integer.parseInt(id);
Log.i("com.eventmanager","insert Event");
formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String datestring = formatter.format(date);
ContentValues cv = new ContentValues();
cv.put(KEY_TITLE,title);
cv.put(KEY_DESCRIPTION,description);
cv.put(KEY_REMINDER,reminder);
cv.put(KEY_ALARM,alarm);
cv.put(KEY_DATE,datestring);
myDB.beginTransaction();
Log.i("com.eventmanager","sql UPDATE ");
myDB.update(DATABASE_TABLE,cv,KEY_ROWID + "=" + rowid,null);
myDB.setTransactionSuccessful();
myDB.endTransaction();
} catch (Exception e) {
e.printstacktrace();
}
return 1;
}
提前致谢 ！！
解决方法
long i = myDB.update(DATABASE_TABLE,KEY_ROWID + "=?",new String[]{rowid});
if(i>0)
return 1; // 1 for successful
else
return 0; // 0 for unsuccessful
是的,通过public int update (String table,ContentValues values,String whereClause,String[] whereArgs)功能.
Android SQLite将表复制到另一个表中
目标是拥有方法deleteColumn(int indexOfColumn);
谢谢
解决方法
create table new_table as select column1,column2,....from old_table;
在这里选择要在新表中看到的那些列.然后删除旧表并将新表重命名为旧表的名称.希望它可以工作.
关于android – SQLite中一行与另一行的比较和sqlplus一行显示的问题就给大家分享到这里，感谢你花时间阅读本站内容，更多关于Android Java SQLite rowid 基于另一列中的数据、Android SQLite中的多主键、android SQLite中的更新功能不起作用、Android SQLite将表复制到另一个表中等相关知识的信息别忘了在本站进行查找喔。
本文标签：
