關于條件SQL的問題

uj5u.com熱心網友回復：

;with cte as (

    select evenid,netstation,count(1) as cnt from t group by evenid,netstation

)

,r1 as (

select evenid,netstation

,avg(ml) as avg_v

,avg(mb1) as avg_mb1

,avg(mb2) as avg_mb2 

,avg(msBB) as avg_msBB

from t as a inner join cte as b on a.evenid=b.evenid and a.netstation=b.netstation

where b.cnt<5

group by evenid,netstation

)

,t2 as (

    select * from (

        select row_number() over(partition evenid,netstation order by ml+mb1+mb2+msBB desc) as rid,a.*,b.cnt from t as a inner join cte as b

        on a.evenid=b.evenid and a.netstation=b.netstation

        where b.cnt>=5

    ) as tt

    where rid>1 and rid!=cnt

)

,r2 as (

select evenid,netstation

,avg(ml) as avg_v

,avg(mb1) as avg_mb1

,avg(mb2) as avg_mb2 

,avg(msBB) as avg_msBB

from t2 

group by evenid,netstation

)

select * from r1

union all

select * from r2

uj5u.com熱心網友回復：

uj5u.com熱心網友回復：

SET NAMES utf8mb4;

SET FOREIGN_KEY_CHECKS = 0;



-- ----------------------------

-- Table structure for earthquake_stat

-- ----------------------------

DROP TABLE IF EXISTS `earthquake_stat`;

CREATE TABLE `earthquake_stat`  (

  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增ID，作為表的主鍵',

  `eventid` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '地震事件ID，作為event表的外鍵',

  `netstation` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '臺網臺站名',

  `distance` double(11, 2) NOT NULL DEFAULT -1.00 COMMENT '震中距度數',

  `ml` double(11, 8) NOT NULL DEFAULT -1.00000000,

  `mb1` double(11, 8) NOT NULL DEFAULT -1.00000000 COMMENT '指震級mb',

  `mb2` double(11, 8) NOT NULL DEFAULT -1.00000000 COMMENT '指震級mB',

  `msBB` double(11, 8) NOT NULL DEFAULT -1.00000000,

  `pTime` datetime(0) NOT NULL COMMENT 'P波到時',

  PRIMARY KEY (`id`) USING BTREE

) ENGINE = InnoDB AUTO_INCREMENT = 12920 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;



-- ----------------------------

-- Records of earthquake_stat

-- ----------------------------

INSERT INTO `earthquake_stat` VALUES (1, '20191217111750', 'QH.LEH.00.BHZ', 46.31, -1.00000000, 5.55438771, 6.70477852, 6.03926671, '2019-12-17 19:10:31');

INSERT INTO `earthquake_stat` VALUES (2, '20191217111750', 'XZ.ZHF.00.BHZ', 50.70, -1.00000000, 5.99896267, 5.70109382, 5.11351366, '2019-12-17 19:11:29');

INSERT INTO `earthquake_stat` VALUES (3, '20191217120814', 'XJ.WNQ.00.BHZ', 1.48, 3.84471630, -1.00000000, -1.00000000, 4.22272603, '2019-12-17 20:06:40');

INSERT INTO `earthquake_stat` VALUES (4, '20191217120814', 'XJ.LSG.00.BHZ', 2.52, 4.38947441, -1.00000000, -1.00000000, 4.50544545, '2019-12-17 20:06:56');

INSERT INTO `earthquake_stat` VALUES (5, '20191217120814', 'XJ.XNY.00.BHZ', 3.38, 4.34511777, -1.00000000, -1.00000000, 4.22714363, '2019-12-17 20:07:06');

INSERT INTO `earthquake_stat` VALUES (6, '20191217120814', 'XJ.ZSU.00.BHZ', 3.83, 4.34804329, -1.00000000, -1.00000000, 4.11841309, '2019-12-17 20:07:17');

INSERT INTO `earthquake_stat` VALUES (7, '20191217120814', 'XJ.FUH.00.BHZ', 4.38, 3.49249629, -1.00000000, -1.00000000, 3.48971106, '2019-12-17 20:07:26');

