请教一条 SQL 写法

› MySQL 5.5 Community Server

› MySQL 5.6 Community Server

› Percona Configuration Wizard

› XtraBackup 搭建主从复制

Great Sites on MySQL

› Percona

› MySQL Performance Blog

› Severalnines

推荐管理工具

› Sequel Pro

› phpMyAdmin

推荐书目

› MySQL Cookbook

MySQL 相关项目

› MariaDB

› Drizzle

参考文档

› http://mysql-python.sourceforge.net/MySQLdb.html

This topic created in 2454 days ago, the information mentioned may be changed or developed.

表结构如下：
id name
1 A
2 A
2 B
3 A
3 B
3 C
查询 name 仅含 A,B，得到结果 id 为 2。

Supplement 1 · Feb 18, 2020

表的数量级为千万 jibie

SQL

请教

查询

如下

15 replies • 2020-02-19 10:28:29 +08:00

itechify

Feb 18, 2020 via Android

。。。

execute

Feb 18, 2020

没有附加条件么？
那：where id = 2

shyrock

Feb 18, 2020

这。。。作业？好奇 lz 咋写的 SQL

speedofstephen

Feb 18, 2020

select H1.id from
(select id, count(name) as cnt from T where name in ('A' ,'B') group by id) H1
inner join
(select id, count(name) as cnt from T group by id) H2
where H1.id = H2.id and H1.cnt = H2.cnt;

speedofstephen

Feb 18, 2020

错了好像还得加上 where H1.cnt=2

shyrock

Feb 18, 2020

select distinct(id) from T MINUS select distinct(id) from T where name in ('C','D',...)

ZPPP

Feb 18, 2020

@shyrock 表中不存在 C 和 D。
@speedofstephen 表中含有千万条数据，内连接 H2 会比较慢。

taotaodaddy

Feb 18, 2020 via Android

考虑一下 concat ？效率不知

ebony0319

Feb 18, 2020

select * from table t where name in ('A','B') and 2=(
select count(1) from table t1 where t1.name=t.name
)
这里的 2 为('A','B') 的长度。

ebony0319

Feb 18, 2020

修改一下：
select * from table t where name in ('A','B') and 2=(
select count(1) from table t1 where t1.id=t.id
)
这里的 2 为('A','B') 的长度。

ZPPP

Feb 18, 2020 via Android

@ebony0319 有个 id 对应 A 和 C 也会返回

ZPPP

Feb 18, 2020

@speedofstephen 感谢，是正解。

turan12

Feb 19, 2020

请楼主独立完成作业。。。

zhuzhibin

Feb 19, 2020 via iPhone

内联取交集？

alya

Feb 19, 2020

select
id
from t1
group by id
having collect_set(name) in (array("A","B"), array("B", "A"))