我之前是遇到过一个,那个语句是,select a from c,sum d, round(y,2) from t where o group by u ,这是能查出结果的,但是后来我自己又加了一个from,结果就报错了……
select a from b union all select a from c ...
select a from c,sum d, round(y,2) from t where o group by u这个语句能查出来就见鬼了。
我是用java做的一个项目。数据库是mysql
select a from c,sum d, round(y,2) from t where o group by u 这个语句在MYSQL中是不可能查出结果的。
你在MYSQL中测试过 select a from c,sum d, round(y,2) from t where o group by u?
是的,实际句子比这个还长,后面还多了两个left join,可以的
只能用一个from, select a from b,c where c但是如果用sub query的话可以写多个吧,比如说 SELECT number FROM tablename WHERE number > (SELECT AVG(number) FROM tablename); 再比如SELECT STKFIRM FROM STOCK WHERE NATCODE IN(SELECT NATCODE FROM NATION WHERE NATNAME = 'Australia')
请你贴完整SQL语句出来看看,有这样的SQL语句?
select product.productName as name, product.productcode, AA.problem_total, AA.problem_solve, AA.problem_resolve,AA.problem_percent, BB.question_total, BB.question_solve, BB.question_resolve, BB.question_percent, DD.bug_total, DD.bug_solve, DD.bug_resolve, DD.bug_percent from dict_product product left join (select count(*) as problem_total, sum(case when cp.dealstatecode in('DS004','DS005','DS006','DS007','DS008') then 1 else 0 end ) as problem_solve, sum(case when cp.dealstatecode in('DS001','DS002','DS003','DS009') then 1 else 0 end) as problem_resolve, round( sum( case when cp.dealstatecode in('DS004','DS005','DS006','DS007','DS008') then 1 else 0 end )/count(*)*100, 2 ) problem_percent, cp.productcode from crm_clientproblem cp ,dict_clientproblemtype p where 1=1 and cp.isdelete=0 and p.problemtypecode = cp.problemtypecode and p.flag=1 and cp.citycode<>110200 and cp.provincecode<>310000 and isdelete = 0 group by cp.productcode )as AA on AA.productcode=product.productcode left join (select count(*) as question_total, sum(case when cp.dealstatecode in('DS004','DS005','DS006','DS007','DS008') then 1 else 0 end ) as question_solve, sum(case when cp.dealstatecode in('DS001','DS002','DS003','DS009') then 1 else 0 end) as question_resolve, round( sum( case when cp.dealstatecode in('DS004','DS005','DS006','DS007','DS008') then 1 else 0 end )/count(*)*100, 2 ) question_percent, cp.productcode from crm_clientproblem cp ,dict_clientproblemtype p where 1=1 and cp.isdelete=0 and p.problemtypecode = cp.problemtypecode and p.flag=0 and cp.citycode<>110200 and cp.provincecode<>310000 and isdelete = 0 group by cp.productcode ) as BB on BB.productcode=product.productcode left join (select count(*) as bug_total, sum(case when cp.dealstatecode in('DS004','DS005','DS006','DS007','DS008') then 1 else 0 end ) as bug_solve, sum(case when cp.dealstatecode in('DS001','DS002','DS003','DS009') then 1 else 0 end) as bug_resolve, round( sum( case when cp.dealstatecode in('DS004','DS005','DS006','DS007','DS008') then 1 else 0 end )/count(*)*100, 2 ) bug_percent, cp.productcode from crm_clientproblem cp ,dict_clientproblemtype p where 1=1 and cp.isdelete=0 and p.problemtypecode = cp.problemtypecode and p.flag=4 and cp.citycode<>110200 and cp.provincecode<>310000 and isdelete = 0 group by cp.productcode ) as DD on DD.productcode=product.productcode where 1=1 order by name asc
上述SQL语句楼主仔细研究没有 select a from c,sum d, round(y,2) from t where o group by u 有吗?
select a from b,c where c...
生成笛卡积
select 字段 from tableA,tableB where……
union all
select a from c
...
这个语句在MYSQL中是不可能查出结果的。
SELECT number FROM tablename WHERE number > (SELECT AVG(number) FROM tablename); 再比如SELECT STKFIRM FROM STOCK WHERE NATCODE IN(SELECT NATCODE FROM NATION
WHERE NATNAME = 'Australia')
select product.productName as name, product.productcode, AA.problem_total, AA.problem_solve, AA.problem_resolve,AA.problem_percent,
BB.question_total, BB.question_solve, BB.question_resolve, BB.question_percent,
DD.bug_total, DD.bug_solve, DD.bug_resolve, DD.bug_percent
from dict_product product left join (select count(*) as problem_total, sum(case when cp.dealstatecode in('DS004','DS005','DS006','DS007','DS008') then 1 else 0 end ) as problem_solve,
sum(case when cp.dealstatecode in('DS001','DS002','DS003','DS009') then 1 else 0 end) as problem_resolve,
round( sum( case when cp.dealstatecode in('DS004','DS005','DS006','DS007','DS008') then 1 else 0 end )/count(*)*100, 2 ) problem_percent, cp.productcode
from crm_clientproblem cp ,dict_clientproblemtype p where 1=1 and cp.isdelete=0 and p.problemtypecode = cp.problemtypecode
and p.flag=1 and cp.citycode<>110200 and cp.provincecode<>310000 and isdelete = 0 group by cp.productcode )as AA on AA.productcode=product.productcode
left join (select count(*) as question_total,
sum(case when cp.dealstatecode in('DS004','DS005','DS006','DS007','DS008') then 1 else 0 end ) as question_solve,
sum(case when cp.dealstatecode in('DS001','DS002','DS003','DS009') then 1 else 0 end) as question_resolve,
round( sum( case when cp.dealstatecode in('DS004','DS005','DS006','DS007','DS008') then 1 else 0 end )/count(*)*100, 2 ) question_percent, cp.productcode
from crm_clientproblem cp ,dict_clientproblemtype p where 1=1 and cp.isdelete=0 and p.problemtypecode = cp.problemtypecode
and p.flag=0 and cp.citycode<>110200 and cp.provincecode<>310000 and isdelete = 0 group by cp.productcode ) as BB on BB.productcode=product.productcode
left join (select count(*) as bug_total,
sum(case when cp.dealstatecode in('DS004','DS005','DS006','DS007','DS008') then 1 else 0 end ) as bug_solve,
sum(case when cp.dealstatecode in('DS001','DS002','DS003','DS009') then 1 else 0 end) as bug_resolve,
round( sum( case when cp.dealstatecode in('DS004','DS005','DS006','DS007','DS008') then 1 else 0 end )/count(*)*100, 2 ) bug_percent, cp.productcode
from crm_clientproblem cp ,dict_clientproblemtype p where 1=1 and cp.isdelete=0 and p.problemtypecode = cp.problemtypecode
and p.flag=4 and cp.citycode<>110200 and cp.provincecode<>310000 and isdelete = 0 group by cp.productcode ) as DD on DD.productcode=product.productcode
where 1=1 order by name asc
select a from c,sum d, round(y,2) from t where o group by u
有吗?