SQLite将具有不同列值的多行合并到一个条目中

Question

我正在尝试使用SQLite将多行的列值合并到一个条目中：

ID   date        test Val1  Val2 Val3 val4  Val5 Val6

S1   2020/01/01  ECT   5     5     5     0    0    0

S2   2020/02/01  FCT   2     2     2     0    0    0

S1   2020/02/02  ECT   0     0     0     5    5    5

S2   2020/03/01  FCT   0     0     0     2    2    2

结果应该是：

 ID    test Val1  Val2 Val3 val4  Val5 Val6
    
 S1    ECT   5     5     5     5    5    5
    
 S2    FCT   2     2     2     2    2    2

我试过group_concat，但没能成功。我不想创建视图，而是希望减少数据库中相同ID的不完整或“半完成”条目的数量

此外，如果有帮助，我将使用python将值存储在SQLite DB中。因此，如果有一种方法可以通过python来实现，那也将非常好。欢迎提供任何帮助/意见

我是SQL的新手，因此任何建议都将不胜感激！ 多谢各位

Answer 1

对于此数据集，聚合执行您想要的操作：

select id, test, max(val1) val1, max(val2) val2, max(val3) val3, max(val4) val4, max(val5) val5, max(val6) val6
from mytable
group by id, test

Answer 2

我猜您在group by中有一个带有date的聚合查询。大概是这样的：

select ID, date, test, . . .
from . . .
group by ID, date, test;

更改查询以从select和group by中删除date：

select ID, test, . . .
from . . .
group by ID, test;

Answer 3

您可以使用数据字典在第一个辅助查询中动态选择带有val前缀的列：

import sqlite3
con = sqlite3.connect('db123.db')
cur = con.cursor()

query1 = "SELECT cl.name FROM sqlite_master sm CROSS JOIN pragma_table_info(sm.name) AS cl "
query1+= " WHERE sm.type='table' AND sm.name='tab' AND UPPER(cl.name) like 'VAL%' "
cur.execute(query1)
ls=cur.fetchall()

agr=''
col=''
for i in ls:
    col=str(i[0])
    agr+=",MAX("+col+") AS "+col

query2  = "SELECT ID, test {} FROM tab GROUP BY ID, test".format(agr)
cur.execute(query2)    

print(cur.fetchall())

con.close()

>>>

[('S1', 'ECT', 5, 5, 5, 5, 5, 5), ('S2', 'FCT', 2, 2, 2, 2, 2, 2)]