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