tj.sql

等级考试辅助软件

select * into tonji from
(SELECT DISTINCT 
(SELECT name1 FROM kemu WHERE name1="一级 ") as 科目 ,
(SELECT COUNT(*) FROM bmk WHERE LEFT(RIGHT(bmh,6),2)="12") AS 考试总人数 ,
(SELECT COUNT(*) FROM bmk WHERE (LEFT(RIGHT(bmh,6),2)="12") and (blcjzl IS NULL)) AS 全考人数 ,
(SELECT COUNT(*) FROM bmk WHERE (LEFT(RIGHT(bmh,6),2)="12") and (blcjzl="1")) AS 补考上机人数 ,
(SELECT COUNT(*) FROM bmk WHERE (LEFT(RIGHT(bmh,6),2)="12") and (blcjzl="2")) AS 补考笔试人数 
FROM bmk GROUP BY bmh HAVING COUNT(*)>=0 
UNION ALL
SELECT DISTINCT 
(SELECT name1 FROM kemu WHERE name1="一级 B") as 科目 ,
(SELECT COUNT(*) FROM bmk WHERE LEFT(RIGHT(bmh,6),2)="13") AS 考试总人数 ,
(SELECT COUNT(*) FROM bmk WHERE (LEFT(RIGHT(bmh,6),2)="13") and (blcjzl IS NULL)) AS 全考人数 ,
(SELECT COUNT(*) FROM bmk WHERE (LEFT(RIGHT(bmh,6),2)="13") and (blcjzl="1")) AS 补考上机人数 ,
(SELECT COUNT(*) FROM bmk WHERE (LEFT(RIGHT(bmh,6),2)="13") and (blcjzl="2")) AS 补考笔试人数 
FROM bmk GROUP BY bmh HAVING COUNT(*)>=0
UNION ALL
SELECT DISTINCT 
(SELECT name1 FROM kemu WHERE name1="一级 WPS Office") as 科目 ,
(SELECT COUNT(*) FROM bmk WHERE LEFT(RIGHT(bmh,6),2)="14") AS 考试总人数 ,
(SELECT COUNT(*) FROM bmk WHERE (LEFT(RIGHT(bmh,6),2)="14") and (blcjzl IS NULL)) AS 全考人数 ,
(SELECT COUNT(*) FROM bmk WHERE (LEFT(RIGHT(bmh,6),2)="14") and (blcjzl="1")) AS 补考上机人数 ,
(SELECT COUNT(*) FROM bmk WHERE (LEFT(RIGHT(bmh,6),2)="14") and (blcjzl="2")) AS 补考笔试人数 
FROM bmk GROUP BY bmh HAVING COUNT(*)>=0
UNION ALL
SELECT DISTINCT 
(SELECT name1 FROM kemu WHERE name1="一级 MS Office") as 科目 ,
(SELECT COUNT(*) FROM bmk WHERE LEFT(RIGHT(bmh,6),2)="15") AS 考试总人数 ,
(SELECT COUNT(*) FROM bmk WHERE (LEFT(RIGHT(bmh,6),2)="15") and (blcjzl IS NULL)) AS 全考人数 ,
(SELECT COUNT(*) FROM bmk WHERE (LEFT(RIGHT(bmh,6),2)="15") and (blcjzl="1")) AS 补考上机人数 ,
(SELECT COUNT(*) FROM bmk WHERE (LEFT(RIGHT(bmh,6),2)="15") and (blcjzl="2")) AS 补考笔试人数 
FROM bmk GROUP BY bmh HAVING COUNT(*)>=0
UNION ALL
SELECT DISTINCT 
(SELECT name1 FROM kemu WHERE name1="二级 QBASIC") as 科目 ,
(SELECT COUNT(*) FROM bmk WHERE LEFT(RIGHT(bmh,6),2)="21") AS 考试总人数 ,
(SELECT COUNT(*) FROM bmk WHERE (LEFT(RIGHT(bmh,6),2)="21") and (blcjzl IS NULL)) AS 全考人数 ,
(SELECT COUNT(*) FROM bmk WHERE (LEFT(RIGHT(bmh,6),2)="21") and (blcjzl="1")) AS 补考上机人数 ,
(SELECT COUNT(*) FROM bmk WHERE (LEFT(RIGHT(bmh,6),2)="21") and (blcjzl="2")) AS 补考笔试人数 
FROM bmk GROUP BY bmh HAVING COUNT(*)>=0
UNION ALL
SELECT DISTINCT
(SELECT name1 FROM kemu WHERE name1="二级 C") as 科目 ,
(SELECT COUNT(*) FROM bmk WHERE LEFT(RIGHT(bmh,6),2)="24") AS 考试总人数 ,
(SELECT COUNT(*) FROM bmk WHERE (LEFT(RIGHT(bmh,6),2)="24") and (blcjzl IS NULL)) AS 全考人数 ,
(SELECT COUNT(*) FROM bmk WHERE (LEFT(RIGHT(bmh,6),2)="24") and (blcjzl="1")) AS 补考上机人数 ,
(SELECT COUNT(*) FROM bmk WHERE (LEFT(RIGHT(bmh,6),2)="24") and (blcjzl="2")) AS 补考笔试人数 
FROM bmk GROUP BY bmh HAVING COUNT(*)>=0
UNION ALL
SELECT DISTINCT 
(SELECT name1 FROM kemu WHERE name1="二级 FOXBASE+") as 科目 ,
(SELECT COUNT(*) FROM bmk WHERE LEFT(RIGHT(bmh,6),2)="25") AS 考试总人数 ,
(SELECT COUNT(*) FROM bmk WHERE (LEFT(RIGHT(bmh,6),2)="25") and (blcjzl IS NULL)) AS 全考人数 ,
(SELECT COUNT(*) FROM bmk WHERE (LEFT(RIGHT(bmh,6),2)="25") and (blcjzl="1")) AS 补考上机人数 ,
(SELECT COUNT(*) FROM bmk WHERE (LEFT(RIGHT(bmh,6),2)="25") and (blcjzl="2")) AS 补考笔试人数 
FROM bmk GROUP BY bmh HAVING COUNT(*)>=0
UNION ALL
SELECT DISTINCT 
(SELECT name1 FROM kemu WHERE name1="二级 VB") as 科目 ,
(SELECT COUNT(*) FROM bmk WHERE LEFT(RIGHT(bmh,6),2)="26") AS 考试总人数 ,
(SELECT COUNT(*) FROM bmk WHERE (LEFT(RIGHT(bmh,6),2)="26") and (blcjzl IS NULL)) AS 全考人数 ,
(SELECT COUNT(*) FROM bmk WHERE (LEFT(RIGHT(bmh,6),2)="26") and (blcjzl="1")) AS 补考上机人数 ,
(SELECT COUNT(*) FROM bmk WHERE (LEFT(RIGHT(bmh,6),2)="26") and (blcjzl="2")) AS 补考笔试人数 
FROM bmk GROUP BY bmh HAVING COUNT(*)>=0
UNION ALL
SELECT DISTINCT 
(SELECT name1 FROM kemu WHERE name1="二级 VFP") as 科目 ,
(SELECT COUNT(*) FROM bmk WHERE LEFT(RIGHT(bmh,6),2)="27") AS 考试总人数 ,
(SELECT COUNT(*) FROM bmk WHERE (LEFT(RIGHT(bmh,6),2)="27") and (blcjzl IS NULL)) AS 全考人数 ,
(SELECT COUNT(*) FROM bmk WHERE (LEFT(RIGHT(bmh,6),2)="27") and (blcjzl="1")) AS 补考上机人数 ,
(SELECT COUNT(*) FROM bmk WHERE (LEFT(RIGHT(bmh,6),2)="27") and (blcjzl="2")) AS 补考笔试人数 
FROM bmk GROUP BY bmh HAVING COUNT(*)>=0
UNION ALL
SELECT DISTINCT 
(SELECT name1 FROM kemu WHERE name1="二级 JAVA") as 科目 ,
(SELECT COUNT(*) FROM bmk WHERE LEFT(RIGHT(bmh,6),2)="28") AS 考试总人数 ,
(SELECT COUNT(*) FROM bmk WHERE (LEFT(RIGHT(bmh,6),2)="28") and (blcjzl IS NULL)) AS 全考人数 ,
(SELECT COUNT(*) FROM bmk WHERE (LEFT(RIGHT(bmh,6),2)="28") and (blcjzl="1")) AS 补考上机人数 ,
(SELECT COUNT(*) FROM bmk WHERE (LEFT(RIGHT(bmh,6),2)="28") and (blcjzl="2")) AS 补考笔试人数 
FROM bmk GROUP BY bmh HAVING COUNT(*)>=0
UNION ALL
SELECT DISTINCT 
(SELECT name1 FROM kemu WHERE name1="二级 ACCESS") as 科目 ,
(SELECT COUNT(*) FROM bmk WHERE LEFT(RIGHT(bmh,6),2)="29") AS 考试总人数 ,
(SELECT COUNT(*) FROM bmk WHERE (LEFT(RIGHT(bmh,6),2)="29") and (blcjzl IS NULL)) AS 全考人数 ,
(SELECT COUNT(*) FROM bmk WHERE (LEFT(RIGHT(bmh,6),2)="29") and (blcjzl="1")) AS 补考上机人数 ,
(SELECT COUNT(*) FROM bmk WHERE (LEFT(RIGHT(bmh,6),2)="29") and (blcjzl="2")) AS 补考笔试人数 
FROM bmk GROUP BY bmh HAVING COUNT(*)>=0
UNION ALL
SELECT DISTINCT 
(SELECT name1 FROM kemu WHERE name1="二级 C++") as 科目 ,
(SELECT COUNT(*) FROM bmk WHERE LEFT(RIGHT(bmh,6),2)="2A") AS 考试总人数 ,
(SELECT COUNT(*) FROM bmk WHERE (LEFT(RIGHT(bmh,6),2)="2A") and (blcjzl IS NULL)) AS 全考人数 ,
(SELECT COUNT(*) FROM bmk WHERE (LEFT(RIGHT(bmh,6),2)="2A") and (blcjzl="1")) AS 补考上机人数 ,
(SELECT COUNT(*) FROM bmk WHERE (LEFT(RIGHT(bmh,6),2)="2A") and (blcjzl="2")) AS 补考笔试人数 
FROM bmk GROUP BY bmh HAVING COUNT(*)>=0
UNION ALL
SELECT DISTINCT 
(SELECT name1 FROM kemu WHERE name1="三级 PC技术") as 科目 ,
(SELECT COUNT(*) FROM bmk WHERE LEFT(RIGHT(bmh,6),2)="33") AS 考试总人数 ,
(SELECT COUNT(*) FROM bmk WHERE (LEFT(RIGHT(bmh,6),2)="33") and (blcjzl IS NULL)) AS 全考人数 ,
(SELECT COUNT(*) FROM bmk WHERE (LEFT(RIGHT(bmh,6),2)="33") and (blcjzl="1")) AS 补考上机人数 ,
(SELECT COUNT(*) FROM bmk WHERE (LEFT(RIGHT(bmh,6),2)="33") and (blcjzl="2")) AS 补考笔试人数 
FROM bmk GROUP BY bmh HAVING COUNT(*)>=0
UNION ALL
SELECT DISTINCT 
(SELECT name1 FROM kemu WHERE name1="三级信息管理技术") as 科目 ,
(SELECT COUNT(*) FROM bmk WHERE LEFT(RIGHT(bmh,6),2)="34") AS 考试总人数 ,
(SELECT COUNT(*) FROM bmk WHERE (LEFT(RIGHT(bmh,6),2)="34") and (blcjzl IS NULL)) AS 全考人数 ,
(SELECT COUNT(*) FROM bmk WHERE (LEFT(RIGHT(bmh,6),2)="34") and (blcjzl="1")) AS 补考上机人数 ,
(SELECT COUNT(*) FROM bmk WHERE (LEFT(RIGHT(bmh,6),2)="34") and (blcjzl="2")) AS 补考笔试人数 
FROM bmk GROUP BY bmh HAVING COUNT(*)>=0
UNION ALL
SELECT DISTINCT 
(SELECT name1 FROM kemu WHERE name1="三级网络技术") as 科目 ,
(SELECT COUNT(*) FROM bmk WHERE LEFT(RIGHT(bmh,6),2)="35") AS 考试总人数 ,
(SELECT COUNT(*) FROM bmk WHERE (LEFT(RIGHT(bmh,6),2)="35") and (blcjzl IS NULL)) AS 全考人数 ,
(SELECT COUNT(*) FROM bmk WHERE (LEFT(RIGHT(bmh,6),2)="35") and (blcjzl="1")) AS 补考上机人数 ,
(SELECT COUNT(*) FROM bmk WHERE (LEFT(RIGHT(bmh,6),2)="35") and (blcjzl="2")) AS 补考笔试人数 
FROM bmk GROUP BY bmh HAVING COUNT(*)>=0
UNION ALL
SELECT DISTINCT 
(SELECT name1 FROM kemu WHERE name1="三级数据库技术") as 科目 ,
(SELECT COUNT(*) FROM bmk WHERE LEFT(RIGHT(bmh,6),2)="36") AS 考试总人数 ,
(SELECT COUNT(*) FROM bmk WHERE (LEFT(RIGHT(bmh,6),2)="36") and (blcjzl IS NULL)) AS 全考人数 ,
(SELECT COUNT(*) FROM bmk WHERE (LEFT(RIGHT(bmh,6),2)="36") and (blcjzl="1")) AS 补考上机人数 ,
(SELECT COUNT(*) FROM bmk WHERE (LEFT(RIGHT(bmh,6),2)="36") and (blcjzl="2")) AS 补考笔试人数 
FROM bmk GROUP BY bmh HAVING COUNT(*)>=0)