社区教程 Wiki

注册登录

创作新主题

社区所有版块导航

Python

python开源 Django Python DjangoApp pycharm

DATA

docker Elasticsearch

问与答闲聊招聘翻译创业分享发现分享创造求职区块链支付之战

aigc

aigc chatgpt

WEB开发

linux MongoDB Redis DATABASE NGINX 其他Web框架 web工具 zookeeper tornado NoSql Bootstrap js peewee Git bottle IE MQ Jquery

机器学习

机器学习算法

Python88.com

反馈公告社区推广

产品

短视频

印度

关注

Py学习 » DATABASE

关键字“with”在MySQL 8工作台中不起作用

Ethan Allen • 5 年前 • 1559 次点击

with
    converted as (select convert(v using utf8mb4)
                         collate utf8mb4_0900_ai_ci as v from t),
    offending as (select v from converted group by v having count(*) > 1),
    duplicates as (select converted.v from converted join offending
 Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  where converted.v = offending.v)
select * from duplicates as a join duplicates as b
 Â  Â  Â  Â  Â where a.v = b.v and
Â  Â  Â  Â  Â  a.v collate utf8mb4_bin > b.v collate utf8mb4_bin;

当我把它粘贴到Workbench中时,它会说:

我使用的是MySQL 8.0.19和Workbench 8.0.19的最新版本。

当我使用这个查询时,这个工作很好:

with
    converted as (select convert(band_or_artist using utf8mb4) 
                         collate utf8mb4_0900_ai_ci as v from music_band_or_artist), 
    offending as (select v from converted group by v having count(*) > 1)
select count(*) from offending;

Python社区是高质量的Python/Django开发社区
本文地址：http://www.python88.com/topic/54833

1559 次点击

文章 [ 1 ] | 最新文章 5 年前

• 1 楼

Tim Biegeleisen 5 年前

问题是一旦你消费了 converted 中的CTE offending converting 两次:

WITH converted1 AS (
    SELECT CONVERT(v using utf8mb4) COLLATE utf8mb4_0900_ai_ci AS v FROM t
),
converted2 AS (
    SELECT CONVERT(v using utf8mb4) COLLATE utf8mb4_0900_ai_ci AS v FROM t
),
offending AS (
    SELECT v FROM converted1 GROUP BY v HAVING COUNT(*) > 1
),
duplicates AS (
    SELECT c.v FROM converted2 c
    INNER JOIN offending o ON c.v = o.v
)
SELECT *
FROM duplicates a
INNER JOIN duplicates b
    ON a.v = b.v AND
       a.v COLLATE utf8mb4_bin > b.v COLLATE utf8mb4_bin;

登录后回复

关于移动版

Py学习 - 专注于Python技术发展的社区(原Django社区)