获取级别1的所有记录，级别2中仅具有指定数量的值 [英] Get all record of Level 1 which has only specified number of values in Level 2

问题描述

 level1 | level2  |level3 
--------|---------|--------
 A100   | NULL    | NULL
 A101   | B200    | C300
 A101   | B200    | C301
 A101   | B200    | C302
 A101   | B201    | C310
 A102   | B209    | NULL
 A103   | B211    | NULL
 A103   | B213    | NULL
 A103   | B214    | NULL
 A104   | B240    | NULL

我有这张桌子，我想把这个放出来 如果我为级别1选择child=1，则输出应为

i have this table and i want this following out put if i choose child=1 for level 1 then output should be

 Parent | Child
--------|---------
 A102   | B209
 A104   | B240

如果我为级别1选择child=2，则输出应为

if i choose child=2for level 1 then output should be

 Parent | Child
--------|---------
 A101   | B200
 A101   | B201

如果我为级别1选择child=3，则输出应为

if i choose child=3for level 1 then output should be

 Parent | Child
--------|---------
 A103   | B211
 A103   | B213
 A103   | B214

我怎样才能在mysql中使用它?

how can i acchive this in mysql?

推荐答案

使用子查询联接表，该子查询对父级的行数进行计数，并且仅选择具有所需行数的行.

Join the table with a subquery that counts the number of rows for the parent, and only select the ones with the count you want.

SELECT DISTINCT a.level1 AS Parent, a.level2 AS Child
FROM yourTable AS a
JOIN (SELECT level1, COUNT(DISTINCT level2) AS children
      FROM yourTable
      GROUP BY level1) AS b
ON a.level1 = b.level1
WHERE children = :child

用您要匹配的子代数替换:child.

Replace :child with the number of children you're trying to match.

演示

使用COUNT(Column2)而不是COUNT(*)将忽略Column2为NULL的行，因此它将不包含A100.

Using COUNT(Column2) instead of COUNT(*) will ignore the rows where Column2 is NULL, so it won't include A100.

这篇关于获取级别1的所有记录，级别2中仅具有指定数量的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！