SQLize Online / PHPize Online  /  SQLtest Online

with V_Docker as ( select 117360 DevicePK,'9d007786b203' ID,'kisup2-deploy-docker.artifacts.tn.tngrp.ru/trv/3v-platform/cards:0.67.3' Name,'image' Type, 'yes' isKubernetes union all select 117360 DevicePK,'2cf6c5d459de' ID,'kisup2-deploy-docker.artifacts.tn.tngrp.ru/trv/3v-platform/cards:0.67.7' Name,'image' Type, 'yes' isKubernetes union all select 117360 DevicePK,'0f06d576c01b' ID,'kisup2-deploy-docker.artifacts.tn.tngrp.ru/trv/3v-platform/cards:0.68.6' Name,'image' Type, 'yes' isKubernetes union all select 117360 DevicePK,'9755f632cdfe' ID,'kisup2-deploy-docker.artifacts.tn.tngrp.ru/trv/3v-platform/cards:0.69.0' Name,'image' Type, 'yes' isKubernetes union all select 117360 DevicePK,'ad8be71cf748' ID,'kisup2-deploy-docker.artifacts.tn.tngrp.ru/trv/3v-platform/cards:0.70.0-rc.0' Name,'image' Type, 'yes' isKubernetes union all select 117360 DevicePK,'f5637353fb44' ID,'kisup2-deploy-docker.artifacts.tn.tngrp.ru/trv/3v-platform/cards:0.70.0-rc.3' Name,'image' Type, 'yes' isKubernetes union all select 117360 DevicePK,'339b16e7bc08' ID,'kisup2-deploy-docker.artifacts.tn.tngrp.ru/trv/3v-platform/commentsprocessor:0.66.0' Name,'image' Type, 'yes' isKubernetes union all select 117360 DevicePK,'3044cfdedcba' ID,'kisup2-deploy-docker.artifacts.tn.tngrp.ru/trv/3v-platform/commentsprocessor:0.67.1' Name,'image' Type, 'yes' isKubernetes union all select 117360 DevicePK,'0017f519ff03' ID,'kisup2-deploy-docker.artifacts.tn.tngrp.ru/trv/3v-platform/dictionaries:0.70.0-rc.3' Name,'image' Type, 'yes' isKubernetes union all select 117360 DevicePK,'e0c865937d04' ID,'kisup2-deploy-docker.artifacts.tn.tngrp.ru/trv/3v-platform/engine:0.70.3' Name,'image' Type, 'yes' isKubernetes ) SELECT devices.DevicePK , V_Docker.isKubernetes , V_Docker.Type , CASE WHEN V_Docker.Name LIKE '%[/-]%[/-]%[/-]%' THEN LEFT(V_Docker.Name, CASE WHEN CHARINDEX('/', V_Docker.Name, CHARINDEX('/', V_Docker.Name, CHARINDEX('/', V_Docker.Name) + 1) + 1) > 0 THEN CHARINDEX('/', V_Docker.Name, CHARINDEX('/', V_Docker.Name, CHARINDEX('/', V_Docker.Name) + 1) + 1) WHEN CHARINDEX('-', V_Docker.Name, CHARINDEX('-', V_Docker.Name, CHARINDEX('-', V_Docker.Name) + 1) + 1) > 0 THEN CHARINDEX('-', V_Docker.Name, CHARINDEX('-', V_Docker.Name, CHARINDEX('-', V_Docker.Name) + 1) + 1) ELSE LEN(V_Docker.Name) END - 1 ) WHEN V_Docker.Name LIKE '%[/-]%[/-]%' THEN LEFT(V_Docker.Name, CASE WHEN CHARINDEX('/', V_Docker.Name, CHARINDEX('/', V_Docker.Name) + 1) > 0 THEN CHARINDEX('/', V_Docker.Name, CHARINDEX('/', V_Docker.Name) + 1) WHEN CHARINDEX('-', V_Docker.Name, CHARINDEX('-', V_Docker.Name) + 1) > 0 THEN CHARINDEX('-', V_Docker.Name, CHARINDEX('-', V_Docker.Name) + 1) ELSE LEN(V_Docker.Name) END - 1 ) ELSE V_Docker.Name END AS app , COUNT(V_Docker.id) AS cnt FROM UDB.kasua.V_Devices devices INNER JOIN UDB.kasua.V_Docker V_Docker on (devices.DevicePK = V_Docker.DevicePK) WHERE V_Docker.isKubernetes = 'yes' and devices.DevicePK IN (%s) GROUP BY devices.DevicePK, V_Docker.isKubernetes, V_Docker.Type, app