本文目录
SQL查询语句在线求
这个问题取决与你用那种Database。我的SQL能在MS SQL Server 2005中运行。1.如果要查询DJY里0和1分别有多少应该怎么写语句 --------------------------------------------SELECT SUM(CASE djy WHEN ’1’ THEN 1 ELSE 0 END) AS NumOf1,SUM(CASE djy WHEN ’0’ THEN 1 ELSE 0 END) AS NumOf0, fjgFROM AGROUP BY fjg//SUM意为总和,CASE djy WHEN ’1’ THEN 1 ELSE 0 END表示当djy的值为’1’时候,就在总和加1,此外加0,END表示结束。//GROUP BY为分组,这个概念较复杂,需要自己实践理解。这里GROUP BY表示以fjg为基准,来选出diy为1,0的数目//NumOf1为自己定义的名字--1的数目,NumOf0通理2.如果FJG是22 DJY0和1分别有多少语句应该怎么写--------------------------------------------SELECT SUM(CASE djy WHEN ’1’ THEN 1 ELSE 0 END) AS NumOf1,SUM(CASE djy WHEN ’0’ THEN 1 ELSE 0 END) AS NumOf0, fjgFROM AWHERE fjg=22GROUP BY fjg//WHERE fig=22表示当fig=22的时候。如果fig的data type是String或其它的文本类型,那么WHERE fig=22要改成:WHERE fig=’22’
sql 查询语句 ,在线等 急
select a.工号,a.销售额,b.分入额,c.分出额,(isnull(销售额,0) + isnull(分出额,0) + isnull(分入额,0)) as 实额 from a,b,c where a.工号 = b.工号 and a.工号 = c.工号
sql查询语句,在线等,急,急!QQ:495537346
/*====================创建表及导入数据====================*/create table teacher(id int,name varchar(30),age int,salary numeric(18,2),school int)GOinsert into teacherselect 101,’Alice’,32,4000,201 unionselect 102,’Bernard’,41,3700,202GOcreate table School(id int,name varchar(100),address varchar(200),)GOinsert into Schoolselect 201,’FLS’,’A.B.C’ unionselect 202,’CRS’,’D.C.B’GOcreate table Subject(id int,name varchar(100),attribute varchar(100))GOinsert into Subjectselect 301,’English’,’Optional’ unionselect 302,’Chinese’,’Optional’GOcreate table Class(id int,name varchar(100))GOinsert into Classselect 401,’A’ unionselect 402,’B’create table Score(id int,teacher int,subject int,class int,score int)GOinsert into Scoreselect 501,101,301,401,98GO--1.select name from teacher where salary=(select max(salary) from teacher)GO/*如果是每个学校的老师,应该需要有学校的资料才对*/select School, max(salary) as salary_maxfrom teachergroup by SchoolGO--2.select name from teacher where age》=40 and salary《4000GO--3.select name, salary_s / countafrom(select school,count(*) counta,sum(salary) salary_s from teacherwhere salary《4000group by school) A left join school s on A.school=s.id--4.update teacherset salary=salary+500where salary《4000 and school=(select id from school where name=’CRS’)GO--5create view vTeacherAddrAS select t.name, t.age, s.name, s.address from teacher t left join school s on t.school=s.idGO--6.select subject.name,count(*)from Score s left join teacher t on s.teacher=t.idleft join subject on s.subject=subject.idwhere t.name in (’Alice’,’Bernard’) and subject.attribute=’Optional’group by subject.namehaving count(*)》10--7.select c.name,subject.name,sum(score)/count(*)from Score s left join class c on s.class=c.idleft join subject on s.subject=subject.idgroup by c.name,subject.nameorder by c.name,subject.nameGO