Fork me on GitHub

SQL应用

发表于 | 分类于
字数统计: 845 | 阅读时长 ≈ 4

一、工作总结：　

　　今天工作中遇到了这样一个需求，具体是根据某张表的某一列值得不同，进行不同关联操作。起初自己的想法是采用UNION操作，把两种情况连接起来，但是会出现一个问题，当进行动态传值SQL拼接的时候，只能给最后面加，而不能给两种情况的结果集都动态拼接，导致查询结果和预期不一样。　　

自己的SQL语句如下，自己使用UNION进行结果集连接。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT DISTINCT C.TUUSERNA,C.TUCLASID,C.TUUSERID,C.TUEMAIL,C.TUPHONE,C.TUACEMAIL,C.TUACMOBILE,C.TUCOMP,C.TUACTIVE,
                                C.TUENNAME,B.UJPAPARV AS TUUSERNA,A.UJPAPARV AS OFFCCODE,D.TBDTL2 FROM
                                FROM WEBNFLIB.URUSERPF C
                                INNER JOIN WEBNFLIB.URUSOBNOPF B ON B.TUUSERID=C.TUUSERID 
                                INNER JOIN WEBNFLIB.URUSOBNOPF A ON A.TUUSERID=C.TUUSERID 
                                INNER JOIN CDFLIB.CDTABDPF D ON D.TBCODE=A.UJPAPARV 
                                WHERE B.UJPAPARN = 'FWD_FRTP' AND A.UJPAPARN = 'FWD_OFFC' AND C.TUACTIVE=1 AND C.TUCLASID IN('A','B') AND D.TBTYPE='OFFC'
                                UNION
                              SELECT DISTINCT C.TUUSERNA,C.TUCLASID,C.TUUSERID,C.TUEMAIL,C.TUPHONE,C.TUACEMAIL,C.TUACMOBILE,C.TUCOMP,C.TUACTIVE,
                                C.TUENNAME,B.UJPAPARV AS TUUSERNA,A.UJPAPARV AS OFFCCODE,D.TBDTL2 FROM
                                FROM WEBNFLIB.URUSERPF C
                                INNER JOIN WEBNFLIB.URUSOBNOPF B ON B.TUUSERID=C.TUFUSRID 
                                INNER JOIN WEBNFLIB.URUSOBNOPF A ON A.TUUSERID=C.TUFUSRID 
                                INNER JOIN CDFLIB.CDTABDPF D ON D.TBCODE=A.UJPAPARV 
                                WHERE B.UJPAPARN = 'FWD_FRTP' AND A.UJPAPARN = 'FWD_OFFC' AND C.TUACTIVE=1 AND C.TUCLASID IN('A','B') AND D.TBTYPE='OFFC'

