2表1结果，一个棘手的sql

ca1c2owp 于 2021-08-13 发布在 Java

关注(0)|答案(3)|浏览(336)

在潜伏了多年的stackoverflow之后，我终于有必要来找你dev gurus寻求一点帮助。
上下文：
我是一个开发人员，正在玩我从sap提取的信息。我提取信息，并使用h2数据库在内存中创建两个表。
我的table是这样的：

CREATE TABLE USERS 
(
     SID VARCHAR(255), 
     SYSID VARCHAR(5), 
     MANDT VARCHAR(3), 
     BNAME VARCHAR(255), 
     GLTGV DATE, 
     GLTGB DATE, 
     USTYP VARCHAR(2), 
     LOCNT VARCHAR(3), 
     UFLAG VARCHAR(3), 
     TRDAT DATE, 
     LTIME VARCHAR(255), 
     CLASS VARCHAR(255), 
     PWDCHGDATE DATE, 
     PROFILE VARCHAR(255)
)

CREATE TABLE ROLES 
(
     SID VARCHAR(255), 
     SYSID VARCHAR(5), 
     MANDT VARCHAR(3), 
     UNAME VARCHAR(255), 
     AGR_NAME VARCHAR(255)
)

别担心这些定义，为了简单起见，我只是在“愚弄”它们。
一个用户可以没有或有多个角色。你怎么把它们绑在一起？
好 SYSID , MANDT 以及 BNAME 从表 USERS 必须符合 ROLES 表，列 SYSID , MANDT 以及 UNAME （所有类型相同）
所以我创建了这个查询

SELECT DISTINCT
    t1.SID, 
    t1.SYSID, 
    t1.MANDT, 
    t1.BNAME, 
    t1.GLTGV, 
    t1.GLTGB, 
    t1.USTYP, 
    t1.LOCNT, 
    t1.UFLAG, 
    t1.TRDAT, 
    t1.LTIME, 
    t1.CLASS, 
    t1.PWDCHGDATE, 
    t1.PROFILE
FROM 
    USERS AS t1, 
    ROLES AS t2 
WHERE 
    t1.SYSID = t2.SYSID AND 
    t1.MANDT = t2.MANDT AND 
    t1.BNAME = t2.UNAME AND
    (t2.AGR_NAME = "ZTEST_ROLE")

只要我只通过一个角色进行查询，它就工作得很好。
要查询多个角色，我需要

SELECT DISTINCT
    t1.SID, 
    t1.SYSID, 
    t1.MANDT, 
    t1.BNAME, 
    t1.GLTGV, 
    t1.GLTGB, 
    t1.USTYP, 
    t1.LOCNT, 
    t1.UFLAG, 
    t1.TRDAT, 
    t1.LTIME, 
    t1.CLASS, 
    t1.PWDCHGDATE, 
    t1.PROFILE
FROM 
    USERS AS t1, 
    ROLES AS t2 
WHERE 
    t1.SYSID = t2.SYSID AND 
    t1.MANDT = t2.MANDT AND 
    t1.BNAME = t2.UNAME AND
    (t2.AGR_NAME = "ZTEST_ROLE" OR t2.AGR_NAME = "ZTEST_ROLE2")

它也起作用。如果要从所有具有ztest\u role或ztest\u role2角色的用户获取信息，可以使用此查询。
现在，这是我的问题，如果我想选择所有同时具有ztest\u role和ztest\u role2角色的用户，我就不能让它工作。我的查询总是返回0行
这是我正在使用的查询

SELECT DISTINCT
    t1.SID, 
    t1.SYSID, 
    t1.MANDT, 
    t1.BNAME, 
    t1.GLTGV, 
    t1.GLTGB, 
    t1.USTYP, 
    t1.LOCNT, 
    t1.UFLAG, 
    t1.TRDAT, 
    t1.LTIME, 
    t1.CLASS, 
    t1.PWDCHGDATE, 
    t1.PROFILE
FROM 
    USERS AS t1, 
    ROLES AS t2 
WHERE 
    t1.SYSID = t2.SYSID AND 
    t1.MANDT = t2.MANDT AND 
    t1.BNAME = t2.UNAME AND
    (t2.AGR_NAME = "ZTEST_ROLE" AND t2.AGR_NAME = "ZTEST_ROLE")

