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

颤菊大师 2013-10-11 11:52:02

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.ID



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

还有更好的办法吗？亲

...全文

193 12 打赏收藏转发到动态举报

写回复

用AI写文章

12 条回复

切换为时间正序

请发表友善的回复…

发表回复

无敌小二傻 2013-10-11

打赏
举报

引用 4 楼 mrlen 的回复:

[quote=引用 3 楼 lyliu602 的回复:] 1、把union可否改为union all 2、表的数据量是多少，相关字段是否都有建立索引

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

颤菊大师 2013-10-11

打赏
举报

引用 3 楼 lyliu602 的回复:

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

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

无敌小二傻 2013-10-11

打赏
举报

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

颤菊大师 2013-10-11

打赏
举报

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

颤菊大师 2013-10-11

打赏
举报

第二句漏了

SELECT f.* FROM COM_FUNC f 
JOIN COM_FUNC_PRIV fp1 ON fp1.FUNC_ID=f.ID

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 fp1.USER_ID=:userID OR ru.USER_ID=:userID OR du.USER_ID=:userID

颤菊大师 2013-10-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 '已授权的部门编号，互斥字段，优先级低';

无敌小二傻 2013-10-11

打赏
举报

引用 6 楼 mrlen 的回复:

[quote=引用 5 楼 lyliu602 的回复:] [quote=引用 4 楼 mrlen 的回复:] [quote=引用 3 楼 lyliu602 的回复:] 1、把union可否改为union all 2、表的数据量是多少，相关字段是否都有建立索引

里面会有重复记录，用 all 达不到目的。表的数据量挺大的。字段肯定都有索引了。[/quote] 是否可以先union all 起来再去重复呢？ select * from table where rowid in (select max(rowid) from table group by col..) [/quote] 您的意思是效率上有益？[/quote] 看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 有什么用呢？

yinan9 2013-10-11

打赏
举报

。。。我的意思是你需要得到什么样的数据

颤菊大师 2013-10-11

打赏
举报

引用 8 楼 yinan9 的回复:

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


    /// <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
    }

yinan9 2013-10-11

打赏
举报

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

ozcsdog1 2013-10-11

打赏
举报

不知道这样管用不

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)

颤菊大师 2013-10-11