日期:2014-05-17  浏览次数:20761 次

请问如何把一个数组字段赋值入查询语句?
现在有个数组
String[] c = {"延庆县","怀柔区","密云县","昌平区","顺义区","平谷区","门头沟区","北京市","通州区","虎山区","大兴区"};

利用for循环 执行了11次循环 完成下面的一句SQL:

select c[i] as country,(select count(*) from aa where area = c[i]) as num from dual;


这样执行了11次循环,就开关了11次数据库
我现在想执行1句SQL完成这个工作
只开关1次数据库
主要是原来的方法运行效率太低了,想优化下SQL语句自己还不会...

想要得到SQL查询结果是这样的
country num
延庆县 11
怀柔区 15
密云县 25
....





------解决方案--------------------
SQL code

select distinct(t.area) ,(select count(1) as num from aa s where t.area=s.area) from aa t

------解决方案--------------------
C# code

string oraclesql;
            string sql = "select country,count(*) num from aa where area in({0})";
            StringBuilder sb = new StringBuilder();
            string[] c = { "延庆县", "怀柔区", "密云县", "昌平区", "顺义区", "平谷区", "门头沟区", "北京市", "通州区", "虎山区", "大兴区" };
            foreach (string s in c)
            {
                sb.Append(string.Format("'{0}',", s));
            }
            oraclesql = string.Format(sql, sb.ToString().TrimEnd(','));
            //用oraclesql在数据库中执行