高级SQL.docx

高级SQL问题1：如何查询数据表当天每小时累计数据？300 标签：数据表,数据 0 2010-11-04 16:34 从每天的0点开始，查询每个小时的rzsl累计，也就是每个小时都累加，并且显示在每一行后面 举报 满意答案 好评率：0% 我建了测试的表如下：create table tbl (wl_time timestamp ,rzsl decimal(4,2)输入了几条数据：insert into tbl values(2010-11-01 23:00:00,5.91);insert into tbl values(2010-11-02 00:00:00,5.93);insert into tbl values(2010-11-02 01:00:00,5.93);insert into tbl values(2010-11-02 02:00:00,5.86);insert into tbl values(2010-11-02 03:00:00,5.91);insert into tbl values(2010-11-02 04:00:00,5.97);insert into tbl values(2010-11-02 05:00:00,5.95);写的SQL如下：select t1.wl_time, t1.rzsl, sum(t2.rzsl) from tbl t1, tbl t2where t1.wl_time = t2.wl_time and to_char(t1.wl_time,YYYY-MM_DD)=to_char(t2.wl_time,YYYY-MM_DD) group by t1.wl_time,t1.rzsl order by t1.wl_time运行结果如下：问题2：SQL语句查询问题标签：语句,sql语句 折之翼 2010-10-25 14:13 题目如下：一表格:ID 产品 数量1 A 102 A 203 B 404 B 505C 306 C 407C 40想要的结果：ID 产品 数量2 A 204 B 506 C 40举报 满意答案 好评率：100% create table product (id int primary key,name char(1),q int)insert into product values(1,A,10)insert into product values(2,A,20)insert into product values(3,B,40)insert into product values(4,B,50)insert into product values(5,C,30)insert into product values(6,C,40)insert into product values(7,C,40)答案如下：select p.id,,p.qfrom product p,(select name, max(q) as maxq from product group by name) p1where = and p.q=p1.maxqand not exists (select * from product p2 where = and p2.q=p.q and p2.id100时可以获得30分，当100=a50时可以获得20分，当50=a0时可以获得10分，当a=0时获得0分b占30%，当b10时可以获得30分，当10=b5时可以获得20分，当5=b0时可以获得10分，当b=0时获得0分c占30%，当c3时可以获得30分，当3=c1时我的回答(select a,b,c, case a when a100 then 30 when a50 then 20 when a0 then 10 when a=0 then 0 as ascore, case b when b10 then 30 when b5 then 20 when b0 then 10 when b=0 then 0 as bscore, case c when c3 then 30 when c1 then 20 when c0 then 10 when c=0 then 0 as cscore, ascore+bscore+cscore as ssumfrom score) as t没有经过测试，自己试试吧补充回答： 前面少拷贝了两行，更正一下select a,b,c,t.ssumfrom (select a,b,c, case a when a100 then 30 when a50 then 20 when a0 then 10 when a=0 then 0 as ascore, case b when b10 then 30 when b5 then 20 when b0 then 10 when b=0 then 0 as bscore, case c when c3 then 30 when c1 then 20 when c0 then 10 when c=0 then 0 as cscore, ascore+bscore+cscore as ssum问题4：一道sql面试题（查询语句）id name age1 a 112b113 c124 d 135e12.查询age唯一的那一个 这个应该怎么写select * from table1 where id not in (select age from table1 group by age having count(1)1)如果不考虑效率，可写为：select * from T where age in (select age from T group by age having count(id)=1)问题：sql问题，怎样写最好？已解决问题收藏 转载到空间 转播到腾讯微博 sql问题，怎样写最好？5 标签：sql,语句,select 匿名 2010-10-13 20:14 A表NameID(PK) Name001 张三002 李四B表ID Computer NameID(FK)1 电脑1 0012 电脑2 001要求:查询出电脑数量大于2的用户姓名?select from a,b where id = id group by id, having count(b.id)=2补充回答： 补充说明，在分组时必须使用group by id, 来进行分组，不能单纯用name来分组。否则同name的情况就会有问题。补充回答： 这个应该是最好的写法了 继续追问： select 只有 而group by有。这样编译器不会报错吗？在a表中nameid 和name都是唯一的，有必要都用吗？补充回答： 肯定不会报错，我用过，并且这是分组写法中的一个用法，按nameid分组可以保障无论名字是否唯一都会正确。由于nameid是主键，所以按nameid和name分组，和单独按nameid分组在逻辑上等价的。但是如果group子句中不包含name的话，select中包含name则编译器会报错。所以考虑到重名的情况，所以一般select中最好也把id也加上。问题：select count(*) from 速度很慢Select Count(*)From np_goldidea_vote c, dept_with_path b, np_goldidea_main aWhere c.vote_org_id = b.dept_id And a.main_id = c.main_id And a.column_id = 2 And a.status = 9Group By b.path请大家分析下 如何优化这条SQL 目前查询需要12秒 ，其中C表数据量80W条，b表1440条，a表3891条，请高手帮忙下，谢谢，目前这几张表的关联也都建立了。急单纯把表联接由=的写法改成inner join的写法不会提升效率，因为两者是等价写法。试试下面的写法：Select Count(*)From np_goldidea_vote c, dept_with_path b, (select main_id,column_id, status from np_goldidea_main where column_id = 2 and status = 9 ) aWhere c.vote_org_id = b.dept_id And a.main_id = c.main_idGroup By b.path这个写法的思路是先做a的选择运算，这样在多表连接时，a的数据量会大大降低，从而提高效率。另外，这三个表除了建立默认的主键和唯一性索引外，在b的path上一定要建立索引，这是由于需要按path分组。还有，在连接字段上也要建立索引，这样表连接时，很多时候只需要访问索引而不需要访问表，从而提高效率。补充回答： 没有提高效率也在情理之中，这是数据库优化器处理的结果。大多数情况下，对于不同的等价SQL来说，优化器自动会进行优化。你这个问题单纯从sql的写法上不能提高效率。而应该从数据库优化或者重构表设计的角度来考虑。问题：select a.11 from 表 a join 表 b on a.id=b.id 如何把查询出来的a.11列 update 等成2呢？问题没说清楚，update等成2是什么意思继续追问： 把查询出来的全部结果等于2 补充回答： update a, b set a.11 = 2 where a.id=b.id 继续追问： 大哥，不行啊。 补充回答： 你是什么数据库 继续追问： server 2000 补充回答： sorry, 刚才有事。用这个肯定可以updatea seta.11= 2 where exists (select* fromb where b.id = a.id)继续追问： update mzfp set a.brlx= 2 where exists (select * from cqyb_jzjl where b.id = a.id)无法使用列前缀 a。此前缀必须与 UPDATE 子句 mzfp 中的对象匹配。大哥：还是不行啊，你看一下语法是对的不？补充回答： 我进行了测试，完全没问题。你的sql都没写对update mzfp set a.brlx= 2 where exists (select * from cqyb_jzjl where b.id = a.id)a应该写成mzfp呀，b写成cqyb_jzjl.问题：如何使用SQL语句查询连续编号中缺失的编号?5 标签：sql,语句,oracle 匿名 2010-09-30 19:18 现在偶到一个问题，就是在一个数据库中原来连续编号中删除掉一部分编号后，然后要使用SQL语句查询这些删除掉的编号，并且找出来。假如表中是这样的：myid xm1 小说2 读者5 语文7 数学9 英语现在要使用SQL语句找出编号为3、4、6、8的编号，想问问大家应该怎样来查询，要尽量优化语句，并且效率要高，因为实际中的表的编号可能是上万条甚至更多，所以请大家想想办法。举报 满意答案 好评率：100% 单纯依赖这个表的信息，不存在有一个优化的sql来实现你的目的。从原理上必须要做两个集合的差集，效率问题无法回避。建议，通过做删除标识保留删除的记录就可以很好地解决你的问题，并且这种方法很高效。继续追问： 我现在是要用SQL语句把缺失的编号找出来，请问你有什么办法可以解决？补充回答： id之间每次增长1吗？单独依赖这张表不存在效率高的sql，特别当记录量大的时候继续追问： 简单的说就是1到10之间，缺失了3,4,6,8的编号。该怎样来查询找出缺失掉的编号？这是一道在课堂上老师布置的作业，我想请大家帮帮忙。补充回答： 你可以建一表里面有个id值，里面保存所有编号。然后做两个运算的差集即可。继续追问： 怎么保存和做2个运算的差集？补充回答： 你创建一个保存所有编号的表：create table allno (id int)insert into allno values(1);insert into allno values(2);insert into allno values(3);insert into allno values(4);insert into allno values(5);insert into allno values(6);insert into allno values(7);insert into allno values(8);insert into allno values(9);insert into allno values(10); 当前的表：create table tno (id int,xm varchar(10)insert into tno values(1,小说);insert into tno values(2,读者);insert into tno values(5,语文);insert into tno values(7,数学);insert into tno values(9,英语);然后使用下面的sql：select allno.id,tno.id from allno left outer join tno on (allno.id=tno.id ) where tno.id is null 输出的结果如下：继续追问： 谢谢，除了这个办法还有没有能一次性的用SQL查询出缺失的编号，就是说在已经建好的表，未保存的表中查询出缺失了的编号。 补充回答： 用一条sql语句不能计算出你的那些缺失的编号，原因在于缺失的数据的编号没有任何规律，无法用同一个函数推导出来。 用sql语句怎么实现库存总量更新已解决问题收藏 转载到空间 转播到腾讯微博 用sql语句怎么实现库存总量更新标签：sql,语句,库存 世界杯 2010-09-30 16:36 库存明细表： 件号 数量 001 2002 2002 5003 5 003 4（原来）材料库存表： 件号 数量 0018 0024003 3现在要把库存表更新成： 材料库存表： 件号 数量 00120027003 9 原来材料库存表和现在库存表是一个表吧，请继续解释清楚。和明细表有无关系？继续追问： 和原来是一个表，库存是库存明细的汇总嘛！ 补充回答： 你的为问题是不是根据明细表的内容更新库存表？ 继续追问： 对，是这样的！根据件号 补充回答： 先删除库存表delete from kc;insert into kc (select no,sum(q) from kcmx group by no)继续追问： 不能删除原来的库存，最好用update 补充回答： update kc set q = (select sum(q) from kcmx where kcmx.no = kc.no) 补充回答： 把我测试的建表sql也给你create table kcmx (no varchar(10),q int)create table kc (no varchar(10),q int)insert into kcmx values(001,2);insert into kcmx values(002,2);insert into kcmx values(002,5);insert into kcmx values(003,5);insert into kcmx values(003,4);insert into kc values(001,8);insert into kc values(002,4);insert into kc values(003,3);输出结果继续追问： update kc set q = (select sum(q) from kcmx where kcmx.no = kc.no)显示语法错误啊！你试试补充回答： 你是什么数据库，我这个数据库是可以运行的，没问题。 SQL 查询语句我现在有一张表，里面几W条数据code nameh1234 青霉素h1234 青霉素A 这个是别名 h1234 青霉素B 这个是别名g3434 葡萄糖g3434 葡萄糖A 这个是别名后面的A、B只是一种形式，可能是数字也可能是中文、英文不要和我说 like %A 这样的，后面的不定什么都可以有别名的数量不定，我想做一个查询一个是吧code nameh1234 青霉素g3434 葡萄糖这样的数据查出来一个查询要这样的code nameh1234 青霉素A h1234 青霉素B g3434 葡萄糖A 问题补充： 这个表个主键是code+name 表是code name class other 。还有一些其实我想把后面的也整理出来，但是知道前面的后面也好做了，如果大家方便，最好能帮我写全点谢谢了满意答案 好评率：100% 你这张表的主键是怎么定义的继续追问： 主键是code+name 这张表后面还有些其他字段，我没写上去而已 补充回答： 你想要两个查询，一个是查没有别名的，一个是查别名的。是不是 继续追问： 是的，做2个查询，一个是没有别名的所有数据，一个是有全是别名的所有数据 补充回答： 第一个答案表是tselect distinct t1.code, from t as t1, t as t2 where t1.code = t2.code and length() length()补充回答： 把建表的sql也给你吧create table t (code varchar(10),name varchar(20),class varchar(10),primary key(code,name)insert into t values(h1234,青霉素);insert into t values(h1234,青霉素A);insert into t values(h1234,青霉素B);insert into t values(g3434,葡萄糖);insert into t values(g3434,葡萄糖A);还有运行结果：第一个第二个继续追问： select distinct t1.code, from t as t1, t as t2 where t1.code = t2.code and length() length()后面的数据直接加进去就行了吧，不用再做什么处理吧sql锁表没反应declare c as varchar(10)begin transelect c=a from aaa WITH (TABLOCKX) commit tranbegin tran update aaa set a=0commit tran执行起来没反应,我的数据照样被修改了commit tran后，锁就释放了。所以后面的修改当然起作用了继续追问： begin tranSET LOCK_TIMEOUT 1800select *from aaa WITH (TABLOCKX) update aaa set a=0commit tran 补充回答： 对于上面的代码，update仍然会起作用。因为update在select语句的后面。控制并发是多用户的事务而言，你这是一个事务内部的语句。继续追问： declare c as varchar(10)begin tranSET LOCK_TIMEOUT 180000select *from aaa WITH (TABLOCK) begin tran update aaa set a=0commit tran 这样可以吗补充回答： 请说明你使用SET LOCK_TIMEOUT 180000的目的 继续追问： 设置锁的时间,要不要无所谓,可以去掉 补充回答： 你理解错了，SET LOCK_TIMEOUT 不是设置锁的时间，是指定语句等待锁释放的时间。问题：数据库触发器问题已解决问题收藏 转载到空间 转播到腾讯微博 数据库触发器问题标签：触发器,数据库,触发器问题 冰 2010-09-28 14:52 如：CREATE TRIGGER TestTrig ON TestTabFOR UPDATE AS SAVE TRANSACTION MyName INSERT INTO TestAudit SELECT * FROM inserted IF (error 0)BEGIN ROLLBACK TRANSACTION MyName END 请问在以上触发器中ROLLBACK TRANSACTION MyName 是返回到SAVE TRANSACTION MyName 位置继续向下执行呢，还是回滚中止此次事务？一个数据库的事务可由若干个嵌套子事务组成，因为所有事务都具有原子性，也就是说如果一个事务T中的某个嵌套子t1事务失败了，则该事务T整个都必须回滚，当然也包括事务T中那些已经执行成功的其他嵌套子事务。因为触发器本是一个隐含的事务，所以，触发器中的任一嵌套子事务如果失败都必须回滚整个触发器对应的事务。以上仅是我的看法，仅供参考。继续追问： 使用SAVE TRANSACTION的意义就在于部分回滚，我只想知道以上SAVE TRANSACTION的作用 补充回答： SAVE TRANSACTION 在事务内设置保存点。保存点可以定义在按条件取消某个事务的一部分后，该事务可以返回的一个位置。如果将事务回滚到保存点，则根据需要必须完成其他剩余的 Transact-SQL 语句和 COMMIT TRANSACTION 语句，或者必须通过将事务回滚到起始点完全取消事务。指定保存点名称的 ROLLBACK TRANSACTION 语句只将事务回滚到使用该名称的最近的 SAVE TRANSACTION。补充回答： 对于这里例子应该不会回滚此次事务而是返回到SAVE TRANSACTION MyName 位置继续向下执行。 问题：sql 关联语句通过城市ID，查出所在省已解决问题收藏 转载到空间 转播到腾讯微博 sql 关联语句通过城市ID，查出所在省100 标签：语句,sql,城市 四开 2010-09-28 12:37 数据结构：表1（info）id,name,cityid1,张三,12,李四,13.表2(city)cityid,cityname,type(0-直辖，1-省，2-市，3-区县，4-地区),parentid1,北京,0,02,吉林,1,013,长春,2,2我想一条关联语句查询info表后，得到：1，张三，北京2，李四，吉林请问该如何写这条语句？能否实现？问题补充： 大家看清楚啊，我是要查出info表中所属城市所在的省的名字或者是直辖市的名字，不是查info表中cityid对应的中文名select 表1.name, 表2.cityname from 表1,表2 where 表1.cityid = 表2.cityid补充回答： 少了一个id的列，更正如下select 表1.id, 表1.name, 表2.cityname form 表1, 表2 where 表1.cityid = 表2.cityid补充回答： 表1为person，表2为cityperson中的数据同你给出的一样city中的数据为了说明问题，我又增加两个，如下：1;北京;0;02;吉林;1;013;长春;2;214;长春下;3;1315;长春下下;4;14SQL如下：select id,name,corp from person join(select cityid,cityname,cityname as corp from city where type = 0 or type =1unionselect c1.cityid,c1.cityname,c2.cityname as corp from city as c1, city as c2 where c1.parentid = c2.cityid and c1.type = 2unionselect c1.cityid,c1.cityname,c3.cityname as corp from city as c1, city as c2, city as c3where c1.parentid = c2.cityid and c2.parentid = c3.cityidand c1.type = 3unionselect c1.cityid,c1.cityname,c4.cityname as corp from city as c1, city as c2, city as c3, city as c4where c1.parentid = c2.cityid and c2.parentid = c3.cityid and c3.parentid = c4.cityid and c1.type = 4) as ton (person.cityid = t.cityid)显示结果如下：问题：数据库多表之间连接查询sql数据库三个表，如table1，table2，table。table1中的字段为HM，no1，值为 1,1 2,1table2中的字段为HM，no2，值为 1,2 3,2table中的字段为HM，值为 1 2 3 4现在想做的是做一个查询，查询的结果为HM no1 no21 ， 1， 22 ， 1， 03， 0， 2即取出所有table1和table2中的HM值，并且将对应HM在table1中和table2中的值取出。不存在的则为空或置为0。如 3， 0， 1这条记录，3是取自table2中出现的且在table中有的，0指值为3的HM在table1中没有，2指值为3的HM在table2中的值为2。这是我写的sql：selecttable1.HM,table1.no1,table2.no2 from table1,table2,tablewhere table1.HM in table.hm,and table2.hm in