不用输入班级号subs(lyxh,5,3)就能快速从a表统计出各班人数zs，男生数nl女生数ls 到b表

[此贴子已经被作者于2016-12-8 15:05编辑过]

看看是否符合要求
INSERT INTO B (BJ,ZS,NS,LS) SELECT SUBSTR(LYXH,5,3) BJ,COUNT(XB) zs,SUM(IIF(XB='女',1,0)) ns ,SUM(IIF(XB='男',1,0)) ls FROM a GROUP BY BJ

[此贴子已经被作者于2016-12-8 16:26编辑过]

SELECT SUBSTR(lyxh,5,3) as bj,xm,xb FROM a INTO CURSOR tmp1               **把班级数据存入临时表tmp1
SELECT bj,coun(bj) as zs,sum(iif(xb='男',1,0)) as ns,sum(iif(xb='女',1,0)) as ls FROM tmp1 GROUP BY bj ORDER BY bj INTO ARRAY tmp2    **把统计数据存入数组tmp2
INSERT INTO b FROM ARRAY tmp2                                             **把统计的数组数据写入B表中

[此贴子已经被作者于2016-12-12 17:30编辑过]

不懂耶

SQL语句，呵呵，好东西

SELECT SUBSTR(LYXH,5,3) BJ,COUNT(XB) zs,SUM(IIF(XB='女',1,0)) ns ,SUM(IIF(XB='男',1,0)) ls FROM a GROUP BY BJ

SELECT T1.*,NVL(T2.NS,0) NS,NVL(T3.LS,0) LS;
   FROM (SELECT SUBSTR(Lyxh,5,3) BJ, COUNT(*) ZS FROM A GROUP BY 1) T1 ;
      LEFT JOIN (SELECT SUBSTR(Lyxh,5,3) BJ, COUNT(*) NS FROM A WHERE XB='男' GROUP BY 1) T2 ;
         ON T1.BJ=T2.BJ ;
      LEFT JOIN (SELECT SUBSTR(Lyxh,5,3) BJ, COUNT(*) LS FROM A WHERE XB='女' GROUP BY 1) T3 ;
         ON T1.BJ=T3.BJ ;
   ORDER BY 1 ;
   INTO TABLE RS

6楼的做法比我的更简洁，我只是提供另一种思路。不过，6楼把男女生的左右顺序搞倒了。

[此贴子已经被作者于2016-12-13 09:57编辑过]

是楼主没写明白，“男生数nl女生数ls ”

感谢各位的支持，帮助我这只菜鸟解决了很多问题。