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
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
sql = @"select A.JID,A.JH,A.CX,A.CZ,B.HZB HZZ,B.ZZB FROM DEF_WELL_TAG A  left JOIN sc_well_location B ON A.JID=B.JID {1} WHERE A.QTID='{0}'
AND  A.JID IN   (SELECT CC.JID
			   FROM   SC_WILD_ZONATION CC  ,
				   SC_LAYER_T       DD                 
			  WHERE 1 = 1
				AND DD.JID = CC.JID)";
string filterStr = string.Empty;
foreach (string filter in filterCondition)
{
  filterStr = filterStr + filter + ",";
}
string oldChart = "B";
string inserSQL = string.Empty;
if (filterStr.IndexOf("有设计报告,") >= 0)
{
  inserSQL += @" JOIN (select distinct jingid as JID from DOC_DOCUMENT_TARGET where path='单井资料\钻井资料\地质设计' and JINGID is not null) C ON {0}.JID=C.JID";
  inserSQL=string.Format(inserSQL,oldChart);
  oldChart="C";
}
if (filterStr.IndexOf("有日产数据,") >= 0)
{
  inserSQL += @" JOIN (select CC.JID FROM DEF_WELL_TAG CC JOIN (SELECT distinct BB.ZJMC FROM DC_GAS_WELL_VOL_DAILY AA JOIN DEF_WELL_TAG BB ON AA.JID=BB.JID)DD ON CC.JH=DD.ZJMC) D ON {0}.JID=D.JID";
  inserSQL = string.Format(inserSQL, oldChart);
  oldChart = "D";
}
if (filterStr.IndexOf("有测井解释,") >= 0)
{
  inserSQL += @" JOIN (select distinct JID from SC_LOG_INTERPRET_RESULT) E ON {0}.JID=E.JID";
  inserSQL = string.Format(inserSQL, oldChart);
  oldChart = "E";
}
if (filterStr.IndexOf("有井斜数据,") >= 0)
{
  inserSQL += @" JOIN (select distinct JID from SC_INDINATION) F ON {0}.JID=F.JID";
  inserSQL = string.Format(inserSQL, oldChart);
  oldChart = "F";
}
if (filterStr.IndexOf("有压恢数据,") >= 0)
{
  inserSQL += @" JOIN ((select distinct JID from  DC_TEST_GAS) union (select distinct JID from  DC_TEST_GATHER)) G ON {0}.JID=G.JID";
  inserSQL = string.Format(inserSQL, oldChart);
  oldChart = "G";
}
if (filterStr.IndexOf("有射孔数据,") >= 0)
{
  inserSQL += @" JOIN (select distinct JID from DC_PERFORATE_T) H ON {0}.JID=H.JID";
  inserSQL = string.Format(inserSQL, oldChart);
  oldChart = "H";
}
if (filterStr.IndexOf("有大分层数据,") >= 0)
{
  inserSQL += @" JOIN (select distinct JID from SC_WILD_ZONATION) I ON {0}.JID=I.JID";
  inserSQL = string.Format(inserSQL, oldChart);
  oldChart = "I";
}
if (filterStr.IndexOf("有FMT/RFT数据,") >= 0)
{
  inserSQL += @" JOIN ((select distinct JID from SC_FMT_LOG_RESULT) union (select distinct JID from SC_RFT_LOG_RESULT)) J ON {0}.JID=J.JID";
  inserSQL = string.Format(inserSQL, oldChart);
  oldChart = "J";
}
if (filterStr.IndexOf("有复杂情况数据,") >= 0)
{
  inserSQL += @" JOIN (select distinct JID from SC_COMPLEX_EXPLAIN) K ON {0}.JID=K.JID";
  inserSQL = string.Format(inserSQL, oldChart);
  oldChart = "K";
}
if (filterStr.IndexOf("有气测录井数据,") >= 0)
{
  inserSQL += @" JOIN (select distinct JID from SC_OILINESS_SHOW) L ON {0}.JID=L.JID";
  inserSQL = string.Format(inserSQL, oldChart);
  oldChart = "L";
}
if (filterStr.IndexOf("有钻井液使用数据,") >= 0)
{
  inserSQL += @" JOIN (select distinct JID from SC_FLUID_CAPABILITY) M ON {0}.JID=M.JID";
  inserSQL = string.Format(inserSQL, oldChart);
  oldChart = "M";
}
sql = string.Format(sql, zyqID, inserSQL);

例2

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
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
sql = @"select distinct A.JID,A.JH,A.CX,A.CZ,B.HZB HZZ,B.ZZB FROM DEF_WELL_TAG A  left JOIN sc_well_location B ON A.JID=B.JID {1} WHERE A.QTID='{0}'
 AND  A.JID IN   (SELECT CC.JID
                 FROM   SC_WILD_ZONATION CC  ,
                     SC_LAYER_T       DD                 
                WHERE 1 = 1
                  AND DD.JID = CC.JID)
  {2} ";

string oldChart = "B";
string inserSQL = string.Empty;
string inserSQL2 = string.Empty;
if (filterCondition["ceDafencengshuju"].Equals("1"))
{//大分层数据
    inserSQL += @" JOIN ( select  distinct jid from SC_WILD_ZONATION ) C ON {0}.JID=C.JID ";
    inserSQL = string.Format(inserSQL, oldChart);
    oldChart = "C";
}
if (filterCondition["ceXifencengshuju"].Equals("1"))
{//细分层数据
    inserSQL += @" JOIN ( select  distinct jid from SC_LAYER_T ) D ON {0}.JID=D.JID ";
    inserSQL = string.Format(inserSQL, oldChart);
    oldChart = "D";
}
if (filterCondition["ceFanwei"].Equals("1"))
{//半径范围
    if (!string.IsNullOrEmpty(filterCondition["teRadius"]))
    {
        string x=filterCondition["x"];
        string y=filterCondition["y"];
        string radius = filterCondition["teRadius"];
           // t.ZZB - {1}) * (t.ZZB - {1}) + (t.HZB - {2}) * (t.HZB - {2});
        inserSQL += @" JOIN ( select   jid ,zzb,hzb from sc_well_location ) E ON {0}.JID=E.JID and 
                     (E.ZZB - {1}) * (E.ZZB - {1}) + (E.HZB - {2}) * (E.HZB - {2})  <  {3}*{3}     ";
        inserSQL = string.Format(inserSQL, oldChart,y,x,radius);
        oldChart = "E";
    }
}
if (filterCondition["ceGuanxixuanjing"].Equals("1"))
{//层系层组选井
    inserSQL += @" JOIN ( select distinct jid, CX, CZ from DEF_WELL_TAG ) F ON {0}.JID=F.JID ";
    if (!string.IsNullOrEmpty(filterCondition["cbCengxi"]))
    {
        inserSQL += "AND F.CX in (select dm from DEF_STRATUM_SYSTEM_TAG where cxjc= '" + filterCondition["cbCengxi"] + "') ";
    }
    if (!string.IsNullOrEmpty(filterCondition["cbCengzu"]))
    {
        inserSQL += "AND F.CZ in (select dm from DEF_STRATUM_ORGANISE where czjc= '" + filterCondition["cbCengzu"] + "') ";
    }

    inserSQL = string.Format(inserSQL, oldChart);
    oldChart = "F";
}
if (filterCondition["ceJinghao"].Equals("1"))
{//井号
    if(!string.IsNullOrEmpty(filterCondition["teJinghao"]))
    {
        inserSQL2 += @" AND A.JH LIKE '%" + filterCondition["teJinghao"] + "%' ";
    }
}
sql = string.Format(sql, zyqID, inserSQL, inserSQL2);