sqlite - SQL Lite按子字符串分组,然后使用第一个

  显示原文与译文双语对照的内容
0 0

我需要为测试树结构生成一些数据。
我已经扩展了OrderEntryOrders表,其中包含 2个附加列 MasterOrderId ( 最顶层) 和 ParentOrderId ( 主或者下级 id ) 。
我想通过 Substr(Id,0,4) 子字符串的记录,来分组,为父,创建masterIds和 Substr(Id,0,6),看看树结构是否被正确组装。
我被卡住了,我需要 Join 表本身才能占据最高的1记录。


SELECT oeo.id, x.str FROM OrderEntryOrders oeo 
JOIN (SELECT Substr(Id,0,6)||'%' as str 
 FROM OrderEntryOrders GROUP BY Substr(Id,0,6)) as x 
ON x.str like oeo.id

根据请求,SQLFiddle

时间: 原作者:

0 0

我自己解决了,因为在 SQL Lite中没有 Join 和更新组合,这是徒劳的。


select * 
FROM OrderEntryOrders AS ord JOIN (
SELECT oeo.id, x.str FROM OrderEntryOrders AS oeo 
JOIN (SELECT Substr(Id,0,4)||'%' AS str 
 FROM OrderEntryOrders GROUP BY Substr(Id,0,4)) AS x 
ON oeo.id like x.str limit 1
) AS joined 
ON ord.Id like joined.str

http://sqlfiddle.com/#!7/4d0779/9

下面的代码,以防有人需要做类似的任务。


onTheMove.openLocalDatabase().transaction(function (tx) {
 tx.executeSql("SELECT DISTINCT Substr(Id,0,4)||'%' AS str FROM OrderEntryOrders GROUP BY Substr(Id,0,4)", [], function (transaction, results) {
 console.log('executed');
 var rowsToListDataStructure = function (results) {
 var array = [];
 for (var i = 0; i <results.rows.length; i++) {
 array.push(results.rows.item(i));
 }

 return array;
 }
 $.each(rowsToListDataStructure(results), function (i, item) {
 console.log("SELECT ID FROM OrderEntryOrders WHERE ID LIKE '" + item.str +"' LIMIT 1");
 transaction.executeSql("SELECT ID FROM OrderEntryOrders WHERE ID LIKE '" + item.str +"' LIMIT 1", [], function (tra, res) {
 console.log(res.rows.item(0));
 console.log("UPDATE OrderEntryOrders SET MasterOrderId ='" + res.rows.item(0).Id +"' WHERE ID LIKE '" + item.str +"'");
 tra.executeSql("UPDATE OrderEntryOrders SET MasterOrderId ='" + res.rows.item(0).Id +"' WHERE ID LIKE '" + item.str +"'", [],
 function () {
 console.log('update success')
 },
 function () {
 console.log('update fail');
 });
 }, function () {
 console.log('fail');
 })
 })
 })
});

原作者:
...