在单个表上组合2条sql语句 - combine 2 sql statement on single table

combine 2 sql statement on single table

本文关键字：sql 语句 2条组合单个表 | 更新日期: 2023-09-27

我有一个包含这些字段的表

table_id    name      tablename   parenttable   hierarchy
1          Staff       staff        1              10
2          Student     student      2              20
3          Intern      intern       3              30
4          StaffCon    contract     1              11
5          Staff Dep   dependant    1              12

我想在上表的基础上组合两个sql雄蕊。我在2语句中有"tablename"的值。

query 1 : result1 =select table_id from A Where tablename='staff' 
query2 : result2 = select table_id from A where parenttable_id =result1 && tablename='dependant'

试试这个

SELECT table_id FROM A WHERE parenttable_id IN (
    SELECT table_id FROM A WHERE tablename = 'staff'
) AND tablename = 'dependant'

或具有上述表结构

SELECT table_id FROM A WHERE parenttable IN (
    SELECT table_id FROM A WHERE name = 'staff'
) AND name = 'dependant'

这个也适用于我。

SELECT 
    c2.table_id
FROM
    A c1
        INNER JOIN
   A c2 ON c1.table_id = c2.parenttable
        AND c1.tablename ='student' AND c2.tablename = 'dependant'