Operate on two columns of data as new columns in sql

As shown below:

select a1,a2,a1+a2 a,a1*a2 b,a1*1.0/a2 c from bb_sb

Add columns a1 and a2 of table a to form a new column a, and multiply columns a1 and a2 to form a new column b. Note:

When dividing, type conversion must be performed, otherwise the result is 0.

select a.a1,b.b1,a.a1+b.b1 a from bb_sb a ,bb_cywzbrzb b

This is an operation between columns of two different tables.

Supplementary knowledge: Sql statement to calculate the difference between the same attribute column in different records

The specific structure of the table used is shown in the figure below

The primary key in the table is (plateNumber+currentTime)

The query to be implemented is:

Given a license plate number and a query time interval, query the difference in currentTime of the records contained in the given time interval, calculate the product of AverageSpeed ​​and the difference, find the highest speed (HighestSpeed) during this period, and divide it according to different type values. –>(type value has only two values ​​0 and 1)

The main idea is that, first of all, what can be obtained is the difference in currentTime of the same license plate number (that is, the same vehicle) under the same type within a given time interval, such as the difference in currentTime of two adjacent records sorted by currentTime. After this is obtained, the rest can be obtained through aggregation functions.

We take the license plate number 京A111111 as an example and design the test case as shown in the figure below.

It can be seen that there are 6 records for the vehicle with license plate number 京A111111, of which there are 2 records with type 0 and 4 records with type 1.

We first calculate the time difference, and the SQL statement is written as follows:

SELECT a.platenumber, 
  a.currenttime, 
  a.type, 
  a.averagespeed, 
  a.highestspeed, 
  currenttime - (SELECT currenttime 
      FROM carmultispeedinfo 
      WHERE platenumber = a.platenumber 
        AND type = a.type 
        AND currenttime < a.currenttime 
      ORDER BY currenttime DESC 
      LIMIT 1)AS timediff 
FROM carmultispeedinfo a

Through Navicat, you can see the query results as shown in the following figure:

By verifying that the value of timediff is correct, you can then add content based on it.

The complete SQL statement is as follows:

SELECT Sum(aa.averagespeed * aa.timediff) AS milesdiff, 
  Max(aa.highestspeed) AS HighestSpeed, 
  Sum(aa.timediff) AS timediff, 
  aa.type 
FROM (SELECT a.platenumber, 
    a.currenttime, 
    a.type, 
    a.averagespeed, 
    a.highestspeed, 
    currenttime - (SELECT currenttime 
        FROM carmultispeedinfo 
        WHERE platenumber = a.platenumber 
          AND type = a.type 
          AND currenttime < a.currenttime 
        ORDER BY currenttime DESC 
        LIMIT 1) AS timediff 
  FROM carmultispeedinfo a)aa 
WHERE aa.platenumber = '京A111111' 
  AND aa.currenttime >= 1521790124670 
  AND aa.currenttime <= 1521790125685 
GROUP BY aa.type

The results are as follows:

After checking, we get the result we want. Then just replace the sql corresponding to the mybatis mapper file. <Record, memo> I will continue to update after I have a deeper understanding in the future. Thank you everyone, I hope it can give you a reference, and I also hope that you will support 123WORDPRESS.COM.