mysql count case(计算一列中值的总出现次数)

dy1byipe 于 2021-07-26 发布在 Java

关注(0)|答案(2)|浏览(315)

我想数一数地球上的gps点数 plt_distinct 表1按运输方式划分 labels 表格：

CREATE TABLE `plt_distinct` (
  `directory` varchar(10) NOT NULL DEFAULT '',
  `latitude` double NOT NULL DEFAULT '0',
  `longitude` double NOT NULL DEFAULT '0',
  `flag` int(11) DEFAULT NULL,
  `altitude` double NOT NULL DEFAULT '0',
  `passeddate` varchar(255) DEFAULT NULL,
  `gpsdate` date ,
  `gpstime` time ,
  `gpsdatetime` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`directory`,`latitude`,`longitude`,`gpsdate`,`gpstime`,`altitude`)
);

CREATE TABLE `labels` (
  `directory` varchar(10) NOT NULL DEFAULT '',
  `starttime` datetime ,  
  `endtime` datetime ,
  `transportationmode` varchar(10) NOT NULL DEFAULT '',
  PRIMARY KEY (`directory`,`starttime`,`endtime`,`transportationmode`)
);

INSERT INTO labels (directory, starttime, endtime, transportationmode) 
VALUES (010, '2007-06-26 11:32:29', '2007-06-26 11:40:29','bus'), 
(010, '2008-03-28 14:52:54', '2008-03-28 15:59:59', 'train'),
(010, '2008-03-29 01:27:50', '2008-03-29 15:59:59', 'train'),
(010, '2008-03-31 16:00:08', '2008-03-31 16:09:01', 'taxi'),
(010, '2008-04-01 00:48:32', '2008-04-01 00:59:23', 'taxi');

INSERT INTO plt_distinct (directory, latitude, longitude, flag, altitude, 
passeddate,gpsdate, gpstime, gpsdatetime)
VALUES (010, 18.254638, 109.500127, 0, 26, 39347.6212037037, '2007-09-22', 
'14:54:32', '2007-09-22 14:54:32'),
(010, 18.254665, 109.500092, 0, 36, 39347.6212268519, '2007-09-22', 
'14:54:34', '2007-09-22 14:54:34'),
(010, 18.254925, 109.499998, 0, 44, 39347.6213078704, '2007-09-22', 
'14:54:41', '2007-09-22 14:54:41'),
(010, 18.255035, 109.499972, 0, 38, 39347.6213310185, '2007-09-22',
'14:54:43', '2007-09-22 14:54:43');

然后我写道：

SELECT COUNT(*), CASE( WHEN transportationmode='bus' THEN 'bus', 
                       WHEN transportationmode='car' OR transportationmode='taxi' THEN 'car', 
                       WHEN transportationmode='train' THEN 'train') 
              END AS mode 
FROM plt_distinct JOIN labels USING(directory);

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHEN transportationmode='bus' THEN 'bus', WHEN transportationmode='car' OR trans' at line 1

编辑
我在这里用示例数据生成一个db fiddle。

sql mysql Join

来源：https://stackoverflow.com/questions/62404837/mysql-count-case-count-the-total-occurrance-of-value-in-a-column

2条答案

按热度按时间

2nbm6dog1#

你只需要修改一些语法和用法 group by . . . mysql允许您使用列别名：

SELECT COUNT(*),
       (CASE WHEN l.transportationmode IN ('bus', 'train') THEN l.transportationmode
             WHEN l.transportationmode IN ('car', 'taxi') THEN 'car' 
        END) AS mode 
FROM plt_distinct d JOIN 
     labels l
     USING (directory)
GROUP BY mode;

这是一把小提琴。
笔记：
你的 CASE 有多余的逗号和括号。
虽然你的基本逻辑 CASE , IN 比链锁更简单 = / OR 他们在一起——而且经常有更好的表现。
这简化了 CASE 条件。
所有列引用都是限定的，这意味着表是名称的一部分。

赞(0）回复(0）举报 2021-07-26

w3nuxt5m2#

也许你需要这样的东西：

SELECT mode, COUNT(*)
FROM (
    SELECT 
        CASE transportationmode
            WHEN 'taxi' THEN 'car'
            ELSE transportationmode
        END AS mode 
    FROM plt_distinct 
    JOIN labels USING(directory)
) t1
GROUP BY mode;

赞(0）回复(0）举报 2021-07-26

我来回答

mysql count case(计算一列中值的总出现次数)

2条答案

相关问题

热门标签

最新问答