INSERT INTO `earthquake_stat` VALUES (8, '20191217120814', 'XJ.KNS.00.BHZ', 4.98, 3.45560554, -1.00000000, -1.00000000, 3.44423112, '2019-12-17 20:07:27');

INSERT INTO `earthquake_stat` VALUES (9, '20191217120814', 'XJ.LTA.00.BHZ', 5.05, 4.25690109, 5.20812244, 5.35481069, 4.00039789, '2019-12-17 20:07:20');

INSERT INTO `earthquake_stat` VALUES (10, '20191217120814', 'XJ.AKS.00.BHZ', 5.39, 3.62614248, 4.89469273, 4.88790930, 3.55002247, '2019-12-17 20:07:36');

INSERT INTO `earthquake_stat` VALUES (11, '20191217120814', 'XJ.BLT.00.BHZ', 5.44, 3.81304103, 4.94677373, 4.83794639, 3.63880494, '2019-12-17 20:07:37');

INSERT INTO `earthquake_stat` VALUES (12, '20191217121045', 'XJ.WNQ.00.BHZ', 5.11, 4.99469901, 6.79657393, 6.52761268, 5.11689452, '2019-12-17 20:06:40');

INSERT INTO `earthquake_stat` VALUES (13, '20191217121045', 'XJ.LSG.00.BHZ', 7.10, 5.38947077, 7.38961381, 6.42378772, 5.25150119, '2019-12-17 20:06:56');

INSERT INTO `earthquake_stat` VALUES (14, '20191217121045', 'XJ.XNY.00.BHZ', 7.27, 4.99532861, 5.46608454, 6.08594295, 4.77995372, '2019-12-17 20:07:06');

INSERT INTO `earthquake_stat` VALUES (15, '20191217121045', 'XJ.LTA.00.BHZ', 8.69, 5.05001330, 5.45508388, 6.01400755, 4.77459540, '2019-12-17 20:07:20');

INSERT INTO `earthquake_stat` VALUES (16, '20191217121045', 'XJ.FUH.00.BHZ', 8.86, 4.62976456, 5.51815966, 5.54162134, 4.36117754, '2019-12-17 20:07:26');

INSERT INTO `earthquake_stat` VALUES (17, '20191217121045', 'XJ.KNS.00.BHZ', 9.10, -1.00000000, 5.30129338, 5.23472488, 4.02088541, '2019-12-17 20:07:27');

INSERT INTO `earthquake_stat` VALUES (18, '20191217121045', 'XJ.BLT.00.BHZ', 9.72, -1.00000000, 5.23247499, 5.59718519, 4.40276365, '2019-12-17 20:07:37');

INSERT INTO `earthquake_stat` VALUES (19, '20191217121045', 'XJ.MIF.00.BHZ', 12.52, -1.00000000, 4.07022374, 5.18356718, 4.06617685, '2019-12-17 20:08:08');

INSERT INTO `earthquake_stat` VALUES (20, '20191217121045', 'XJ.BKO.00.BHZ', 13.73, -1.00000000, 4.68744809, 4.91388139, 3.85629478, '2019-12-17 20:08:36');

INSERT INTO `earthquake_stat` VALUES (21, '20191217121045', 'QH.LEH.00.BHZ', 16.33, -1.00000000, 5.12778274, 6.32003837, 5.29324261, '2019-12-17 20:08:53');

INSERT INTO `earthquake_stat` VALUES (22, '20191217131942', 'XJ.WNQ.00.BHZ', 1.23, 3.46122843, -1.00000000, -1.00000000, 3.71450266, '2019-12-17 21:17:28');

INSERT INTO `earthquake_stat` VALUES (23, '20191217131942', 'XJ.LSG.00.BHZ', 2.04, 4.10800040, -1.00000000, -1.00000000, 4.22591766, '2019-12-17 21:17:44');

INSERT INTO `earthquake_stat` VALUES (24, '20191217131942', 'XJ.XNY.00.BHZ', 2.91, 4.05445949, -1.00000000, -1.00000000, 3.96127365, '2019-12-17 21:17:56');

