我正在尝试回答这个问题:
字符在字符串的第一个位置相对于第二个位置出现的频率是多少?
在Mysql上使用SQL查询。
但是我收到语法错误。
代码:
SELECT
onechar,
ASCII(onechar) as asciival,
COUNT(*) as cnt,
SUM(CASE WHEN pos = 1 THEN 1 ELSE 0 END) as pos_1,
SUM(CASE WHEN pos = 2 THEN 1 ELSE 0 END) as pos_2
FROM (
(SELECT
SUBSTRING(`city`, 1, 1) as onechar,
1 as pos
FROM `orders`
WHERE LEN(`city` >= 1 )
UNION ALL
(SELECT
SUBSTRING(`city`, 2, 1) as onechar,
2 as pos
FROM `orders`
WHERE LEN(`city` >= 2)
)
GROUP BY onechar
ORDER BY onechar
错误:
您的SQL语法有误;在第1行的“ GROUP BY onechar ORDER BY onechar LIMIT 0,30”附近检查与您的MySQL服务器版本相对应的手册以使用正确的语法
尝试了几种方法而没有成功。
任何人都可以告诉我这个问题吗?
括号看起来不正确,查询缺少派生表的别名。另外,由于mysql将布尔值评估为1或0,因此您可以简化sum
语句。试试这个:
SELECT onechar, ASCII(onechar) as asciival, COUNT(*) as cnt,
SUM(pos = 1) as pos_1,
SUM(pos = 2) as pos_2
FROM (
SELECT SUBSTRING(`city`, 1, 1) as onechar, 1 as pos
FROM `orders` WHERE LENGTH(`city`) >= 1
UNION ALL
SELECT SUBSTRING(`city`, 2, 1) as onechar, 2 as pos
FROM `orders` WHERE LENGTH(`city`) >= 2
) t
GROUP BY onechar
ORDER BY onechar
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句