有點(diǎn)復(fù)雜，在你基礎(chǔ)上加了條有奇數(shù)的數(shù)據(jù)
成都創(chuàng)新互聯(lián)是專業(yè)的麥積網(wǎng)站建設(shè)公司，麥積接單;提供網(wǎng)站設(shè)計(jì)制作、成都網(wǎng)站建設(shè),網(wǎng)頁設(shè)計(jì),網(wǎng)站設(shè)計(jì),建網(wǎng)站,PHP網(wǎng)站建設(shè)等專業(yè)做網(wǎng)站服務(wù);采用PHP框架,可快速的進(jìn)行麥積網(wǎng)站開發(fā)網(wǎng)頁制作和功能擴(kuò)展;專業(yè)做搜索引擎喜愛的網(wǎng)站,專業(yè)的做網(wǎng)站團(tuán)隊(duì),希望更多企業(yè)前來合作!
創(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)?price
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,
(
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
)?t2
WHERE
t1.cat_id?=?t2.cat_id
AND?t1.rank?=?t2.rank
GROUP?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ù)。自己研究一下吧。
sekect *,TIMEDIFF(b,c) AS '結(jié)果' FROM a
或
sekect b,c,TIMEDIFF(b,c) AS '結(jié)果' FROM a
datediff
DATEDIFF(expr,expr2)
DATEDIFF()
返回起始時(shí)間
expr和結(jié)束時(shí)間expr2之間的天數(shù)。Expr和expr2
為日期或
date-and-time
表達(dá)式。計(jì)算中只用到這些值的日期部分。
mysql
SELECT
DATEDIFF('1997-12-31
23:59:59','1997-12-30');
-
1
mysql
SELECT
DATEDIFF('1997-11-30
23:59:59','1997-12-31');
-
-31
select
Datediff(列名,列名)
as
datenum
from
表名
重慶分公司
重慶分公司
