SQL Query Latest Package user

Problem demand:

In the database, the time-based dimension of the mobile customers to find the latest set of packages.

--------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------

Ideas:

Check that the number corresponds to the latest change plan time, and then check out the latest package for that user through conditional matching.

The first step:

Query the time of the user's latest change plan, query the user as a group, select the time of the latest (maximum) time.

Select Msisdn, MAX (time) from the B GROUP by msisdn

Step Two:

Use this as a condition to match the data in the original table B, matching the query with a left or right connection.

Select A.msisdn, A.time, b.tariff_packages from B--queries the user in a. table, time in table, package in table B
Right Join--Connection query
(select Msisdn, MAX (time) as time from B GROUP by MSISDN) A--Name the result you just queried as table a
On (b.msisdn = a.msisdn and b.time = a.time)-The match condition is that the user and time of the two tables can match the result before output

Note: In the connection query need to indicate which field is queried from which table, or the query will appear when the error, the error content is xxx field does not know the source of the table, only need to be clearly identified can be resolved.

Results of the output:

User 135002 Current latest in-use package is B

User 135001 Current latest in-use package is a

User 135003 current latest in use of the package is C

Note: Some of the data in the original table is not inserted in chronological order, in the actual application, there may be a day of data loss after a few days to fill up the situation,

Therefore, some of the data will be inserted in the order of the time of the inconsistency, but will not query the effect.

--------------------------------------------------------------------------------------------------------------- --------------------------------------------------------

Attached 1: Table structure and annotations

Table B

Field type Comment

MSISDN int User Number

Time int (easy to calculate with int type as comparison)

Tariff_packages varchar Modified Package Type

Attachment 2: Inserting statement code

Insert into B (MSISDN, Time, Tariff_packages) VALUES (135001,20180101, ' A ');
Insert into B (MSISDN, Time, Tariff_packages) VALUES (135002,20180123, ' C ');
Insert into B (MSISDN, Time, Tariff_packages) VALUES (135003,20180123, ' A ');
Insert into B (MSISDN, Time, Tariff_packages) VALUES (135001,20180317, ' C ');
Insert into B (MSISDN, Time, Tariff_packages) VALUES (135002,20180415, ' B ');
Insert into B (MSISDN, Time, Tariff_packages) VALUES (135003,20180418, ' B ');
Insert into B (MSISDN, Time, Tariff_packages) VALUES (135002,20180419, ' B ');
Insert into B (MSISDN, Time, Tariff_packages) VALUES (135001,20180518, ' B ');
Insert into B (MSISDN, Time, Tariff_packages) VALUES (135003,20180619, ' C ');
Insert into B (MSISDN, Time, Tariff_packages) VALUES (135003,20180711, ' C ');
Insert into B (MSISDN, Time, Tariff_packages) VALUES (135001,20180712, ' A ');
Insert into B (MSISDN, Time, Tariff_packages) VALUES (135003,20180718, ' B ');
Insert into B (MSISDN, Time, Tariff_packages) VALUES (135002,20180102, ' C ');
Insert into B (MSISDN, Time, Tariff_packages) VALUES (135003,20180801, ' C ');
Insert into B (MSISDN, Time, Tariff_packages) VALUES (135003,20180730, ' A ');

SQL Query Latest Package user