How to remove duplicate records, and the data is the latest time, or the record of the lowest price-CodePudding

A product table, the model only
The model def_price brand
Wa 001 0.5
002 0.2 qa
Wa 003 0.3

B to watch
The model price who date
001 0.4 zhang 2017-10-08
001 0.3 2017-9-08 zhang 2
001 0.5 zhang 2017-8-08
001 0.6 zhang 2017-11-08
002 the 2017-5-05
0.2, dick, and harry002 0.3 five li 2017-4-06

1: I don't want duplicate records, and the record is the latest date, record, or the lowest price???????
Article 2: I want every lowest price and do not repeat types of data, 003 to the table and no records, just use the default table price, other is null,
I want to output results: below:
001 0.3 2017-9-08 wa zhang 2
002 the 2017-5-05 qa
0.2, dick, and harry0.3 null null wa 003

Select distinct B.m odel, min (p. rice), B.w ho, B.d ate from A, B, where arjun odel=B.m odel;
If so, 003 will not be printed,

The select arjun odel, min (p. rice), B.w ho, B.d ate from A, B group by arjun odel;
Such out each column of the data is independent, will not be associated, such as the lowest price in the records of the who, the date is not the lowest price the recorded data,

Consult long worry me a great god, baidu a lot and I don't know how to write, next heartfelt thanks,

CodePudding user response:

To explore this problem is more troublesome, you want to get a B to cargo list each model with the lowest price of the latest one record
So here are a few problems:
1. Lowest price

 
SELECT the model, MIN (price) as price FROM GROUP B to table BY model

2. The lowest price for the latest date

 
The SELECT arjun odel, Amy polumbo rice, MAX (B.d ate) as the date 
The FROM (
SELECT the model, MIN (price) as price FROM GROUP B to table BY model 
) a, B to table B 
WHERE arjun odel=b.m odel 
GROUP BY arjun odel, Amy polumbo rice

3. The lowest price for the latest date, if there is still a repetitive, arbitrary a

 
The SELECT aa model, aa. Price, aa. The date, ANY_VALUE (bb) who) as the who 
The FROM (
The SELECT arjun odel, Amy polumbo rice, MAX (B.d ate) as the date 
The FROM (
SELECT the model, MIN (price) as price FROM GROUP B to table BY model 
) a, B to table B 
WHERE arjun odel=b.m odel 
Aa, B) to cargo table bb 
WHERE aa. Model=bb. Model 
GROUP BY aa. The model, the aa price, aa. The date

4. This is the final query, need data

 
The SELECT m.m odel, 
IFNULL (data) price, m. ef_price) as price, 
Data. The who, the data date, m.b rand 
The FROM (
The SELECT aa model, aa. Price, aa. The date, ANY_VALUE (bb) who) as the who 
The FROM (
The SELECT arjun odel, Amy polumbo rice, MAX (B.d ate) as the date 
The FROM (
SELECT the model, MIN (price) as price FROM GROUP B to table BY model 
) a, B to table B 
WHERE arjun odel=b.m odel 
Aa, B) to cargo table bb 
WHERE aa. Model=bb. Model 
GROUP BY aa. The model, the aa price, aa. The date 
JOIN A product table m) data RIGHT ON m.m odel=data. The model

CodePudding user response:

reference 1st floor ZJCXC response:

this question to explore more troublesome, you want to get a B to cargo list each model with the lowest price of the latest one record
So here are a few problems:
1. Lowest price

 
SELECT the model, MIN (price) as price FROM GROUP B to table BY model

2. The lowest price for the latest date

 
The SELECT arjun odel, Amy polumbo rice, MAX (B.d ate) as the date 
The FROM (
SELECT the model, MIN (price) as price FROM GROUP B to table BY model 
) a, B to table B 
WHERE arjun odel=b.m odel 
GROUP BY arjun odel, Amy polumbo rice

3. The lowest price for the latest date, if there is still a repetitive, arbitrary a

 
The SELECT aa model, aa. Price, aa. The date, ANY_VALUE (bb) who) as the who 
The FROM (
The SELECT arjun odel, Amy polumbo rice, MAX (B.d ate) as the date 
The FROM (
SELECT the model, MIN (price) as price FROM GROUP B to table BY model 
) a, B to table B 
WHERE arjun odel=b.m odel 
Aa, B) to cargo table bb 
WHERE aa. Model=bb. Model 
GROUP BY aa. The model, the aa price, aa. The date

4. This is the final query, need data

 
The SELECT m.m odel, 
IFNULL (data) price, m. ef_price) as price, 
Data. The who, the data date, m.b rand 
The FROM (
The SELECT aa model, aa. Price, aa. The date, ANY_VALUE (bb) who) as the who 
The FROM (
The SELECT arjun odel, Amy polumbo rice, MAX (B.d ate) as the date 
The FROM (
SELECT the model, MIN (price) as price FROM GROUP B to table BY model 
) a, B to table B 
WHERE arjun odel=b.m odel 
Aa, B) to cargo table bb 
WHERE aa. Model=bb. Model 
GROUP BY aa. The model, the aa price, aa. The date 
JOIN A product table m) data RIGHT ON m.m odel=data. The model

Very thank you for your brother, you this four solutions or no way to print A table 003:
First, don't query A table,
2/3/4 are where arjun odel=b.m odel, then A list has 003, no 003 B list, there's no way to give out 003, according to A table model, to merge table B all sorts of conditions?

Is there a way to output the result?
001 0.3 2017-9-08 wa zhang 2
002 the 2017-5-05 qa
0.2, dick, and harry0.3 null null wa 003

Thank you

CodePudding user response:

You said 002 of the who, the date didn't write, I admit that, because I wrote a GROUP BY leakage
You say no is 003, that is completely unknown
RIGHT the JOIN is white?

CodePudding user response:

 USE tempdb for; 
The product TABLE DROP TABLE IF the EXISTS A, B to goods TABLE; 

The CREATE TABLE A product TABLE (
The model varchar (10) primary key, def_price decimal (1, 1), brand varchar (10)); 
INSERT A product table VALUES 
(' 001 ', 0.5 'wa'), 
(' 002 ', 0.2 'qa), 
(' 003 ', 0.3 'wa'); 

CREATE TABLE B to cargo list (
Model varchar (10), a price a decimal (10, 1), the who varchar (10), the date the date); 
INSERT table B to cargo VALUES 
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull