【100分】这样的SQL还有可优化的空间吗？

Question

SELECT f.* FROM COM_FUNC f 
JOIN COM_FUNC_PRIV fp ON fp.FUNC_ID=f.ID
WHERE fp.USER_ID=:userID 
  UNION
SELECT f.* FROM COM_FUNC f
JOIN COM_FUNC_PRIV fp ON fp.FUNC_ID=f.ID
JOIN COM_ROLE_USER ru ON ru.ROLE_ID=fp.ROLE_ID
WHERE ru.USER_ID=:userID
  UNION
SELECT f.* FROM COM_FUNC f
JOIN COM_FUNC_PRIV fp ON fp.FUNC_ID=f.ID
JOIN COM_DEPT_USER du ON du.DEPT_ID=fp.DEPT_ID
WHERE du.USER_ID=:userID还有一种是
SELECT f.* FROM COM_FUNC f 
JOIN COM_FUNC_PRIV fp1 ON fp1.FUNC_ID=f.IDJOIN COM_FUNC_PRIV fp2 ON fp2.FUNC_ID=f.ID
JOIN COM_ROLE_USER ru ON ru.ROLE_ID=fp2.ROLE_IDJOIN COM_FUNC_PRIV fp3 ON fp3.FUNC_ID=f.ID
JOIN COM_DEPT_USER du ON du.DEPT_ID=fp3.DEPT_ID
WHERE ru.USER_ID=:userID OR du.USER_ID=:userID
还有更好的办法吗？亲

Answer 1

第二句漏了SELECT f.* FROM COM_FUNC f 
JOIN COM_FUNC_PRIV fp1 ON fp1.FUNC_ID=f.IDJOIN COM_FUNC_PRIV fp2 ON fp2.FUNC_ID=f.ID
JOIN COM_ROLE_USER ru ON ru.ROLE_ID=fp2.ROLE_IDJOIN COM_FUNC_PRIV fp3 ON fp3.FUNC_ID=f.ID
JOIN COM_DEPT_USER du ON du.DEPT_ID=fp3.DEPT_ID
WHERE fp1.USER_ID=:userID OR ru.USER_ID=:userID OR du.USER_ID=:userID

Answer 2

想说可不可以做成一个视图……只要 xxx where user_id=:userid
就好了……

Answer 3

1、把union可否改为union all
2、表的数据量是多少，相关字段是否都有建立索引

Answer 4

里面会有重复记录，用 all 达不到目的。
表的数据量挺大的。字段肯定都有索引了。

Answer 5

里面会有重复记录，用 all 达不到目的。
表的数据量挺大的。字段肯定都有索引了。是否可以先union all 起来再去重复呢？select *
  from table
 where rowid in (select max(rowid) from table group by col..)

Answer 6

里面会有重复记录，用 all 达不到目的。
表的数据量挺大的。字段肯定都有索引了。是否可以先union all 起来再去重复呢？select *
  from table
 where rowid in (select max(rowid) from table group by col..)

您的意思是效率上有益？

Answer 7

不知道这样管用不SELECT f.* FROM COM_FUNC f
WHERE EXISTS(SELECT 1 FROM COM_FUNC_PRIV fp1 WHERE fp1.FUNC_ID=f.ID AND fp1.USER_ID=:userID)
OR EXISTS(SELECT 1 FROM COM_FUNC_PRIV fp2 JOIN COM_ROLE_USER ru ON ru.ROLE_ID=fp2.ROLE_ID WHERE fp2.FUNC_ID=f.ID AND ru.USER_ID=:userID)
OR EXISTS(SELECT 1 FROM COM_FUNC_PRIV fp3 JOIN COM_DEPT_USER du ON du.DEPT_ID=fp3.DEPT_ID WHERE fp3.FUNC_ID=f.ID AND du.USER_ID=:userID)

Answer 8

楼主，不大明白你的业务逻辑。
从你的第一个SQL来理解，两个union后的查询结果完全是第一部分的子集，这样Union起来只是多了一部分重复数据而已。最好能把你的业务逻辑解释一下，以便判断。