我已经玩了一段时间，我不知道我的错误在哪里
为了方便和万一你想尝试这个在线，我一直在使用https://sqliteonline.com/
我很感谢你的帮助，也很抱歉我发了这么长的邮件

sql Join h2

来源：https://stackoverflow.com/questions/62091486/2-tables-1-result-a-tricky-sql

3条答案

按热度按时间

aemubtdh1#

你不能同时拥有两者 (t2.AGR_NAME = "ZTEST_ROLE" and t2.AGR_NAME = "ZTEST_ROLE2") 你在说什么 t2.AGR_NAME = "ZTEST_ROLE" 一定是真的 t2.AGR_NAME = "ZTEST_ROLE2" 在同一行上必须为true。
你能做到的 (t2.AGR_NAME = "ZTEST_ROLE" and (select AGR_NAME from ROLES where uname = t1.bname and agr_name = "ZTEST_ROLE2") = "ZTEST_ROLE2")) 但我怀疑这是最好的办法。有点讨厌
这是你的例子
https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=18052895bcd6bea155ef01c957a06ff7

赞(0）回复(0）举报 2021-08-13

7nbnzgx92#

我认为这样的解决方案正是你想要的。。我可以用不同的方法来做，但我想你明白了。

SELECT DISTINCT 
    t1.SID, 
    t1.SYSID, 
    t1.MANDT, 
    t1.BNAME, 
    t1.GLTGV, 
    t1.GLTGB, 
    t1.USTYP, 
    t1.LOCNT, 
    t1.UFLAG, 
    t1.TRDAT, 
    t1.LTIME, 
    t1.CLASS, 
    t1.PWDCHGDATE, 
    t1.PROFILE
FROM (
    SELECT DISTINCT
        t1.SID, 
        t1.SYSID, 
        t1.MANDT, 
        t1.BNAME, 
        t1.GLTGV, 
        t1.GLTGB, 
        t1.USTYP, 
        t1.LOCNT, 
        t1.UFLAG, 
        t1.TRDAT, 
        t1.LTIME, 
        t1.CLASS, 
        t1.PWDCHGDATE, 
        t1.PROFILE,
        case when t2.AGR_NAME = "ZTEST_ROLE" then 1 else 0 end as t2t1,
        case when t2.AGR_NAME = "ZTEST_ROLE2" then 1 else 0 end as t2t2
    FROM 
        USERS AS t1, 
        ROLES AS t2 
    WHERE 
        t1.SYSID = t2.SYSID AND 
        t1.MANDT = t2.MANDT AND 
        t1.BNAME = t2.UNAME AND
        (t2.AGR_NAME = "ZTEST_ROLE" or t2.AGR_NAME = "ZTEST_ROLE")
    ) as tmp
    WHERE t2t1 = 1 and t2t2 = 1

赞(0）回复(0）举报 2021-08-13

qmb5sa223#

你需要两个 exists 子查询，每个角色一个：

select u.*
from users u
where
    exists (
        select 1
        from roles r 
        where 
            r.ysid = u.sysid
            and r.mandt = u.mandt
            and r.uname = r.uname
            and r.agr_name = 'ZTEST_ROLE'
    ) 
    and exists (
        select 1
        from roles r 
        where 
            r.ysid = u.sysid
            and r.mandt = u.mandt
            and r.uname = u.bname
            and r.agr_name = 'ZTEST_ROLE2'
    )

而且它看起来相当复杂，它应该是一个有效的选择，特别是如果你有一个索引上 roles(sysid, mandt, uname, agr_name) .
另一种解决方案是使用聚合查询进行筛选：

select u.*
from users u
where
    exists (
        select count(*)
        from roles r 
        where 
            r.ysid = u.sysid
            and r.mandt = u.mandt
            and r.uname = r.uname
            and r.agr_name in ('ZTEST_ROLE', 'ZTEST_ROLE2')
    )  = 2

第二种方法的优点是，一次检查更多角色更容易扩展：您只需要扩展列表右侧的列表 in 运算符，并相应地增加目标计数。

赞(0）回复(0）举报 2021-08-13

我来回答

2表1结果，一个棘手的sql

3条答案

相关问题

热门标签

最新问答