底层方法进行动态传值，进行SQL拼接：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
public bool GetOffcInfo(IBaseDataAccess baseReadOnlyDataAccess, DataSet ds,ParmArray parmArray)
        {
            //TBDTL2=中文名，TBDTL1=英文名，TBCODE=代码
            string sql = @" SELECT DISTINCT C.TUUSERNA,C.TUCLASID,C.TUUSERID,C.TUEMAIL,C.TUPHONE,C.TUACEMAIL,C.TUACMOBILE,C.TUCOMP,C.TUACTIVE,
                                C.TUENNAME,B.UJPAPARV AS TUUSERNA,A.UJPAPARV AS OFFCCODE,D.TBDTL2 FROM
                                FROM WEBNFLIB.URUSERPF C
                                INNER JOIN WEBNFLIB.URUSOBNOPF B ON B.TUUSERID=C.TUUSERID 
                                INNER JOIN WEBNFLIB.URUSOBNOPF A ON A.TUUSERID=C.TUUSERID 
                                INNER JOIN CDFLIB.CDTABDPF D ON D.TBCODE=A.UJPAPARV 
                                WHERE B.UJPAPARN = 'FWD_FRTP' AND A.UJPAPARN = 'FWD_OFFC' AND C.TUACTIVE=1 AND C.TUCLASID IN('A','B') AND D.TBTYPE='OFFC'
                                UNION
                              SELECT DISTINCT C.TUUSERNA,C.TUCLASID,C.TUUSERID,C.TUEMAIL,C.TUPHONE,C.TUACEMAIL,C.TUACMOBILE,C.TUCOMP,C.TUACTIVE,
                                C.TUENNAME,B.UJPAPARV AS TUUSERNA,A.UJPAPARV AS OFFCCODE,D.TBDTL2 FROM
                                FROM WEBNFLIB.URUSERPF C
                                INNER JOIN WEBNFLIB.URUSOBNOPF B ON B.TUUSERID=C.TUFUSRID 
                                INNER JOIN WEBNFLIB.URUSOBNOPF A ON A.TUUSERID=C.TUFUSRID 
                                INNER JOIN CDFLIB.CDTABDPF D ON D.TBCODE=A.UJPAPARV 
                                WHERE B.UJPAPARN = 'FWD_FRTP' AND A.UJPAPARN = 'FWD_OFFC' AND C.TUACTIVE=1 AND C.TUCLASID IN('A','B') AND D.TBTYPE='OFFC' ";
            ParmArray keyArray = new ParmArray();
            if (parmArray.parmNameList.Contains("TBDTL2"))
            {
                string strTBDTL2 = parmArray.GetParmValue("TBDTL2").ToString().Trim();
                keyArray.Add("TBDTL2",  "%"+strTBDTL2+"%");
                sql += " AND D.TBDTL2 LIKE ? ";
            }

            if (parmArray.parmNameList.Contains("OFFCCODE"))
            {
                keyArray.Add("OFFCCODE", parmArray.GetParmValue("OFFCCODE").ToString().Trim()+"%");
                sql += " AND A.UJPAPARV LIKE ? ";
            }

            if (parmArray.parmNameList.Contains("TUUSERNA"))
            {
                keyArray.Add("TUUSERNA",parmArray.GetParmValue("TUUSERNA").ToString().Trim());
                sql += " AND C.TUUSERNA = ? ";
            }
            return baseReadOnlyDataAccess.FillDataSetByCondition(sql, ds, keyArray);
        }

这样sql，除了大量重复，臃肿，而且，传值只能加载最后面。不可行。

　　改进方法：采用oracle的case when方法进行操作。

Case….When的用法：

CASE 列名

WHEN 条件1 THEN 选项1

WHEN 条件2 THEN 选项2

ELSE 默认值 END

SQL语句如下：

1
2
3
4
5
6
7
SELECT DISTINCT C.TUUSERNA,C.TUCLASID,C.TUUSERID,C.TUEMAIL,C.TUPHONE,C.TUACEMAIL,C.TUACMOBILE,C.TUCOMP,C.TUACTIVE,
                                C.TUENNAME,B.UJPAPARV AS TUUSERNA,A.UJPAPARV AS OFFCCODE,D.TBDTL2 FROM
                                (SELECT (CASE WHEN A.TUFUSRID>0 THEN A.TUFUSRID ELSE A.TUUSERID END) AS CUSERID,A.* FROM WEBNFLIB.URUSERPF A) C
                                INNER JOIN WEBNFLIB.URUSOBNOPF B ON B.TUUSERID=C.CUSERID 
                                INNER JOIN WEBNFLIB.URUSOBNOPF A ON A.TUUSERID=C.CUSERID 
                                INNER JOIN CDFLIB.CDTABDPF D ON D.TBCODE=A.UJPAPARV 
                                WHERE B.UJPAPARN = 'FWD_FRTP' AND A.UJPAPARN = 'FWD_OFFC' AND C.TUACTIVE=1 AND C.TUCLASID IN('A','B') AND D.TBTYPE='OFFC'