table
a b c d
2 s 60 P
2 y 50 F
4 s1 70 p
select t1.a, Aqty,Pqty from( select a,count(*) as Aqty from table group by a) as t1
left join (select a,count(*) as Pqty from table where d='p' group by a) as t2
on t1.a=t2.a
请问上面语句有什么问题,为什么总是出错
[localhost] ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near select a,count(*) as Aqty from table
a b c d
2 s 60 P
2 y 50 F
4 s1 70 p
select t1.a, Aqty,Pqty from( select a,count(*) as Aqty from table group by a) as t1
left join (select a,count(*) as Pqty from table where d='p' group by a) as t2
on t1.a=t2.a
请问上面语句有什么问题,为什么总是出错
[localhost] ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near select a,count(*) as Aqty from table
left join (select a,count(*) as Pqty from table where d='p' group by a) as t2
on t1.a=t2.a单独运行
select a,count(*) as Aqty from table group by a
select a,count(*) as Pqty from table where d='p' group by a
是否有问题
left join (select a,count(*) as Pqty from `table` where d='p' group by a) as t2
on t1.a=t2.a
from
(select a,count(*) as Aqty from `table` group by a) as t1
left join
(select a,count(*) as Pqty from `table` where d='p' group by a) as t2
on t1.a=t2.a
from `table`
group by a
select t1.a from(select * from tb1) as t1 这样也会出问题。
我的是MySql,为什么不可以使用。
年级(G),姓名(N),科目(K),分数(F),是否及格(J)
1 wang 数 59 F
1 wang 语 79 P
我想统计各年级的所有科目全部通过的人数。
请问用Mysql怎么查询
select g,count(*) as 年级人数,sum(if(j=0,1,0)) as 全部通过的人数
from (select g,n,sum(if(j=f,1,0) as j from school group by g,n)
group g
这样是出错的,请问这样要如何查询。
建议你列出你的表结构,并提供测试数据以及基于这些测试数据的所对应正确结果。
参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。
{
Grade varchar(10),
name varchar(20),
kemu varchar(20),
fenshu float,
result bit
}
insert into school select '01','wang','数学',59,1
insert into school select '01','wang','语文',60,0
insert into school select '01','li','语文',80,0
insert into school select '01','li','数学',90,0
insert into school select '02','li2','数学',90,0
insert into school select '02','li2','语文',90,0
...
我想要的结果是
年级 人数 全部科目合格人数
01 2 1
02 N m
我自己使用的查询语句是
select grade,count(*) as 人数,sum(if(result=0,1,0)) as 全部科目合格人数
from (select grade,name,sum(if(result=1,1,0) as result from school group by grade,name)as T1
group grade
但查询时语句出错ERROR 1064:
INNER JOIN
(SELECT GRADE,COUNT(DISTINCT NAME) AS RS FROM SCHOOL A WHERE NOT EXISTS(SELECT 1 FROM SCHOOL WHERE `NAME`=A.`name` AND A.fenshu<=60) GROUP BY GRADE) B