TA貢獻(xiàn)1831條經(jīng)驗(yàn) 獲得超4個(gè)贊
有點(diǎn)復(fù)雜，在你基礎(chǔ)上加了條有奇數(shù)的數(shù)據(jù)
創(chuàng)建表，插入數(shù)據(jù)：
create table test(cat_id int,price int); insert into test values (101,90);insert into test values (101,99);insert into test values (102,98);insert into test values (103,96);insert into test values (102,95);insert into test values (102,94);insert into test values (102,93);insert into test values (103,99);insert into test values (103,98);insert into test values (103,97);insert into test values (104,96);insert into test values (104,95);insert into test values (105,97);insert into test values (105,96);insert into test values (105,95); |
執(zhí)行：
SELECT t1.cat_id, round(avg(t1.price), 1) priceFROM ( SELECT * FROM ( SELECT t.cat_id, t.price, count(*) AS rank FROM test t LEFT OUTER JOIN test r ON t.cat_id = r.cat_id AND t.price <= r.price GROUP BY t.cat_id, t.price ORDER BY t.cat_id, t.price DESC ) s ) t1, ( SELECT DISTINCT a.cat_id, round(a.maxrank / 2) rank FROM ( SELECT cat_id, max(rank) maxrank, MOD (max(rank), 2) modrank FROM ( SELECT * FROM ( SELECT t.cat_id, t.price, count(*) AS rank FROM test t LEFT OUTER JOIN test r ON t.cat_id = r.cat_id AND t.price <= r.price GROUP BY t.cat_id, t.price ORDER BY t.cat_id, t.price DESC ) s ) t1 GROUP BY cat_id ) a, ( SELECT * FROM ( SELECT t.cat_id, t.price, count(*) AS rank FROM test t LEFT OUTER JOIN test r ON t.cat_id = r.cat_id AND t.price <= r.price GROUP BY t.cat_id, t.price ORDER BY t.cat_id, t.price DESC ) s ) b WHERE a.cat_id = b.cat_id AND a.modrank = 0 UNION ALL SELECT DISTINCT a.cat_id, round(a.maxrank / 2) + 1 rank FROM ( SELECT cat_id, max(rank) maxrank, MOD (max(rank), 2) modrank FROM ( SELECT * FROM ( SELECT t.cat_id, t.price, count(*) AS rank FROM test t LEFT OUTER JOIN test r ON t.cat_id = r.cat_id AND t.price <= r.price GROUP BY t.cat_id, t.price ORDER BY t.cat_id, t.price DESC ) s ) t1 GROUP BY cat_id ) a, ( SELECT * FROM ( SELECT t.cat_id, t.price, count(*) AS rank FROM test t LEFT OUTER JOIN test r ON t.cat_id = r.cat_id AND t.price <= r.price GROUP BY t.cat_id, t.price ORDER BY t.cat_id, t.price DESC ) s ) b WHERE a.cat_id = b.cat_id AND a.modrank = 0 UNION ALL SELECT DISTINCT a.cat_id, round(a.maxrank / 2) rank FROM ( SELECT cat_id, max(rank) maxrank, MOD (max(rank), 2) modrank FROM ( SELECT * FROM ( SELECT t.cat_id, t.price, count(*) AS rank FROM test t LEFT OUTER JOIN test r ON t.cat_id = r.cat_id AND t.price <= r.price GROUP BY t.cat_id, t.price ORDER BY t.cat_id, t.price DESC ) s ) t1 GROUP BY cat_id ) a, ( SELECT * FROM ( SELECT t.cat_id, t.price, count(*) AS rank FROM test t LEFT OUTER JOIN test r ON t.cat_id = r.cat_id AND t.price <= r.price GROUP BY t.cat_id, t.price ORDER BY t.cat_id, t.price DESC ) s ) b WHERE a.cat_id = b.cat_id AND a.modrank = 1 ) t2WHERE t1.cat_id = t2.cat_idAND t1.rank = t2.rankGROUP BY t1.cat_id |
結(jié)果：
其中：
select * from ( select t.cat_id,t.price,count(*) as rank from test t LEFT OUTER JOIN test r on t.cat_id = r.cat_id and t.price<=r.price group by t.cat_id,t.price order by t.cat_id, t.price desc ) s |
這條是主語句，主要是按照大小給出一個(gè)排名，然后根據(jù)中位數(shù)的公式，偶數(shù)的話，取最中間兩個(gè)的平均數(shù)，奇數(shù)取最中間的數(shù)。自己研究一下吧。
舉報(bào)