特急 关于多表合计
把本来分开写的五段SQL，合成一段，但检索结果不对，结果是翻倍的，应该如何写？
合并后的sql
SB.Append(" SELECT ");
SB.Append(" count(aff.SINSA_KEKKA), ");
SB.Append(" sum(asp1.DAT_VALUE), ");
SB.Append(" sum(asp2.DAT_VALUE), ");
SB.Append(" count(client1.MOSHI_KB), ");
SB.Append(" count(client2.SEIYAKU_KB), ");
SB.Append(" aspm.NAME as NAME, ");
SB.Append(" aspm.CARRIER as CARRIER, ");
SB.Append(" client2.HASEI_DATE as HASEI_DATE, ");
SB.Append(" client2.PRO_NAME as PRO_NAME ");
SB.Append(" FROM ");
SB.Append(" aff_sites aff, ");
SB.Append(" asp_dailys asp1, ");
SB.Append(" asp_dailys asp2, ");
SB.Append(" client_results client1, ");
SB.Append(" client_results client2, ");
SB.Append(" asp_mst aspm ");
SB.Append(" WHERE ");
SB.Append(" aff.CLIENT_ID ");
SB.Append(" = ");
SB.Append(" @client_id ");
SB.Append(" AND ");
//プロモーションが「指定なし」以外
if (!Const.SITEINASI_NAYO.Equals(W002007_VO.txtPAGE_NM))
{
SB.Append("aff.PRO_NAME");
SB.Append(" = ");
SB.Append(" @pro_name ");
}
else
{
SB.Append("(1 = 1)");
}
SB.Append(" AND ");
//ASPが「指定なし」以外
if (!Const.SITEINASI_NAYO.Equals(W002007_VO.txtASP_NM))
{
SB.Append("aff.ASP_ID");
SB.Append(" = ");
SB.Append(" @asp_id ");
}
else
{
SB.Append("(1 = 1)");
}
SB.Append(" AND ");
SB.Append("(");
//From入力した
if (!"".Equals(W002007_VO.txtDATE_FROM.Trim()))
{
SB.Append("aff.SINSA_DATE");
SB.Append(" >= ");
SB.Append("@DATE_From");
}
else
{
SB.Append(" 1=1 ");
}
SB.Append(")");
SB.Append(" AND ");
SB.Append("(");
//To入力した
if (!"".Equals(W002007_VO.txtDATE_TO.Trim()))
{
SB.Append("aff.SINSA_DATE");
SB.Append(" <= ");
SB.Append("@DATE_To");
}
else
{
SB.Append(" 1=1 ");
}
SB.Append(")");
SB.Append(" AND ");
SB.Append(" aff.SINSA_KEKKA ");
SB.Append(" = ");
SB.Append(" '1' ");
SB.Append(" AND ");
//媒体状態
if ((W002007_VO.txtCHKPC_CHK == "1") || (W002007_VO.txtCHKMB_CHK == "1"))
{
SB.Append("aspm.CARRIER");
SB.Append(" in ");
SB.Append("(");
string PC = "";
string MB = "";
if (W002007_VO.txtCHKPC_CHK == "1")
{
PC = "'PC'";
if (W002007_VO.txtCHKMB_CHK == "1")
{
MB = ",'MB'";
}
}
else if (W002007_VO.txtCHKMB_CHK == "1")
{
MB = "'MB'";
}
SB.Append(PC + MB);
SB.Append(")");
}
else
{
SB.Append(" (1=1) ");
}
SB.Append(" AND ");
SB.Append(" asp1.CLIENT_ID ");
SB.Append(" = ");
SB.Append(" aff.CLIENT_ID ");
SB.Append(" AND ");
SB.Append(" asp1.OROMO_NAME ");
SB.Append(" = ");
SB.Append(" aff.PRO_NAME ");
SB.Append(" AND ");
SB.Append(" asp1.ASP_ID ");
SB.Append(" = ");
SB.Append(" aff.ASP_ID ");
SB.Append(" AND ");
SB.Append(" asp1.HASEI_DATE ");
SB.Append(" = ");
SB.Append(" aff.SINSA_DATE ");
SB.Append(" AND ");
SB.Append(" asp1.DAT_KIND ");
SB.Append(" = ");
SB.Append(" 'K03' ");
SB.Append(" AND ");
SB.Append(" asp2.CLIENT_ID ");
SB.Append(" = ");
SB.Append(" aff.CLIENT_ID ");
SB.Append(" AND ");
SB.Append(" asp2.OROMO_NAME ");
SB.Append(" = ");
SB.Append(" aff.PRO_NAME ");
SB.Append(" AND ");
SB.Append(" asp2.ASP_ID ");
SB.Append(" = ");
SB.Append(" aff.ASP_ID ");
SB.Append(" AND ");
SB.Append(" asp2.HASEI_DATE ");
SB.Append(" = ");
SB.Append(" aff.SINSA_DATE ");
SB.Append(" AND ");
SB.Append(" asp2.DAT_KIND ");
SB.Append(" = ");
SB.Append(" 'K04' ");
SB.Append(" AND ");
SB.Append(" client1.CLIENT_ID ");
SB.Append(" = ");
SB.Append(" aff.CLIENT_ID ");
SB.Append(" AND ");
SB.Append(" client1.PRO_NAME ");
SB.Append(" = ");
SB.Append(" aff.PRO_NAME ");
SB.Append(" AND ");
SB.Append(" client1.ASP_ID ");
SB.Append(" = ");
SB.Append(" aff.ASP_ID ");
SB.Append(" AND ");
SB.Append(" client1.HASEI_DATE ");
SB.Append(" = ");
SB.Append(" aff.SINSA_DATE ");
SB.Append(" AND ");
SB.Append(" client1.MOSHI_KB ");
SB.Append(" = ");
SB.Append(" '1' ");
SB.Append(" AND ");
SB.Append(" client1.TERM_KB ");
SB.Append(" in ");
SB.Append(" (2,3) ");
SB.Append(" AND ");
SB.Append(" client2.CLIENT_ID ");
SB.Append(" = ");
SB.Append(" aff.CLIENT_ID ");
SB.Append(" AND ");
SB.Append(" client2.PRO_NAME ");
SB.Append(" = ");
SB.Append(" aff.PRO_NAME ");
SB.Append(" AND ");
SB.Append(" client2.ASP_ID ");
SB.Append(" = ");
SB.Append(" aff.ASP_ID ");
SB.Append(" AND ");
SB.Append(" client2.HASEI_DATE ");
SB.Append(" = ");
SB.Append(" aff.SINSA_DATE ");
SB.Append(" AND ");
SB.Append(" client2.SEIYAKU_KB ");
SB.Append(" = ");
SB.Append(" '1' ");
SB.Append(" AND ");
SB.Append(" client2.TERM_KB ");
SB.Append(" = ");
SB.Append(" client1.TERM_KB ");
SB.Append(" AND ");
SB.Append(" aspm.ASP_ID ");
SB.Append(" = ");
SB.Append(" aff.ASP_ID ");
SB.Append(" group by ");
SB.Append(" client2.HASEI_DATE, ");
SB.Append(" client2.ASP_ID, ");
SB.Append(" client2.PRO_NAME ");
SB.Append(" order by ");
SB.Append(" client2.HASEI_DATE asc");
[解决办法]
楼主你贴的是你的程序代码，能否直接在调试中看一下实际的SQL语句是什么？
