internal static string Collections(string fromDate, string endDate, string wherePart)
{
string sql = "with v_t as({0}) select count(myselectid) from v_t";
var sqlBuilder = new StringBuilder();
for (int i = 0; i < 10; i++)
{
sqlBuilder.Append("(SELECT MySelectID FROM [mysoufun].[dbo].[MySelect_" + i + @"]
WHERE " + wherePart + @"
AND (createtime BETWEEN @fromDate AND @endDate)
)
UNION ALL ");
}
var chars = "UNION ALL".ToCharArray();
sql = string.Format(sql, sqlBuilder.ToString().TrimEnd(chars));
var sqlparam = new List <SqlParameter>();
sqlparam.Add(new SqlParameter()
{
ParameterName = "@fromDate", Value = fromDate
});
sqlparam.Add(new SqlParameter()
{
ParameterName = "@endDate", Value = endDate
});
return(SlDatabase.ExecuteScalar(connection, sql, sqlparam.ToArray()).ToString());
}
internal static string Total(string fromDate, string endDate)
{
string sql = @"declare @date datetime
declare @sum int
set @sum=0
set @date=@fromDate
while @date<@endDate
begin
with v_b as(({0}) union ({1}) union ({2}))
select @sum=@sum+COUNT(userid) from v_b
set @date=@date+1
end
select @sum/7";
var sqlBuilder = new StringBuilder();
for (int i = 0; i < 10; i++)
{
sqlBuilder.Append("(SELECT userid FROM [mysoufun].[dbo].[MySelect_" + i + @"]
with (nolock) where type='new' AND roomid !=''
AND (createtime BETWEEN @date and @date+1)
)
UNION ALL " );
}
var sqlBuilder1 = new StringBuilder();
for (int i = 0; i < 10; i++)
{
sqlBuilder1.Append("(SELECT userid FROM [mysoufun].[dbo].[MySelect_" + i + @"]
with (nolock) where type='new' AND FangYuanID!=''
AND (createtime BETWEEN @date and @date+1)
)
UNION ALL " );
}
var sqlBuilder2 = new StringBuilder();
for (int i = 0; i < 10; i++)
{
sqlBuilder2.Append("(SELECT userid FROM [mysoufun].[dbo].[MySelect_" + i + @"]
with (nolock) where type='new' AND houseid!='' AND (FangYuanID='' OR FangYuanID IS NULL) AND (roomid ='' OR roomid IS NULL)
AND (createtime BETWEEN @date and @date+1)
)
UNION ALL " );
}
var chars = "UNION ALL".ToCharArray();
sql = string.Format(sql, sqlBuilder.ToString().TrimEnd(chars), sqlBuilder1.ToString().TrimEnd(chars), sqlBuilder2.ToString().TrimEnd(chars));
var sqlparam = new List <SqlParameter>();
sqlparam.Add(new SqlParameter()
{
ParameterName = "@fromDate", Value = fromDate
});
sqlparam.Add(new SqlParameter()
{
ParameterName = "@endDate", Value = endDate
});
return(SlDatabase.ExecuteScalar(connection, sql, sqlparam.ToArray()).ToString());
}
static void Main(string[] args)
{
string myfang_test = ConfigurationManager.ConnectionStrings["myfang_test"].ConnectionString;
/*
* string myfang_w = ConfigurationManager.ConnectionStrings["myfang_w"].ConnectionString;
* try
* {
* List<MySelect> list = HandleData.GetData(myfang_test);
* Console.WriteLine("导入" + list.Count);
* int i = 0;
* list.ForEach(en => { if (HandleData.Insert(myfang_w, en)) { Console.WriteLine(++i); } });
* Console.Write("完成");
*
* Console.ReadKey();
* }
* catch (Exception e)
* {
* Console.WriteLine("error:" + e.ToString());
* }*/
string sql = @"select count(*) from myselect_0
where city in (@city1,@city2) and type in (@t1,@t2,@t3)";
var param = new List <SqlParameter>();
param.Add(new SqlParameter {
ParameterName = "@city1", Value = "北京"
});
param.Add(new SqlParameter {
ParameterName = "@city2", Value = "上海"
});
param.Add(new SqlParameter {
ParameterName = "@t1", Value = "esf"
});
param.Add(new SqlParameter {
ParameterName = "@t2", Value = "new"
});
param.Add(new SqlParameter {
ParameterName = "@t3", Value = "rent"
});
var count = SlDatabase.ExecuteScalar(myfang_test, sql, param.ToArray());
Console.WriteLine(count);
Console.ReadKey();
}
internal static string NewUser(string fromDate, string endDate, string wherePart)
{
string sql = @"declare @date datetime
declare @sum int
set @sum=0
set @date=@fromDate
while @date<@endDate
begin
with v_b as({0})
select @sum=@sum+COUNT(userid) from v_b
set @date=@date+1
end
select @sum/7";
var sqlBuilder = new StringBuilder();
for (int i = 0; i < 10; i++)
{
sqlBuilder.Append("(SELECT userid FROM [mysoufun].[dbo].[MySelect_" + i + @"]
with (nolock) where " + wherePart + @"
AND (createtime BETWEEN @date and @date+1)
)
UNION " );
}
var chars = "UNION ".ToCharArray();
sql = string.Format(sql, sqlBuilder.ToString().TrimEnd(chars));
var sqlparam = new List <SqlParameter>();
sqlparam.Add(new SqlParameter()
{
ParameterName = "@fromDate", Value = fromDate
});
sqlparam.Add(new SqlParameter()
{
ParameterName = "@endDate", Value = endDate
});
return(SlDatabase.ExecuteScalar(connection, sql, sqlparam.ToArray()).ToString());
}