分类导航

Android 发布时间：2022-04-28 发布网站：大佬教程 code.js-code.com

大佬教程收集整理的这篇文章主要介绍了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

我想为每一行计算：在当前或上一行中有一个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中做到这一点;它会更整洁.@R_405_10675@用什么查询？

解决方法

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

您的结果实际读取的方式实际上是请求在一天而不是之前或前一天参加的人数而不是当前人数.

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      |   .25

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.DayNum

DayNum | Attendence | Ratio
1      |    NULL    |  NULL
2      |    2       |  0.500000
3      |    1       |  0.250000
4      |    1       |  0.250000