Получить список записей, имеющих имя, начинающееся с определенного значения, и игнорировать в случае других значений для разных строк
Я пытаюсь получить записи Col A на основе первого символа в Col B. Если первый символ в Col B совпадает со всеми уникальными значениями Col A, то возвращается значение Col A. Если первый символ в столбце B не соответствует другим первым значениям в столбце B, то значение столбца A не должно возвращаться. Это в Oracle SQL.
select T1.colA,count(*) from colTables T1 where T1.colA in (
select T2.colA from colTables T2 where T2.colB like '1%'
group by T2.colA)
group by T1.colA;
Col A | Col B
101|12541
101|15475
101|19874
102|12544
102|22549
102|12537
103|22549
103|28747
104|72549
104|82549
104|82549
105|12549
105|12531
105|12589
106|75448
106|71544
Мой запрос дает следующий результат
ColA | Count
101|3
102|3
105|3
но я хочу, чтобы результат был
ColA| Count
101|3
105|3
Также .. Я проверяю, есть ли какие-либо средства, где я могу опустить T2.colB like '1%', чтобы получить результат следующим образом.
ColA| Count
101|3 -- All values in col B starts with 1
103|2 -- All values in col B starts with 2
105|3 -- All values in col B starts with 1
106|2 -- All values in col B starts with 7
Ответы 2
Схема
CREATE TABLE tbl (
"cola" varchar(100),
"colb" varchar(100)
);
INSERT INTO tbl
("cola", "colb")
VALUES
('101', '12541'),
('101', '15475'),
('101', '19874'),
('102', '12544'),
('102', '22549'),
('102', '12537'),
('103', '22549'),
('103', '28747'),
('104', '72549'),
('104', '82549'),
('104', '82549'),
('105', '12549'),
('105', '12531'),
('105', '12589'),
('106', '75448'),
('106', '71544');
Запрос
Живой тест: https://www.db-fiddle.com/f/iquHToVTGz8JxnWSaT2ChD/4
select cola, count(*)
from tbl
group by cola
having (cola, count(*))
in (
select
cola, count(*)
from tbl
where colb like '1%'
group by cola
)
order by cola;
Выход:
| cola | count |
| ---- | ----- |
| 105 | 3 |
| 101 | 3 |
Другой подход работает с СУБД, которая не поддерживает кортеж в предложении IN. Используйте СУЩЕСТВУЮЩИЕ:
select y.cola, count(*) as y_count
from tbl y
group by y.cola
having exists
(
select
null -- does not matter
from tbl x
-- this matters
where x.cola = y.cola
and x.colb like '1%'
group by x.cola
having count(x.cola) = count(y.cola)
)
order by y.cola;
Если вы хотите, чтобы все значения colB начинались с 1, то:
select t.colA, count(*)
from colTables t
group by t.colA
having sum(case when t.colB like '1%' then 1 else 0 end) = count(*);
Вы можете сформулировать это и по-другому, например:
having min(t.colB) >= '1' and
max(t.colB) < '2'
Если вы просто хотите, чтобы значения colB начинались с одной и той же буквы для всех colA, используйте:
having min(substr(t.colB, 1, 1)) = max(substr(t.colB, 1, 1))
Боже мой, я забыл, что это может быть так просто. Я всегда вижу, что вы можете понять, о чем спрашивает ОП, даже если их описание проблемы слишком длинное, но недостаточно ясное, как, например, проблема с островом и промежутком, которую я видел вчера. Ваше решение хорошее, я бы просто написал его как sum(case when t.colB like '1%' then 1 end), я чувствую, что 0 в SUM - это культивирование грузов. Или более семантически, да, SUM можно было бы использовать вместо COUNT, однако COUNT имеет лучшую семантику для э... подсчета :) count(case when t.colB like '1%' then t.colB end)
Если OP использует СУБД, которая поддерживает предложение FILTER для агрегата: having count(colB) filter(where colB like '1%') = count(colA). db-fiddle.com/f/iquHTovTGz8JxnWSaT2ChD/5
Спасибо @Gordon Linoff, это работает как шарм, имеющий min(substr(t.colB, 1, 1)) = max(substr(t.colB, 1, 1)) для любого заданного условия.
Спасибо @Майкл. Это работает с данным сценарием для запуска с 1.