sql2005中的sql查询 [英] sql query in sql2005

问题描述

任何人都可以说这0和一个1在GPS_DATETIME附近在做什么，以及是否插入了值

can any one say what does this 0 and one 1 does here near the GPS_DATETIME and also whether the values get inserted

 insert into @temp          
   SELECT REGISTRATIONNO,GPS_DATETIME,SPEED,          
   CASE WHEN BASESTATIONNAME IS NULL THEN LOCATION ELSE BASESTATIONNAME END AS LOCATION,          
   LAT_METER,LON_METER,ODOMETER       
   FROM GPSDATA_HISTORY          
   WHERE          
   GPS_DATETIME BETWEEN @v_from_date AND @v_to_date          
   and registrationno=@v_reg_no          
             
           
    union           
 select REGISTRATIONNO,GPS_DATETIME,SPEED,          
  LOCATION,          
   LAT_METER,LON_METER,ODOMETER from (SELECT TOP 1 REGISTRATIONNO,GPS_DATETIME,1 as speed,          
   CASE WHEN BASESTATIONNAME IS NULL THEN LOCATION ELSE BASESTATIONNAME END AS LOCATION,          
   LAT_METER,LON_METER,ODOMETER          
   FROM GPSDATA_HISTORY          
   WHERE GPS_DATETIME BETWEEN @v_from_date AND @v_to_date          
   and registrationno=@v_reg_no order by gps_Datetime desc)b      



    
union           
 select REGISTRATIONNO,GPS_DATETIME,SPEED,          
  LOCATION,          
   LAT_METER,LON_METER,ODOMETER from (SELECT TOP 1 REGISTRATIONNO,GPS_DATETIME,0 as speed,          
   CASE WHEN BASESTATIONNAME IS NULL THEN LOCATION ELSE BASESTATIONNAME END AS LOCATION,          
   LAT_METER,LON_METER,ODOMETER          
   FROM GPSDATA_HISTORY          
   WHERE GPS_DATETIME BETWEEN @v_from_date AND @v_to_date          
   and registrationno=@v_reg_no order by gps_Datetime desc)c

推荐答案

零将是第三列中名为speed的数据.

也许他们会在速度场上做更多的动作，并且应该从零开始:-?

如果您看到对此SQL进行进一步的操作，您可能会发现为什么它们需要零作为速度的初始值.

您提供了更多的SQL语句后:

这是因为它们将3个表UNIONed，并且它们必须具有相等数量的字段和数据类型.

但是很奇怪的是，第二和第三次查询仅在记录时返回具有相同数据的数据，这是GPS_DATETIME表中最后一次发生在@v_from_date和@v_to_date
之间的事件 但是第二个表的速度为零，第三个SQL结果的速度为1.

所以你总是在输出中有这个
R1
R2
.
.
.
RN
RN(速度= 0)
RN(速度= 1)
*如果第一个SQL的输出按GPS_DATETIME排序

因此，您似乎需要跟踪SQL结果并查看其使用位置以及它们如何使用最后两个记录:-?

希望这对您有帮助.

Zero would be the data in third column which is named speed.

May be they are going to do some more action on the speed field and it should start from zero :-?

If you see further operations on this SQL you may find out why they need a zero as the initial value for speed.

Edit : after you provided more SQL statements :

It is because they UNIONed 3 tables and they need to have equal number of fields and datatypes.

But something that is very strange is that the second and third query return only on record with the same data and it is the last happened event in GPS_DATETIME table which happened between @v_from_date AND @v_to_date
But the second table has the zero speed and the third SQL result have the speed of 1.

So you have always this in output
R1
R2
.
.
.
RN
RN(speed=0)
RN(speed=1)
*if the output of the first SQL was sorted by GPS_DATETIME

So it seems that you need to track the SQL result and see where its used and how they used the last two records :-?

Hope this helps.

Select语句通常从列中获取数据，您可以使用as关键字将列名更改为更易读的名称，有时会计算列值并而不是直接来自表格本身.

出现0时就是这种情况，这意味着在输出中显示一列并将值零放入其中.

Select statements usually get their data from columns and you can change the column name to something more readable by using the as key word, sometimes your column values are computed and not directly from the table itself.

This is the case with the 0, and it means show a column in the output and put the value zero in it.

这篇关于sql2005中的sql查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！