mysql - GROUP BY 另一个已经用两个子查询分组的表
问题描述
我有这样的桌子
Table1
ID | Val | Val2 |
606541 |3175031503131004|3175032612900004|
606542 |3175031503131004|3175032612900004|
677315 |3175031503131004|3175032612980004|
222222 |1111111111111111|8888888888888888|
231233 |1111111111111111|3175032612900004|
111111 |9999992222211111|1111111111111111|
57 |3173012102121018|3173015101870020|
59 |3173012102121018|3173021107460002|
2 |900 |7000 |
4 |900 |7001 |
我有两个条件与列Val
和Val2
。显示结果,如果Val
:
- Val 列至少有两个或多个重复值AND
- Val2 列没有重复值(唯一)
例如 :
样品 1
ID | Val | Val2 |
606541 |3175031503131004|3175032612900004|
606542 |3175031503131004|3175032612900004|
677315 |3175031503131004|3175032612980004|
False, because even the Val column
had two or more duplicate but the Val2
had dulicate value (ID 606541 and 606542)
预期样本 1 个结果
No records
样品 2
ID | Val | Val2 |
222222 |1111111111111111|8888888888888888|
231233 |1111111111111111|3175032612900004|
111111 |9999992222211111|1111111111111111|
True, Because the condition is match,
Val column had duplicate value AND Val2 had unique values
样本 2 预期结果
ID | Val | Val2 |
222222 |1111111111111111|8888888888888888|
231233 |1111111111111111|3175032612900004|
样品 3
ID | Val | Val2 |
606541 |3175031503131004|3175032612900004|
606542 |3175031503131004|3175032612900004|
677315 |3175031503131004|3175032612980004|
222222 |1111111111111111|8888888888888888|
231233 |1111111111111111|3175032612900004|
111111 |9999992222211111|1111111111111111|
Note : This is false condition, Because even the value for id 606541, 606542, and
677315 in column Val had duplicate value at least
two or more but the value in column Val2 had no unique value (it could be true condition if id 606541,
606542, and 677315 had 3 different value on Val2).
NOte 2 : for Id 222222 and 231233 that had duplicate value, this is still false, because the column
Val2 with ID 231233 had the same value with ID 606542 and 606541 (3175032612900004), so it didnt match
the second condition which only have no duplicate value
样本 3 预期结果
No records
现在回到Table1
前面,我尝试使用此查询显示两个条件的结果
SELECT
tb.* FROM table1 tb
WHERE
tb.Val2 IN (
SELECT ta.Val2
FROM (
SELECT
t.*
FROM
table1 t
WHERE
t.Val IN (
SELECT Val FROM table1
GROUP BY Val
HAVING count( Val ) > 1 )
) ta
GROUP BY
ta.Val2
HAVING
count( ta.Val2 ) = 1
)
结果
ID Val Val2
677315 3175031503131004 3175032612980004
222222 1111111111111111 8888888888888888
57 3173012102121018 3173015101870020
59 3173012102121018 3173021107460002
2 900 7000
4 900 7001
虽然我希望结果是这样的:
ID Val Val2
57 3173012102121018 3173015101870020
59 3173012102121018 3173021107460002
2 900 7000
4 900 7001
我的查询有问题吗?
这是我的数据库小提琴。
解决方案
您必须使用Group By
来查找val
&val2
具有重复值,并且需要使用Inner Join
andLeft Join
来包含/消除作为给定条件的记录(反对IN
,NOT IN
等在处理大数据时可能导致性能问题的子句)。
请在下面找到查询:
select t1.*from table1 t1 left join
(select val from table1
where val2 in (select val2 from table1 group by val2 having count(id) > 1)
) t2
on t1.val = t2.val
inner join
(select val from table1 group by val having count(id) >1) t3
on t1.val = t3.val
where t2.val is null
查询反向条件:
select t1.*from table1 t1 inner join
(select val from table1 group by val having count(id) = 1)
t2
on t1.val = t2.val
inner join
(select val2 from table1 group by val2 having count(id) >1) t3
on t1.val2 = t3.val2
请在此处找到两个查询的小提琴。
推荐阅读
- python - 或 if 语句中的命令无法正常工作
- git - 合并位于git中不同目录的文件中的更改
- python - 制作了一个小rpg,但是如果不调用整个函数,就无法将一个函数中的变量值传递给另一个函数
- vue.js - 在请求成功时有条件地呈现 b-alert
- mysql - nodejs中mysql查询的意外标识符
- javascript - PHP、mySQL、随机问题测验
- python - 如何使用 bump2version 创建自定义版本标签
- .htaccess - 在 .htaccess 中为 Google 字体添加过期标题
- autodesk-designautomation - JSON Files as parameter input using Forge Design Automation
- hibernate - 无法使用连接表的多对多映射保存数据