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

wgfxman

The difference should be the difference between the two price changes?

月夜雾影

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

wgfxman

Oh. . . That's simpler. . . !

wgfxman

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)