INSERT INTO `earthquake_stat` VALUES (25, '20191217131942', 'XJ.ZSU.00.BHZ', 3.60, 4.06063561, -1.00000000, -1.00000000, 3.81184460, '2019-12-17 21:18:05');

INSERT INTO `earthquake_stat` VALUES (26, '20191217131942', 'XJ.FUH.00.BHZ', 4.04, 3.93571629, -1.00000000, -1.00000000, 3.64791207, '2019-12-17 21:18:04');

INSERT INTO `earthquake_stat` VALUES (27, '20191217131942', 'XJ.LTA.00.BHZ', 4.60, 4.35975266, -1.00000000, -1.00000000, 4.07492317, '2019-12-17 21:18:31');

INSERT INTO `earthquake_stat` VALUES (28, '20191217131942', 'XJ.KNS.00.BHZ', 4.78, 3.43113096, -1.00000000, -1.00000000, 3.45343957, '2019-12-17 21:18:24');

INSERT INTO `earthquake_stat` VALUES (29, '20191217131942', 'XJ.QHE.00.BHZ', 6.46, 3.41981088, 4.32709556, 4.99092049, 3.73905036, '2019-12-17 21:18:41');

INSERT INTO `earthquake_stat` VALUES (30, '20191217131942', 'XJ.QMO.00.BHZ', 9.44, -1.00000000, 3.60104144, 5.04867819, 3.95204233, '2019-12-17 21:19:10');

INSERT INTO `earthquake_stat` VALUES (31, '20191217131957', 'XJ.WNQ.00.BHZ', 1.20, 3.46125598, -1.00000000, -1.00000000, 3.69580675, '2019-12-17 21:17:28');

INSERT INTO `earthquake_stat` VALUES (32, '20191217131957', 'XJ.LSG.00.BHZ', 2.08, 4.15799603, -1.00000000, -1.00000000, 4.24110550, '2019-12-17 21:17:44');

INSERT INTO `earthquake_stat` VALUES (33, '20191217131957', 'XJ.XNY.00.BHZ', 2.92, 4.05438788, -1.00000000, -1.00000000, 3.96296238, '2019-12-17 21:17:56');

INSERT INTO `earthquake_stat` VALUES (34, '20191217131957', 'XJ.ZSU.00.BHZ', 3.57, 4.01008376, -1.00000000, -1.00000000, 3.80596232, '2019-12-17 21:18:05');

INSERT INTO `earthquake_stat` VALUES (35, '20191217131957', 'XJ.FUH.00.BHZ', 4.09, 3.93558168, -1.00000000, -1.00000000, 3.65729606, '2019-12-17 21:18:04');

INSERT INTO `earthquake_stat` VALUES (36, '20191217131957', 'XJ.LTA.00.BHZ', 4.60, 4.35868354, -1.00000000, -1.00000000, 4.07580278, '2019-12-17 21:18:31');

INSERT INTO `earthquake_stat` VALUES (37, '20191217131957', 'XJ.KNS.00.BHZ', 4.83, 3.67267934, -1.00000000, -1.00000000, 3.46606308, '2019-12-17 21:18:24');

INSERT INTO `earthquake_stat` VALUES (38, '20191217131957', 'XJ.QHE.00.BHZ', 6.51, 3.41968086, 4.32838144, 4.99220636, 3.74480320, '2019-12-17 21:18:41');

INSERT INTO `earthquake_stat` VALUES (39, '20191217131957', 'XJ.BKO.00.BHZ', 8.71, 3.24601518, 4.36109264, 4.60427252, 3.56700817, '2019-12-17 21:19:12');

INSERT INTO `earthquake_stat` VALUES (40, '20191217131957', 'XJ.RUQ.00.BHZ', 9.09, 4.05241030, 4.38982257, 5.51761767, 4.40151898, '2019-12-17 21:19:20');

INSERT INTO `earthquake_stat` VALUES (41, '20191217131957', 'XJ.MIF.00.BHZ', 9.67, -1.00000000, 4.00133729, 4.86858409, 3.82343981, '2019-12-17 21:19:21');

SET FOREIGN_KEY_CHECKS = 1;

uj5u.com熱心網友回復：

標籤：應用實例

上一篇：網路問題

下一篇：UMAT