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

Linux implements automatic and scheduled backup of MySQL database every day

Overview Backup is the basis of disaster recovery...

Comparative Analysis of High Availability Solutions of Oracle and MySQL

Regarding the high availability solutions for Ora...

How to use TypeScript in Vue

introduction In recent years, the call for TypeSc...

How to completely delete the MySQL service (clean the registry)

Preface When installing the executable file of a ...

How to install Windows Server 2008 R2 on Dell R720 server

Note: All pictures in this article are collected ...

HTML commonly used meta encyclopedia (recommended)

The Meta tag is an auxiliary tag in the head area...

Upgrade MySQL 5.1 to 5.5.36 in CentOS

This article records the process of upgrading MyS...

MySQL data backup and restore sample code

1. Data backup 1. Use mysqldump command to back u...

Deep understanding of the use of ::before/:before and ::after/:after

Part 1: Basics 1. Unlike pseudo-classes such as :...

Sample code for implementing history in vuex

I have recently been developing a visual operatio...

How to solve the Docker container startup failure

Question: After the computer restarts, the mysql ...

How to build SFTP server and image server on Linux cloud server

First of all, you can understand the difference b...

A brief discussion of 3 new features worth noting in TypeScript 3.7

Table of contents Preface Optional Chaining Nulli...