sql - 通过行中的不同列值从数据库中选择

Question

ID        ID_ENTITY        ID_TICKET       C_ENUM        VAL2       VAL3
 1              680           460910        quack
 2              680           460910                    65536           
 3              680           460910                                text
 4              680           460909        quack
 5              680           460909                    65536           
 6              680           460909                                text
 7              680           460908        quack
 8              680           460908                    65535           
 9              680           460908                                text

我有 SQL 来获取 "ID_TICKET"，其中 "C_ENUM"= 'quack':

select "ID_TICKET"
from "T_TICKET_TYPE_ENTITY_VALUE"
where "ID_ENTITY" = 680 and "C_ENUM" = 'quack'

但我需要获取所有 "ID_TICKET"，其中 "C_ENUM"= 'quack' 并且，例如，VAL2 = 65536。

像这样:

select "ID_TICKET"
from "T_TICKET_TYPE_ENTITY_VALUE"
where "ID_ENTITY" = 680 and "C_ENUM" = 'quack' and "VAL2" = 65536

我该怎么做？

---

我正在尝试执行的完整查询:

select t."ID"
from "T_TICKET" t
join "T_TICKET_TYPE_ENTITY_VALUE" entv on entv."ID_TICKET" = t."ID"
join "T_TICKET_TYPE_ENTITY" ent on ent."ID" = entv."ID_ENTITY"
where t."ID_STATUS" != 13 and ent."ID_TICKET_TYPE" = 462
and entv."C_ENUM" = 'quack'

Answer 1

一种方法是使用 EXISTS:前两个条件是选择你想要的 id，最后一个是提取所有 ID_TICKETs):

SELECT *
from "T_TICKET_TYPE_ENTITY_VALUE" A
WHERE EXISTS (SELECT 1 
              FROM "T_TICKET_TYPE_ENTITY_VALUE" B 
              WHERE B."ID_ENTITY" = 680 and B."C_ENUM" = 'quack' 
                    AND A."ID_TICKET"=B."ID_TICKET");

输出:

ID_TICKET   ID_ENTITY   C_ENUM
1           680         quack
2           200         quick
3           680         quack

示例数据:

INSERT INTO "T_TICKET_TYPE_ENTITY_VALUE" VALUES (1,680,'quack');
INSERT INTO "T_TICKET_TYPE_ENTITY_VALUE" VALUES (1,200,'quick');
INSERT INTO "T_TICKET_TYPE_ENTITY_VALUE" VALUES (2,600,'quack');
INSERT INTO "T_TICKET_TYPE_ENTITY_VALUE" VALUES (3,680,'quack');

您的完整查询应该是:

select t."ID"
from "T_TICKET" t
join "T_TICKET_TYPE_ENTITY_VALUE" entv on entv."ID_TICKET" = t."ID"
join "T_TICKET_TYPE_ENTITY" ent on ent."ID" = entv."ID_ENTITY"
where t."ID_STATUS" != 13 and ent."ID_TICKET_TYPE" = 462
    AND EXISTS (SELECT 1 
                from "T_TICKET_TYPE_ENTITY_VALUE" B 
                where B."C_ENUM" = 'quack' AND entv."ID_TICKET"=B."ID_TICKET")