MY SQL 的查询问题
发布于 10 年前 作者 sipgear 3819 次浏览 最后一次编辑是 8 年前 来自 问答
mysql.query('SELECT accountID,userID,notes FROM User WHERE notes = "'+info.uid+'"', function(err, fields) {
                    var res = fields[0];
                    if (fields.length > 0){
                    var acc = res['accountID'];
                    var user = res['userID'];
                    //query grouplist
                    mysql.query('SELECT accountID,userID,groupID FROM GroupList WHERE accountID = "'+acc+'" AND userID = "'+user+'"', function(err, fields) {
                    var res = fields[0];
                    if (fields.length > 0){
                    var acc = res['accountID'];
                    var grouplist = res['groupID']; 
                    //console.log(acc)  
                    //console.log(grouplist)  
                                        //query devicelist
                    mysql.query('SELECT accountID,deviceID FROM DeviceList WHERE accountID = "'+acc+'" AND groupID = "'+grouplist+'"', function(err,rows,fields) {
                    if (rows.length > 0){
                    for(var i=0;i<rows.length;i++){
                    var deviceID = rows[i].deviceID
                    mysql.query('SELECT accountID,deviceID,statusCode,longitude,latitude,speedKPH,heading,timestamp FROM EventData WHERE accountID = "'+acc+'" AND deviceID = "'+deviceID+'"  ORDER BY timestamp DESC ', function(err,rows,fields) {
                    
                    if (rows.length > 0){ 
                     for(var i=0;i<rows.length;i++){
                      var accountID = rows[i].accountID;
                      var deviceID = rows[i].deviceID;
                      var statusCode = rows[i].statusCode;
                      var longitude =  rows[i].longitude;
                      var latitude = rows[i].latitude;
                      var speedKPH =  rows[i].speedKPH;
                      var heading =  rows[i].heading;
                      var timestamp = rows[i].timestamp;
                       var accountID1 = rows[i].accountID;
                      var deviceID1 = rows[i].deviceID;
                      var statusCode1 = rows[i].statusCode;
                      var longitude1 =  rows[i].longitude;
                      var latitude1 = rows[i].latitude;
                      var speedKPH1 =  rows[i].speedKPH;
                      var heading1 =  rows[i].heading;
                      var timestamp1 = rows[i].timestamp
                       re2.push({
                      title: deviceID,
                      pic: 'http://ditu.google.cn/maps/api/staticmap?markers='+latitude+','+longitude+'&zoom=15&size=512x320',
                      url: 'https://github.com/node-webot/webot-example',
                      description: accountID+ '\n' +
                                   deviceID+ '\n' +
                                  statusCode+ '\n' +
                                  unixtime2YYMMDD(timestamp)+ '\n' +     
                                  unixtime2hhmmss(timestamp)                    
                      });
                       console.log(re2)
                       console.log("aaaaaaaaaaaaaaaaaaaa")
                       return next(null,re2)        
    
                    
                      }   
                    }             
                    } 
                    )// end query eventdata mysql

                    }//deviceID end
                     
                    }
                    })// end query devicelist

                    }
                    })// end query grouplist 
                                    
                    }  
                    else{
                      next(null, "你未绑定帐号,请先绑定你")
                    }
        })  //mysql

======================================= 以上是NODE 的微信公众号查询GPS 监控系统数据的一部分, 1:查询 User 表格,获得 accountID userID notes 2:查询 Grouplist 获得groupid 3:查询Devicelist,获得deviceID 4:查询Eventdata,获得GPS的定位数据 由于在

                    mysql.query('SELECT accountID,deviceID FROM DeviceList WHERE accountID = "'+acc+'" AND groupID = "'+grouplist+'"', function(err,rows,fields) {
                    if (rows.length > 0){
                    for(var i=0;i<rows.length;i++){
                    var deviceID = rows[i].deviceID

是历遍 DeviceList 所以最后一步,获得的数据是

[{titel:device1,pic:.... }]
aaaaaaaaaaaaaaaaaaaa
[{titel:device2,pic:.... }]
aaaaaaaaaaaaaaaaaaaa
[{titel:device3,pic:.... }]

但我需要获得

[{titel:device1,pic:.... },
{titel:device2,pic:.... },
{titel:device3,pic:.... }]
aaaaaaaaaaaaaaaaaaaa
回到顶部