Operate on two columns of data as new columns in sql

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.

You may also be interested in:
  • How to batch update the same field of multiple records in MySQL to different values
  • MySQL SQL statement to find duplicate data based on one or more fields
  • MySQL calculates the number of days, months, and years between two dates

<<:  12 types of component communications in Vue2

>>:  Several ways to shut down Hyper-V service under Windows 10

Recommend

A summary of the reasons why Mysql does not use date field index

Table of contents background explore Summarize ba...

A record of a Linux server intrusion emergency response (summary)

Recently, we received a request for help from a c...

Vue uses Canvas to generate random sized and non-overlapping circles

Table of contents Canvas related documents Effect...

js uses FileReader to read local files or blobs

Table of contents FileReader reads local files or...

Detailed deployment of Alibaba Cloud Server (graphic tutorial)

I have recently learned web development front-end...

mysql: [ERROR] unknown option '--skip-grant-tables'

MySQL database reports ERROR 1045 (28000): Access...

Summary of 7 reasons why Docker is not suitable for deploying databases

Docker has been very popular in the past two year...

IDEA graphic tutorial on configuring Tomcat server and publishing web projects

1. After creating the web project, you now need t...

How to use JSX in Vue

What is JSX JSX is a syntax extension of Javascri...

Enable sshd operation in docker

First, install openssh-server in docker. After th...

Solve the black screen problem after VMware installs Linux system and starts

1. Installation environment 1. HUAWEI mate x cpu ...

How to install docker on ubuntu20.04 LTS

Zero: Uninstall old version Older versions of Doc...

Content-type description, that is, the type of HTTP request header

To learn content-type, you must first know what i...