那后面还有好多的筛选条件怎么办啊? sql += " where (A.keshen='审核' or A.keshen='已打印')"; sql += " and A.keshen=B.keshen"; //sql += " where keshen is null"; if (DaYing.MyprintMonth != "") { sql += " and A.luRuYueFen='" + DaYing.MyprintMonth + "'"; sql += " and A.luRuYueFen=B.luRuYueFen"; } if (DaYing.MyprintYear != "") { sql += " and A.luRuNian='" + DaYing.MyprintYear + "'"; sql += " and B.luRuNian=A.luRuNian"; } sql += " group by " + group + ""; sql += " order by (";
使用HAVING 子句来做限制条件
例如: SELECT titles.pub_id, AVG(titles.price) FROM titles INNER JOIN publishers ON titles.pub_id = publishers.pub_id WHERE publishers.state = 'CA' GROUP BY titles.pub_id HAVING AVG(price) > 10
sql = "select A.danWei as danwei,A.luRuYueFen,A.luRuNian,SUM(A.gongZiE) as gongZiE,SUM(A.gongShu) as gongShu,SUM(A.jin_ru_gongShu) as jin_ru_gongShu,"; group = "A.danwei,A.luRuYueFen,A.luRuNian"; Label2.Text = DaYing.MyprintMonth.ToString() + "月份"; } sql += "SUM(A.jin_ru_jinE) as jin_ru_jinE,SUM(A.jin_yeBanJinTie) as jin_yeBanJinTie, "; sql += "SUM(A.jin_gangWangYuanBuTie) as jin_gangWangYuanBuTie,SUM(A.jin_jiDianBuTie) as jin_jiDianBuTie,SUM(A.jin_beiZhu) as jin_beiZhu,"; sql += "SUM(A.qi_shang_gongShu) as qi_shang_gongShu,SUM(A.qi_shang_gongZi) as qi_shang_gongZi,SUM(A.qi_bing_gongShu) as qi_bing_gongShu,"; sql += "SUM(A.qi_bing_gongZi) as qi_bing_gongZi,SUM(A.qi_jie_gongShu) as qi_jie_gongShu,SUM(A.qi_jie_jinE) as qi_jie_jinE, "; sql += "SUM(A.qi_taiJieJiaJiang) as qi_taiJieJiaJiang,SUM(A.qi_chuQinJiaJiang) as qi_chuQinJiaJiang, "; sql += "SUM(A.qi_biaoZhunHuaJiang) as qi_biaoZhunHuaJiang,SUM(A.qi_sanBaojiangJin) as qi_sanBaojiangJin,SUM(A.qi_beiZhu) as qi_beiZhu,"; sql += "SUM(A.huaKuan) as huaKuan,SUM(A.yinFuGongZi) as yinFuGongZi,SUM(A.shui_yangLaoJin) as shui_yangLaoJin, "; sql += "SUM(A.shui_shiYeJin) as shui_shiYeJin,SUM(A.shui_yiBaoJin) as shui_yiBaoJin,SUM(A.shui_zhuFangJin) as shui_zhuFangJin, "; sql += "SUM(A.jiShuiGongZi) as jiShuiGongZi,SUM(A.dai_geRenShui)as dai_geRenShui ,SUM(A.dai_biaoZhuFei) as dai_biaoZhuFei,SUM(A.dai_beiZhu1) as dai_beiZhu1,SUM(A.dai_baiZhu2) as dai_baiZhu2,"; sql += "SUM(A.shiFaGongZi) as shiFaGongZi FROM tb_xiangXiGongZiBiao as A inner join sum(case B.gongzhong when '退休返聘' then 0 else 1 end ) as renshu from tb_xiangXiGongZiBiao as B on A.danwei=B.danwei ";
可以在select后面跟子查询啊 比如:select (select count(*) from a where 工人 = 男的) as "人数" ....... 不知道合不合你用
谢谢大家,已经解决了,大家给了我很多的提示,我慢慢调试,最后 sum(case gongzhong when '退休返聘' 0 else 1 end) as renshu 是正确的,在此贴出来,希望能给遇到此问题的同学帮助。。其实实现的方法很多,主要是我这里面条件和字段太多,这样做是最好的。
select sum(a.工资), sum(a.工数), c.人数 from 表 a inner join (select sum(b.人数) as 人数, b.工号 from 表 b where b.工种 != '临时工') c on a.工号 = c.工号
sql += " and A.keshen=B.keshen";
//sql += " where keshen is null";
if (DaYing.MyprintMonth != "")
{
sql += " and A.luRuYueFen='" + DaYing.MyprintMonth + "'";
sql += " and A.luRuYueFen=B.luRuYueFen";
}
if (DaYing.MyprintYear != "")
{
sql += " and A.luRuNian='" + DaYing.MyprintYear + "'";
sql += " and B.luRuNian=A.luRuNian";
}
sql += " group by " + group + "";
sql += " order by (";
SELECT titles.pub_id, AVG(titles.price)
FROM titles INNER JOIN publishers
ON titles.pub_id = publishers.pub_id
WHERE publishers.state = 'CA'
GROUP BY titles.pub_id
HAVING AVG(price) > 10
group = "A.danwei,A.luRuYueFen,A.luRuNian"; Label2.Text = DaYing.MyprintMonth.ToString() + "月份";
}
sql += "SUM(A.jin_ru_jinE) as jin_ru_jinE,SUM(A.jin_yeBanJinTie) as jin_yeBanJinTie, ";
sql += "SUM(A.jin_gangWangYuanBuTie) as jin_gangWangYuanBuTie,SUM(A.jin_jiDianBuTie) as jin_jiDianBuTie,SUM(A.jin_beiZhu) as jin_beiZhu,";
sql += "SUM(A.qi_shang_gongShu) as qi_shang_gongShu,SUM(A.qi_shang_gongZi) as qi_shang_gongZi,SUM(A.qi_bing_gongShu) as qi_bing_gongShu,";
sql += "SUM(A.qi_bing_gongZi) as qi_bing_gongZi,SUM(A.qi_jie_gongShu) as qi_jie_gongShu,SUM(A.qi_jie_jinE) as qi_jie_jinE, ";
sql += "SUM(A.qi_taiJieJiaJiang) as qi_taiJieJiaJiang,SUM(A.qi_chuQinJiaJiang) as qi_chuQinJiaJiang, ";
sql += "SUM(A.qi_biaoZhunHuaJiang) as qi_biaoZhunHuaJiang,SUM(A.qi_sanBaojiangJin) as qi_sanBaojiangJin,SUM(A.qi_beiZhu) as qi_beiZhu,";
sql += "SUM(A.huaKuan) as huaKuan,SUM(A.yinFuGongZi) as yinFuGongZi,SUM(A.shui_yangLaoJin) as shui_yangLaoJin, ";
sql += "SUM(A.shui_shiYeJin) as shui_shiYeJin,SUM(A.shui_yiBaoJin) as shui_yiBaoJin,SUM(A.shui_zhuFangJin) as shui_zhuFangJin, ";
sql += "SUM(A.jiShuiGongZi) as jiShuiGongZi,SUM(A.dai_geRenShui)as dai_geRenShui ,SUM(A.dai_biaoZhuFei) as dai_biaoZhuFei,SUM(A.dai_beiZhu1) as dai_beiZhu1,SUM(A.dai_baiZhu2) as dai_baiZhu2,";
sql += "SUM(A.shiFaGongZi) as shiFaGongZi FROM tb_xiangXiGongZiBiao as A inner join sum(case B.gongzhong when '退休返聘' then 0 else 1 end ) as renshu from tb_xiangXiGongZiBiao as B on A.danwei=B.danwei ";
不知道合不合你用
sum(case gongzhong when '退休返聘' 0 else 1 end) as renshu 是正确的,在此贴出来,希望能给遇到此问题的同学帮助。。其实实现的方法很多,主要是我这里面条件和字段太多,这样做是最好的。