android – SQLite中一行与另一行的比较

概述我有一个 Android应用程序.在我的Android应用程序中,我有一个SQLite数据库.在我的SQLite数据库中,我有一个如下所示的表： _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 我想为每一 我有一个 Android应用程序.在我的AndroID应用程序中,我有一个sqlite数据库.在我的sqlite数据库中,我有一个如下所示的表：

_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 PercBothFrom    (        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.ColGroup 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 tableCreate 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 1Union All Select 2Union All Select 3Union All Select 4Insert Attendence( ClassID,StudentID,ClassDay )Select 1,1,1Union All Select 1,3Union All Select 1,2,2Union All Select 1,4Union 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 RatioFrom 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 - 1Where Not( A.StudentID Is Not Null And A2.StudentID Is Not Null )Group By Class.ID,ClassDay.DayNum

结果：

DayNum  Attendence | Ratio1      |    0      |   02      |    1      |   .253      |    1      |   .254      |    1      |   .25

what 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.000From 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 - 1Group By ClassDay.DayNum

DayNum | Attendence | Ratio1      |    NulL    |  NulL2      |    2       |  0.5000003      |    1       |  0.2500004      |    1       |  0.250000

总结

以上是内存溢出为你收集整理的android – SQLite中一行与另一行的比较全部内容，希望文章能够帮你解决android – SQLite中一行与另一行的比较所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。