mysql 多表查詢 比較兩個字段最大、最小值,并顯示對應字段
問題描述
有兩個表,表A和表B,結(jié)構(gòu)相同,但是具體字段不同,在表A.date = B.date條件下,查詢出以下結(jié)果:MAX(A.ticker_buy-B.ticker_sell) 和MIN(A.ticker_buy-B.ticker_sell) ,即同一時間下兩個表不同字段的差值的最大值和最小值,并顯示對應最大值、最小值對應的date字段,我嘗試用sql語句寫了下,但是結(jié)果不對(用excel大致比較過)。我的語句如下:
select max(okcomfuturetickerquarter.ticker_buy-okcomfuturetickernextweek.ticker_sell) as '最大差價',min(okcomfuturetickerquarter.ticker_buy-okcomfuturetickernextweek.ticker_sell) as '最小差價',okcomfuturetickerquarter.date as '時間' from okcomfuturetickerquarter,okcomfuturetickernextweek where okcomfuturetickerquarter.date=okcomfuturetickernextweek.date and okcomfuturetickerquarter.ticker_buy is not null and okcomfuturetickernextweek.ticker_sell is not null ,
請各位大神幫助,寫出正確查詢語句。
問題解答
回答1:先吐槽一下很長的表名……
SELECT a.date as '時間', max(a.ticker_buy-b.ticker_sell) AS '最大差價',min(a.ticker_buy-b.ticker_sell) AS '最小差價' FROM a,b WHERE a.date = b.date AND a.ticker_buy IS NOT NULLAND b.ticker_sell IS NOT NULLGROUP BY a.date;回答2:
max的參數(shù)應該是column名,先將每一行ticker_buy和ticker_sell的差值算出來,然后用order by來排序,取第一個即可select (a.ticker_buy-b.ticker_sell) as ticker from a,b where a.date = b.date GROUP BY a.date order by ticker;
