問(wèn)題描述

有兩個(gè)表，表A和表B,結(jié)構(gòu)相同，但是具體字段不同，在表A.date = B.date條件下，查詢出以下結(jié)果：MAX(A.ticker_buy-B.ticker_sell) 和MIN(A.ticker_buy-B.ticker_sell) ，即同一時(shí)間下兩個(gè)表不同字段的差值的最大值和最小值，并顯示對(duì)應(yīng)最大值、最小值對(duì)應(yīng)的date字段，我嘗試用sql語(yǔ)句寫了下，但是結(jié)果不對(duì)（用excel大致比較過(guò)）。我的語(yǔ)句如下：

select max(okcomfuturetickerquarter.ticker_buy-okcomfuturetickernextweek.ticker_sell) as '最大差價(jià)',min(okcomfuturetickerquarter.ticker_buy-okcomfuturetickernextweek.ticker_sell) as '最小差價(jià)',okcomfuturetickerquarter.date as '時(shí)間' from okcomfuturetickerquarter,okcomfuturetickernextweek where okcomfuturetickerquarter.date=okcomfuturetickernextweek.date and okcomfuturetickerquarter.ticker_buy is not null and okcomfuturetickernextweek.ticker_sell is not null ，

請(qǐng)各位大神幫助，寫出正確查詢語(yǔ)句。

問(wèn)題解答

先吐槽一下很長(zhǎng)的表名……

SELECT a.date as '時(shí)間', max(a.ticker_buy-b.ticker_sell) AS '最大差價(jià)',min(a.ticker_buy-b.ticker_sell) AS '最小差價(jià)' 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ù)應(yīng)該是column名,先將每一行ticker_buy和ticker_sell的差值算出來(lái)，然后用order by來(lái)排序，取第一個(gè)即可select (a.ticker_buy-b.ticker_sell) as ticker from a,b where a.date = b.date GROUP BY a.date order by ticker;