Answer 9

    /// <summary>
    /// 功能权限表。数据库表“COM_FUNC_PRIV”对应的实体类型。
    /// </summary>
    [Table("COM_FUNC_PRIV")]
    public partial class ComFuncPriv
    {
        #region 实体属性        /// <summary>
        /// 功能权限编号。
        /// </summary>
        [Column(true), OracleSequence]
        public int ID { get; set; }        /// <summary>
        /// 获取表“COM_FUNC_PRIV”的“ID”下一个序列值，值来自序列“SEQ_COM_FUNC_PRIV_ID”。
        /// </summary>
        /// <param name="engine">查询引擎对象。</param>
        /// <returns>返回一个结果。</returns>
        public static Result<int> NextSequence(IQueryEngine engine)
        {
            var r = engine.Execute("SELECT SEQ_COM_FUNC_PRIV_ID.NEXTVAL FROM DUAL").ToScalar();
            if(r.IsFailed) return r.Exception;
            return Convert.ToInt32(r);
        }        /// <summary>
        /// 功能编号。
        /// </summary>
        [Column("FUNC_ID")]
        public int FuncID { get; set; }        /// <summary>
        /// 已授权的用户编号，互斥字段，优先级高。
        /// </summary>
        [Column("USER_ID")]
        public int? UserID { get; set; }        /// <summary>
        /// 已授权的角色编号，互斥字段，优先级中。
        /// </summary>
        [Column("ROLE_ID")]
        public int? RoleID { get; set; }        /// <summary>
        /// 已授权的部门编号，互斥字段，优先级低。
        /// </summary>
        [Column("DEPT_ID")]
        public int? DeptID { get; set; }        #endregion
    }

Answer 10

里面会有重复记录，用 all 达不到目的。
表的数据量挺大的。字段肯定都有索引了。是否可以先union all 起来再去重复呢？select *
  from table
 where rowid in (select max(rowid) from table group by col..)

您的意思是效率上有益？看8楼，你这样做的目的是什么？ JOIN COM_FUNC_PRIV fp2 ON fp2.FUNC_ID=f.ID JOIN COM_ROLE_USER ru ON ru.ROLE_ID=fp2.ROLE_ID   JOIN COM_FUNC_PRIV fp3 ON fp3.FUNC_ID=f.ID JOIN COM_DEPT_USER du ON du.DEPT_ID=fp3.DEPT_ID WHERE ru.USER_ID=:userID OR du.USER_ID=:userID 
有什么用呢？

Answer 11

COM_FUNC_PRIV表有4个重要字段（FUNC_ID,USER_ID,ROLE_ID,DEPT_ID)现在要取出用户编号为 1 的用户，（拥有角色A，部门X）的所有 FUNC 记录。
--功能权限表
    CREATE TABLE COM_FUNC_PRIV
    (
        ID INTEGER NOT NULL PRIMARY KEY,
        FUNC_ID INTEGER NOT NULL REFERENCES COM_FUNC(ID) ON DELETE CASCADE,
        USER_ID INTEGER REFERENCES COM_USER(ID) ON DELETE CASCADE,
        ROLE_ID INTEGER REFERENCES COM_ROLE(ID) ON DELETE CASCADE,
        DEPT_ID INTEGER REFERENCES COM_DEPT(ID) ON DELETE CASCADE
    );
    CREATE SEQUENCE SEQ_COM_FUNC_PRIV_ID;
    --注释
        COMMENT ON TABLE COM_FUNC_PRIV IS '功能权限表';
        COMMENT ON COLUMN COM_FUNC_PRIV.ID IS '功能权限编号';
        COMMENT ON COLUMN COM_FUNC_PRIV.FUNC_ID IS '功能编号';
        COMMENT ON COLUMN COM_FUNC_PRIV.USER_ID IS '已授权的用户编号，互斥字段，优先级高';
        COMMENT ON COLUMN COM_FUNC_PRIV.ROLE_ID IS '已授权的角色编号，互斥字段，优先级中';
        COMMENT ON COLUMN COM_FUNC_PRIV.DEPT_ID IS '已授权的部门编号，互斥字段，优先级低';