| |

VerySource

 Forgot password?
 Register
Search
Author: 月夜雾影

Help look at this SQL statement, how to write it? There are more than 5W pieces of data in the table, how to query can b

[Copy link]

0

Threads

126

Posts

73.00

Credits

Newbie

Rank: 1

Credits
73.00

 China

Post time: 2020-6-12 20:15:01
| Show all posts
The difference should be the difference between the two price changes?
Reply

Use magic Report

1

Threads

4

Posts

4.00

Credits

Newbie

Rank: 1

Credits
4.00

 Invalid IP Address

 Author| Post time: 2020-6-13 08:45:01
| Show all posts
Unit price 2: It shows that the price corresponding to the date 2016-11-28 is 14.5
The difference is the difference between unit price and unit price 2
Reply

Use magic Report

0

Threads

126

Posts

73.00

Credits

Newbie

Rank: 1

Credits
73.00

 China

Post time: 2020-6-13 11:00:01
| Show all posts
Oh. . . That's simpler. . . !
Reply

Use magic Report

0

Threads

126

Posts

73.00

Credits

Newbie

Rank: 1

Credits
73.00

 China

Post time: 2020-6-13 19:00:01
| Show all posts
select A. product name, MAX (A. time) AS time, SUM (A. quantity) AS quantity, A. unit price AS unit price 1, B. unit price AS unit price 2, A. unit price-(select top 1 unit price from testtb where Product name = A. Product name and time <'2016-12-01' order by time desc) AS difference
FROM testtb A LEFT JOIN testtb B ON A. Product name=B. Product name
AND B. time=(SELECT MAX(time) FROM testtb WHERE product name=A. product name AND time<A. time AND (unit price<>A. unit price OR time<'2016-12-01'))
WHERE A. time>='2016-12-01' AND A. time<='2016-12-31'
GROUP BY A. Product name, A. Unit price, B. Unit price
ORDER BY MAX(A. time)
Reply

Use magic Report

You have to log in before you can reply Login | Register

Points Rules

Contact us|Archive|Mobile|CopyRight © 2008-2023|verysource.com ( 京ICP备17048824号-1 )

Quick Reply To Top Return to the list