回复人: wwl1981(小刀) ( ) 信誉:105 2003-3-21 16:04:18 得分:0
是那个家伙对数据下了手脚^_^ create table IKnowYou (userid varchar(30), TableName varchar(50), Action varchar(6), DateT datatime, TrrigerTableColumns....., TrrigerTableColumns.....) create trriger Who_Do_It on table for update as declare userid varchar(30) declare TableName varchar(50) declare Action varchar(6) userid=@@suser_sname Action='update' TableName='tabel' insert into IKnowYou values(userid,TableName,Action,Now,select * from deleted,select * from inserted)
Top 回复人: csdntoll(低调惯了) ( ) 信誉:147 2003-3-21 17:06:52 得分:0
kill all connections to a given databse CREATE PROCEDURE usp_killDBConnections @DBName varchar(50), @withmsg bit=1 AS SET NOCOUNT ON DECLARE @spidstr varchar(8000) DECLARE @ConnKilled smallint SET @ConnKilled=0 SET @spidstr = '' IF db_id(@DBName) < 4 BEGIN PRINT 'Connections to system databases cannot be killed' RETURN END SELECT @spidstr=coalesce(@spidstr,',' )+'kill '+convert(varchar, spid)+ '; ' FROM master..sysprocesses WHERE dbid=db_id(@DBName) IF LEN(@spidstr) > 0 BEGIN EXEC(@spidstr) SELECT @ConnKilled = COUNT(1) FROM master..sysprocesses WHERE dbid=db_id(@DBName) END IF @withmsg =1 PRINT CONVERT(VARCHAR(10), @ConnKilled) + ' Connection(s) killed for DB ' + @DBName GO
Top 回复人: matq2008(叶子.net) ( ) 信誉:100 2003-3-21 18:00:47 得分:0
SELECT A.NUM, A.NAME, B.UPD_DATE, B.PREV_UPD_DATE FROM TABLE1, (SELECT X.NUM, X.UPD_DATE, Y.UPD_DATE PREV_UPD_DATE FROM (SELECT NUM, UPD_DATE, INBOUND_QTY, STOCK_ONHAND FROM TABLE2 WHERE TO_CHAR(UPD_DATE,'YYYY/MM') = TO_CHAR(SYSDATE, 'YYYY/MM')) X, (SELECT NUM, UPD_DATE, STOCK_ONHAND FROM TABLE2 WHERE TO_CHAR(UPD_DATE,'YYYY/MM') = TO_CHAR(TO_DATE(TO_CHAR(SYSDATE, 'YYYY/MM') ¦¦ '/01','YYYY/MM/DD') - 1, 'YYYY/MM') ) Y, WHERE X.NUM = Y.NUM (+) AND X.INBOUND_QTY + NVL(Y.STOCK_ONHAND,0) <> X.STOCK_ONHAND ) B WHERE A.NUM = B.NUM
Top 回复人: cnuninet(www.helloaspx.com) ( ) 信誉:100 2003-3-21 18:26:32 得分:0
SELECT * FROM table ORDER BY id SELECT * FROM table ORDER BY id DESC 正反排序,厉害吧!
Top 回复人: cnuninet(www.helloaspx.com) ( ) 信誉:100 2003-3-21 18:26:54 得分:0
还有,一次选出表中的所有内容: SELECT * FROM table
Top 回复人: learnner( ) ( ) 信誉:110 2003-3-21 18:42:16 得分:0
mark
Top 回复人: csdntoll(低调惯了) ( ) 信誉:147 2003-3-21 18:42:41 得分:0
楼上的哥们,厉害!^_^
Top 回复人: csdntoll(低调惯了) ( ) 信誉:147 2003-3-21 18:45:07 得分:0
不是说你,是说: cnuninet(www.helloaspx.com) ,呵呵
Top 回复人: miqier1209(米琪儿) ( ) 信誉:100 2003-3-21 22:58:39 得分:0
select * into b from a where 1<>1 这样生成的b表访问的用户没有select的权限? 请问sql DX们这个问题怎么解决?
Top 回复人: whcasp(money is best~) ( ) 信誉:105 2003-3-22 9:25:25 得分:0
厉害
Top 回复人: csdntoll(低调惯了) ( ) 信誉:147 2003-3-22 10:09:48 得分:0
Select left(field,1) as field1 from table_name order by field desc
Top 回复人: ministrybill(生命的烙印) ( ) 信誉:120 2003-3-22 10:23:58 得分:0
唉,好像称不上经典 大家来捧捧场:http://expert.csdn.net/Expert/topic/1545/1545418.xml?temp=.6732141
Top 回复人: legend9(legend) ( ) 信誉:100 2003-3-22 11:13:44 得分:0
select count(clubmember.clubid)as hot,clubmember.clubid,clubinfo.clubid,clubinfo.name from clubmember,clubinfo where clubinfo.clubid=clubmember.clubid group by clubmember.clubid order by hot DESC limit 10
Top 回复人: waterfall_cp(鸟鸟) ( ) 信誉:98 2003-3-22 11:36:37 得分:0
条件删除 DELETE DBO.TEMP WHERE FLD_CHARACTER IN ( SELECT FLD_CHARACTER FROM dbo.TBL_CHARACTER WHERE (FLD_DELETED = 1) AND (FLD_LEVEL <= 18) AND (FLD_UPDATEDATETIME <= GETDATE() - 5) )
Top 回复人: georgechen(小雨点) ( ) 信誉:100 2003-3-22 15:39:49 得分:0
选择前数据库里前10条记录: 1、select top 10 * from table 2、set rowcount 10 select * from table
Top 回复人: dh20156(风之石-ASP.net学习中) ( ) 信誉:5 2003-3-22 16:13:21 得分:0
今天才把合计函数搞定: Set rs=conn.execute("Select min(id) as minID from TABLE") ^_^
Top 回复人: boy21cnthp(娃娃) ( ) 信誉:99 2003-3-22 17:06:00 得分:0
高手
Top 回复人: honghaier(红孩儿) ( ) 信誉:100 2003-3-22 18:23:01 得分:0
select SQL高手 from 本版发贴者 Where 得分>0
Top 回复人: sevenhzheleven(水冰) ( ) 信誉:100 2003-3-24 1:54:26 得分:0
哇,,,,高手,你们是工作的时候学的,还是读书的时候开始学的?请教
Top 回复人: ljupin(无情刀) ( ) 信誉:106 2003-3-24 2:09:57 得分:0
select * form * 会执行吗
Top 回复人: dawooo(大宇) ( ) 信誉:100 2003-3-24 8:15:58 得分:0
大宇阅览
Top 回复人: wfnuser(夏雪) ( ) 信誉:103 2003-3-24 8:52:17 得分:5
select * from 日程安排 where datediff('minute',f开始时间,getdate())>5 日程安排提前五分钟提醒。
Top 回复人: yonghengdizhen(刹那←→永恒) ( ) 信誉:112 2003-3-24 9:52:31 得分:0
to miqier1209(米琪儿) ( ) 信誉:100 2003-03-21 22:58:00 得分:0
select * into b from a where 1<>1 这样生成的b表访问的用户没有select的权限? 请问sql DX们这个问题怎么解决? 指定dbo前缀应该可以解决问题了..
Top 回复人: accp258(男人背后的女人) ( ) 信誉:101 2003-3-24 11:17:19 得分:0
mark!
Top 回复人: suasalito(妈的,什么血这么难喝,喝可乐去) ( ) 信誉:100 2003-3-24 11:19:59 得分:0
同学们回答的都不错,恩,值得表扬
Top 回复人: hotel9545(清风剑客) ( ) 信誉:100 2003-3-24 12:10:38 得分:0
复制一张表 create table aaa as select * from bbb;
Top 回复人: fenlin(千里之行,始于足下......) ( ) 信誉:110 2003-3-24 12:21:33 得分:0
我也来凑热闹,呵呵...... <% '取出随机记录 Randomize RNumber = Int(Rnd*200) + 1 SQL = "SELECT * FROM Customers WHERE ID = " & RNumber set objRec = ObjConn.Execute(SQL) Response.WriteRNumber & " = " & objRec("ID") & " " & objRec("c_email") %>
Top 回复人: linens(存储过程) ( ) 信誉:100 2003-3-24 13:33:10 得分:0
上面的人把视图都搬出来了啊不过几乎都是查询,我来点实用的 insert into pbrule(newrid,subj,bz,zf,orid,rstat,layer,bid) select newrid,subj,bz,zf,orid,rstat,layer,bid from pbrule1 where bruleid=bruleid 将pbrule1 表中符合条件的记录 导入 pbrule表中
Top 回复人: linens(存储过程) ( ) 信誉:100 2003-3-24 13:38:49 得分:0
下面这个更实用,就是两张关联表,删除主表中已经在副表中没有的信息 delete from info where not exists ( select * from infobz where info.infid=infobz.infid ) 这条语句就是删除 INFO表中infid字段在infobz中不存在的记录 此语句用来维护数据库很有用哦。 楼主给点分吧
Top 回复人: qigang_liu(云山云海) ( ) 信誉:100 2003-3-24 15:19:57 得分:5
CREATE OR REPLACE PROCEDURE DUMP_TO_WEB_TCLHD_SP_OBJ AS BEGIN CALC_PIA_PRICE ; DELETE FROM TCLHD_SP_OBJ ; INSERT INTO TCLHD_SP_OBJ (NAME,CODE,ID,PRICE,TYPE,FIELDS) ( SELECT c.DESCRIPTION,C.SEGMENT1,a.INVENTORY_ITEM_ID, nvl(c.ATTRIBUTE14,'0'),0,nvl(c.ATTRIBUTE13,0) from mtl_item_categories a , mtl_categories b , mtl_system_items c where a.CATEGORY_ID = b.CATEGORY_ID and b.SEGMENT1='原材料' and a.INVENTORY_ITEM_ID = c.INVENTORY_ITEM_ID AND A.ORGANIZATION_ID = 21 and c.ORGANIZATION_ID = 21 and c.inventory_item_status_code = 'Active' ); COMMIT ; END ;
Top 回复人: wnhoo(e梦缘) ( ) 信誉:115 2003-3-24 15:38:58 得分:0
数据库IBM DB2 》》》SQL 绝对精华 select dmbh,SJDM,flsm,dmzz from ydm where dmbh=3300 union all select dmbh,SJDM,flsm,dmzz from ydm where dmbh in (select dmzz from ydm where dmbh=3300) union all select dmbh,SJDM,flsm,dmzz from ydm where dmbh in (select dmzz from ydm where dmbh in ( select dmzz from ydm where dmbh=3300)) union all select dmbh,SJDM,flsm,dmzz from ydm where dmbh in (select dmzz from ydm where dmbh in (select dmzz from ydm where dmbh in ( select dmzz from ydm where dmbh=3300))) union all select dmbh,SJDM,flsm,dmzz from ydm where dmbh in (select dmzz from ydm where dmbh in (select dmzz from ydm where dmbh in (select dmzz from ydm where dmbh in ( select dmzz from ydm where dmbh=3300)))) ********************************** select SJDM from ydm where dmbh=3300 union all select SJDM from ydm where dmbh in (select dmzz from ydm where dmbh=3300) union all select SJDM from ydm where dmbh in (select dmzz from ydm where dmbh in ( select dmzz from ydm where dmbh=3300)) union all select SJDM from ydm where dmbh in (select dmzz from ydm where dmbh in (select dmzz from ydm where dmbh in ( select dmzz from ydm where dmbh=3300))) union all select SJDM from ydm where dmbh in (select dmzz from ydm where dmbh in (select dmzz from ydm where dmbh in (select dmzz from ydm where dmbh in ( select dmzz from ydm where dmbh=3300)))) ************************************************** SELECT COUNT(*) as yhs ,SUM(DF) as df FROM DB2.DFTDF WHERE (year(rq)*12+month(rq)) between 24015 and 24015 AND dflb=513 and (ZHH,YYH) IN (SELECT ZHH,YYxH FROM DB2.YDD111 WHERE HYM in ( select SJDM from DB2.ydm where dmbh=3200 union all select SJDM from DB2.ydm where dmbh in ( select dmzz from DB2.ydm where dmbh=3200) union all select SJDM from DB2.ydm where dmbh in ( select dmzz from DB2.ydm where dmbh in ( select dmzz from DB2.ydm where dmbh=3200)) union all select SJDM from DB2.ydm where dmbh in ( Select dmzz from DB2.ydm where dmbh in ( select dmzz from DB2.ydm where dmbh in ( select dmzz from DB2.ydm where dmbh=3200))) union all select SJDM from DB2.ydm where dmbh in ( select dmzz from DB2.ydm where dmbh in ( select dmzz from DB2.ydm where dmbh in ( select dmzz from DB2.ydm where dmbh in ( select dmzz from DB2.ydm where dmbh=3200)))) )) *********************************************** select bcm,bsm,count(bsm) as sl from ( select bcm,case when blx='DXB' and bxh like 'DD%' AND BXH NOT LIKE 'DDS%' then 'JJB' else 'DZB' end as bsm from jldb) as jldb2 group by bcm,bsm select bcm,bsm,count(bsm) as sl from ( select bcm,case when blx='DXB' and bxh like 'DD%' AND BXH NOT LIKE 'DDS%' then 'JJB' else 'DZB' end as bsm from jldb where bzt='OK' and qyrq between '1999-1-1' and '2003-1-3' ) as jldb2 group by bcm,bsm **************** select bcm,bsm,bxh,count(bsm) as sl from ( select bcm,case when blx='DXB' and bxh like 'DD%' AND BXH NOT LIKE 'DDS%' then 'JJB' else 'DZB' end as bsm,bxh from jldb where bzt='OK' and qyrq between '1999-1-1' and '2003-1-3' ) as jldb2 group by bcm,bxh,bsm ********************************************** with ttt as (select bcm,bxh,bsm,count(bsm) as sl from (select bcm,bxh,case when blx='DXB' and bxh like 'DD%' AND BXH NOT LIKE 'DDS%' then '机械表' else 'DZB' end as bsm from jldb where bzt='OK' and qyrq<='2002-2-2' ) as jldb2 group by bcm,bxh,bsm), sss as (select bcm,bxh,bsm,count(bsm) as sl1 from (select bcm,bxh,case when blx='DXB' and bxh like 'DD%' AND BXH NOT LIKE 'DDS%' then '机械表' else 'DZB' end as bsm from jldb where bzt='OK' and qyrq<='2000-1-1' ) as jldb2 group by bcm,bxh,bsm) select ttt.bcm,ttt.bxh,ttt.bsm,value(ttt.sl,0) as sl1,value(sss.sl1,0) as sl2,(value(ttt.sl,0)-value(sss.sl1,0)) as sl3 from ttt full join sss on sss.bxh=ttt.bxh and sss.bcm=ttt.bcm and sss.bsm=ttt.bsm order by ttt.bcm,ttt.bxh,ttt.bsm
Top 回复人: guzh() ( ) 信誉:100 2003-3-24 17:45:41 得分:0
看了
Top 回复人: realljx(至尊十三少) ( ) 信誉:102 2003-3-24 18:52:15 得分:0
好长
Top 回复人: shawshanke(我随风而来,随风而去) ( ) 信誉:100 2003-3-24 19:44:53 得分:0
恭喜此帖突破100大观! -------------------------------------------------- 我随风而来,又随风而去!
Top 回复人: cep50(cep50) ( ) 信誉:100 2003-3-24 20:30:58 得分:0
上当了,该软件没什么用。是在骗取别人的智慧,还要收人家的人民币。
Top 回复人: eafin(e峰.Net)(一叶枫舟) ( ) 信誉:115 2003-3-24 21:00:38 得分:0
收藏啦! 希望谁能把这些整理一下。然后我再收藏,呵呵
Top 回复人: rolandzhang() ( ) 信誉:103 2003-3-24 21:22:06 得分:0
wenhao676能否加些注释?我菜一点。
Top 回复人: coffee_black(黑咖啡) ( ) 信誉:100 2003-3-25 0:07:54 得分:0
是好贴!!
Top 回复人: zhw_yihui(卜卢特) ( ) 信誉:94 2003-3-25 8:34:10 得分:0
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b) 选择在每一组b值相同的数据中对应的a最大的(换成average或别的函数或子查询,你会有意想不到的发现)记录的所有信息. 类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等. 上面的许多同志对子查询存在有偏见与误解,其实在一个好的数据分析程序中,子查询可以简化很多程序逻辑.
Top 回复人: gage(蓝宝石) ( ) 信誉:100 2003-3-25 11:17:11 得分:0
收藏
Top 回复人: fvsl(楚龙) ( ) 信誉:98 2003-3-25 13:09:51 得分:0
我收藏:)
Top 回复人: zhusuhao(不以为然) ( ) 信誉:101 2003-3-25 13:51:32 得分:0
藏
Top 回复人: fule(孤魂野鬼) ( ) 信誉:100 2003-3-25 13:59:59 得分:0
藏
Top 回复人: 98130(Oracle) ( ) 信誉:100 2003-3-25 14:33:53 得分:0
回复人: fenlin(千里之行,始于足下......) ( ) 信誉:100 2003-03-24 12:21:00 得分:0
我也来凑热闹,呵呵...... <% '取出随机记录 Randomize RNumber = Int(Rnd*200) + 1 SQL = "SELECT * FROM Customers WHERE ID = " & RNumber set objRec = ObjConn.Execute(SQL) Response.WriteRNumber & " = " & objRec("ID") & " " & objRec("c_email") %>
这种方法存在bug,有可能取不出数据来, 最好还是还 select top 1 * from tablename order by newid()
Top 回复人: hisi(海山) ( ) 信誉:100 2003-3-25 16:05:23 得分:0
收藏...
Top 回复人: lyexcel(冰上飞人) ( ) 信誉:100 2003-3-25 16:36:58 得分:0
select * from (select top 5 * from (select * from (select top 5 * from GuestBook where 1=1 and Deleted = false order by GuestID desc) order by GuestID asc) order by GuestID asc) order by GuestID desc
Top 回复人: yonghengdizhen(刹那←→永恒) ( ) 信誉:112 2003-3-25 18:37:44 得分:0
select * from (select top 5 * from (select * from (select top 5 * from GuestBook where 1=1 and Deleted = false order by GuestID desc) order by GuestID asc) order by GuestID asc) order by GuestID desc 这可是一个经典的SQL. 大概再加几层嵌套,查询引擎都可以崩溃了..
Top 回复人: csdntoll(低调惯了) ( ) 信誉:147 2003-3-26 9:49:49 得分:0
昨天刚写的: update picture set IsUse='1' where instr(PicPath,'_1')>0
Top 回复人: yonghengdizhen(刹那←→永恒) ( ) 信誉:112 2003-3-26 11:53:51 得分:0
下面的语句不是精华.但是却是用很多用T-SQL进行开发的同志所不了解的.. 如何更新nText,Text,Image字段数据.. DECLARE @ptrval binary(16) SELECT @ptrval = TEXTPTR(LSD_Comment) FROM Legal_Dispute WHERE LD_Record_No=25 --得到指定记录的nText文本指针 UPDATETEXT Legal_Dispute.LSD_Comment @ptrval 0 0 N'Insert Text Content Into Old Content Before' ---将数据插入在老数据之前. 很多同志在更新nText字段的时候使用一个记录集取回ASP然后一次性用"UPDATE set fieldname='" & new content & old content & "'"的方式实现.. 却不知道,sql string一次只能提交的string是有限制的,这样一来,实际的text,ntext永远也不可能存储它所支持的最大长度的数据..也远远不能达到使用nText字段的目的了..
Top 回复人: jtmoon(逍遥小贼) ( ) 信誉:234 2003-3-26 12:50:27 得分:0
呵呵,不错啊,收藏
Top 回复人: csdntoll(低调惯了) ( ) 信誉:147 2003-3-26 17:49:24 得分:0
有好料快点贴,不久要揭贴喽!
Top 回复人: chinahuman(枯) ( ) 信誉:105 2003-3-26 19:36:33 得分:0
高手们来看一看这个问题了http://expert.csdn.net/Expert/topic/1580/1580778.xml?temp=5.489528E-03 在线等了!
Top 回复人: yonghengdizhen(刹那←→永恒) ( ) 信誉:112 2003-3-26 21:12:14 得分:0
to 上面的兄弟 SELECT * INTO [D:\database.mdb].table4 FROM [C:\database.mdb].table1 前提是ASP用户对后者有读权限. 前者有写权限
Top 回复人: guiguai(鬼怪) ( ) 信誉:101 2003-3-26 22:09:06 得分:0
收藏!
Top 回复人: wertou() ( ) 信誉:100 2003-3-27 10:55:30 得分:0
select * from studentinfo where not exists(select * from student where studentinfo.id=student.id) and 系名称='"&strdepartmentname&"' and 专业名称='"&strprofessionname&"' order by 性别,生源地,高考总成绩
Top 回复人: Swanzy(志远) ( ) 信誉:100 2003-3-27 11:57:47 得分:0
请问查询时时有两行相同的记录,如何去掉一行?(其中包含TEXT数据类型) select brepeople,姓名,bbs_content.* from bbs_revert,bbs_content,bbs_userinfo where bauthor=职员id and brepeople='m043' and bbs_content.id=bid 显示“我”参加的主题回复时,如果本主题回复了两次以上,那查询的结果将有两行以上的记录。
Top 回复人: huangang(H.G) ( ) 信誉:100 2003-3-27 12:08:42 得分:0
select * form a like %keywords% 模糊查询
Top 回复人: yonghengdizhen(刹那←→永恒) ( ) 信誉:112 2003-3-27 12:20:43 得分:0
to Swanzy(志远) 包含有text类型数据是无法进行消除重复值处理的.. 因为在sql server中text处理为一个指针.. 读取并比较该字段需要专用的语法 如果要做,建议在存储过程中做或将该字段排除在比较条件外
Top 回复人: 98130(Oracle) ( ) 信誉:100 2003-3-27 12:41:06 得分:0
select * from (select top 5 * from (select * from (select top 5 * from GuestBook where 1=1 and Deleted = false order by GuestID desc) order by GuestID asc) order by GuestID asc) order by GuestID desc 我怎么运行不了?
Top 回复人: yonghengdizhen(刹那←→永恒) ( ) 信誉:112 2003-3-27 14:18:27 得分:0
to 98130(Oracle) 下面的可以.但是好象看起来很没有必要 select top 1 num1 from (select top 5 num1 from (select top 1 num1 from (select top 5 num1 from table1 order by num1 desc) as a order by num1 asc) as b order by num1 asc) as c order by num1 desc
Top 回复人: tigerflyfly(小飞虎) ( ) 信誉:100 2003-3-27 21:20:40 得分:0
取出最先的是select top * from tablename 取出最后的几条是什么?
Top 回复人: clipper_clipper(clipper_clipper) ( ) 信誉:104 2003-3-27 21:46:59 得分:0
select count(*) from tb_tablename 经常用的,取记录数
Top 回复人: csdntoll(低调惯了) ( ) 信誉:147 2003-3-28 9:26:47 得分:0
t tigerflyfly(小飞虎) 还是用select top,只是排序倒过来即可,比如: select top 10 * from tablename order by id desc
Top 回复人: entice(踏雪寻梅) ( ) 信誉:106 2003-3-28 9:27:16 得分:0
to tigerflyfly(小飞虎) 用排序呀。
Top 回复人: xiaojiyi(小己乙) ( ) 信誉:100 2003-3-28 11:15:44 得分:0
分页语句 select top 100 * from 表名 where id not in (select top page_no*100 * from 表名) page_no是程序中的变量
Top 回复人: xiaoshi(js真痛苦!我要被炒了) ( ) 信誉:101 2003-3-28 11:42:47 得分:0
我收藏 中午来看
Top 回复人: alu_ok(冬瓜茶) ( ) 信誉:100 2003-3-28 13:16:31 得分:0
从数据库直接输出XML数据: select text1,text2 from table1 where text1 like '%alu_ok%' for xml auto
Top 回复人: alu_ok(冬瓜茶) ( ) 信誉:100 2003-3-28 13:19:12 得分:0
UPDATE titles SET t.ytd_sales = t.ytd_sales + s.qty FROM titles t, sales s WHERE t.title_id = s.title_id AND s.ord_date = (SELECT MAX(sales.ord_date) FROM sales)
Top 回复人: PeterMCT(天天下雨1991) ( ) 信誉:101 2003-3-28 13:21:02 得分:0
up
Top 回复人: alu_ok(冬瓜茶) ( ) 信誉:100 2003-3-28 13:21:07 得分:0
DELETE authors FROM (SELECT TOP 10 * FROM authors) AS t1 WHERE authors.au_id = t1.au_id
Top 回复人: alu_ok(冬瓜茶) ( ) 信誉:100 2003-3-28 13:22:26 得分:0
初始化表table1 TRUNCATE TABLE table1
Top 回复人: lxxlily(笨鸟先飞) ( ) 信誉:107 2003-3-28 13:31:43 得分:0
cnuninet(www.helloaspx.com) :晕~~
Top 回复人: zhjzh_zjz(虚心学习,望多指教) ( ) 信誉:117 2003-3-28 15:11:57 得分:0
从入库信息表和入库清单表中得期初期末库存: SELECT a.mattype as mattype, a.matname as matname, a.spec as spec,a.indate as indate, a.amount AS lastnum, a.matsum AS lastsum, b.amount AS curnum, b.matsum AS cursum FROM (SELECT a.mattype, a.matname, a.spec, TO_CHAR(a.indate, 'yyyy-mm') AS indate, SUM(b.amount) AS amount, SUM(b.matsum) AS matsum FROM (SELECT a.mattype, a.matname, a.spec, a.indate, SUM(nvl(a.amount, 0) - NVL(b.amount, 0) + NVL(c.amount, 0)) AS amount, SUM(nvl(a.matsum, 0) - NVL(b.matsum, 0) + NVL(c.matsum, 0)) AS matsum FROM (SELECT a.mattype AS mattype, a.matname AS matname, a.spec AS spec, b.indate AS indate, a.amount AS amount, a.matsum AS matsum, b.matsource AS matsource FROM materialin a, matin b WHERE b.serial = a.serial) a, matuse b, matback c WHERE a.mattype = b.mattype (+) AND a.mattype = c.mattype (+) AND a.matname = b.matname (+) AND a.matname = c.matname (+) AND a.spec = b.spec (+) AND a.spec = c.spec (+) AND a.indate = b.outdate (+) AND a.indate = c.backdate (+) GROUP BY a.mattype, a.matname, a.spec, a.indate UNION SELECT b.mattype, b.matname, b.spec, b.outdate, SUM(nvl(a.amount, 0) - NVL(b.amount, 0) + NVL(c.amount, 0)) AS amount, SUM(nvl(a.matsum, 0) - NVL(b.matsum, 0) + NVL(c.matsum, 0)) AS matsum FROM (SELECT a.mattype AS mattype, a.matname AS matname, a.spec AS spec, b.indate AS indate, a.amount AS amount, a.matsum AS matsum, b.matsource AS matsource FROM materialin a, matin b WHERE b.serial = a.serial) a, matuse b, matback c WHERE a.mattype (+) = b.mattype AND b.mattype = c.mattype (+) AND a.matname (+) = b.matname AND b.matname = c.matname (+) AND a.spec (+) = b.spec AND b.spec = c.spec (+) AND a.indate (+) = b.outdate AND b.outdate = c.backdate (+) GROUP BY b.mattype, b.matname, b.spec, b.outdate UNION SELECT c.mattype, c.matname, c.spec, c.backdate, SUM(nvl(a.amount, 0) - NVL(b.amount, 0) + NVL(c.amount, 0)) AS amount, SUM(nvl(a.matsum, 0) - NVL(b.matsum, 0) + NVL(c.matsum, 0)) AS matsum FROM (SELECT a.mattype AS mattype, a.matname AS matname, a.spec AS spec, b.indate AS indate, a.amount AS amount, a.matsum AS matsum, b.matsource AS matsource FROM materialin a, matin b WHERE b.serial = a.serial) a, matuse b, matback c WHERE a.mattype (+) = c.mattype AND b.mattype (+) = c.mattype AND a.matname (+) = c.matname AND c.matname = b.matname (+) AND c.spec = a.spec (+) AND c.spec = b.spec (+) AND c.backdate = b.outdate (+) AND c.backdate = a.indate (+) GROUP BY c.mattype, c.matname, c.spec, c.backdate) a, (SELECT a.mattype, a.matname, a.spec, a.indate, SUM(nvl(a.amount, 0) - NVL(b.amount, 0) + NVL(c.amount, 0)) AS amount, SUM(nvl(a.matsum, 0) - NVL(b.matsum, 0) + NVL(c.matsum, 0)) AS matsum FROM (SELECT a.mattype AS mattype, a.matname AS matname, a.spec AS spec, b.indate AS indate, a.amount AS amount, a.matsum AS matsum, b.matsource AS matsource FROM materialin a, matin b WHERE b.serial = a.serial) a, matuse b, matback c WHERE a.mattype = b.mattype (+) AND a.mattype = c.mattype (+) AND a.matname = b.matname (+) AND a.matname = c.matname (+) AND a.spec = b.spec (+) AND a.spec = c.spec (+) AND a.indate = b.outdate (+) AND a.indate = c.backdate (+) GROUP BY a.mattype, a.matname, a.spec, a.indate UNION SELECT b.mattype, b.matname, b.spec, b.outdate AS indate, SUM(nvl(a.amount, 0) - NVL(b.amount, 0) + NVL(c.amount, 0)) AS amount, SUM(nvl(a.matsum, 0) - NVL(b.matsum, 0) + NVL(c.matsum, 0)) AS matsum FROM (SELECT a.mattype AS mattype, a.matname AS matname, a.spec AS spec, b.indate AS indate, a.amount AS amount, a.matsum AS matsum, b.matsource AS matsource FROM materialin a, matin b WHERE b.serial = a.serial) a, matuse b, matback c WHERE a.mattype (+) = b.mattype AND b.mattype = c.mattype (+) AND a.matname (+) = b.matname AND b.matname = c.matname (+) AND a.spec (+) = b.spec AND b.spec = c.spec (+) AND a.indate (+) = b.outdate AND b.outdate = c.backdate (+) GROUP BY b.mattype, b.matname, b.spec, b.outdate UNION SELECT c.mattype, c.matname, c.spec, c.backdate AS indate, SUM(nvl(a.amount, 0) - NVL(b.amount, 0) + NVL(c.amount, 0)) AS amount, SUM(nvl(a.matsum, 0) - NVL(b.matsum, 0) + NVL(c.matsum, 0)) AS matsum FROM (SELECT a.mattype AS mattype, a.matname AS matname, a.spec AS spec, b.indate AS indate, a.amount AS amount, a.matsum AS matsum, b.matsource AS matsource FROM materialin a, matin b WHERE b.serial = a.serial) a, matuse b, matback c WHERE a.mattype (+) = c.mattype AND b.mattype (+) = c.mattype AND a.matname (+) = c.matname AND c.matname = b.matname (+) AND c.spec = a.spec (+) AND c.spec = b.spec (+) AND c.backdate = b.outdate (+) AND c.backdate = a.indate (+) GROUP BY c.mattype, c.matname, c.spec, c.backdate) b WHERE TO_CHAR(b.indate, 'yyyy-mm') <= TO_CHAR(a.indate, 'yyyy-mm') AND a.mattype = b.mattype (+) AND a.matname = b.matname (+) AND a.spec = b.spec (+) GROUP BY a.mattype, a.matname, a.spec, TO_CHAR(a.indate, 'yyyy-mm')) a, ---未完,待续
Top 回复人: zhjzh_zjz(虚心学习,望多指教) ( ) 信誉:117 2003-3-28 15:13:01 得分:0
---接上面 (SELECT a.mattype, a.matname, a.spec, TO_CHAR(a.indate, 'yyyy-mm') AS indate, SUM(b.amount) AS amount, SUM(b.matsum) AS matsum FROM (SELECT a.mattype, a.matname, a.spec, a.indate, SUM(nvl(a.amount, 0) - NVL(b.amount, 0) + NVL(c.amount, 0)) AS amount, SUM(nvl(a.matsum, 0) - NVL(b.matsum, 0) + NVL(c.matsum, 0)) AS matsum FROM (SELECT a.mattype AS mattype, a.matname AS matname, a.spec AS spec, b.indate AS indate, a.amount AS amount, a.matsum AS matsum, b.matsource AS matsource FROM materialin a, matin b WHERE b.serial = a.serial) a, matuse b, matback c WHERE a.mattype = b.mattype (+) AND a.mattype = c.mattype (+) AND a.matname = b.matname (+) AND a.matname = c.matname (+) AND a.spec = b.spec (+) AND a.spec = c.spec (+) AND a.indate = b.outdate (+) AND a.indate = c.backdate (+) GROUP BY a.mattype, a.matname, a.spec, a.indate UNION SELECT b.mattype, b.matname, b.spec, b.outdate, SUM(nvl(a.amount, 0) - NVL(b.amount, 0) + NVL(c.amount, 0)) AS amount, SUM(nvl(a.matsum, 0) - NVL(b.matsum, 0) + NVL(c.matsum, 0)) AS matsum FROM (SELECT a.mattype AS mattype, a.matname AS matname, a.spec AS spec, b.indate AS indate, a.amount AS amount, a.matsum AS matsum, b.matsource AS matsource FROM materialin a, matin b WHERE b.serial = a.serial) a, matuse b, matback c WHERE a.mattype (+) = b.mattype AND b.mattype = c.mattype (+) AND a.matname (+) = b.matname AND b.matname = c.matname (+) AND a.spec (+) = b.spec AND b.spec = c.spec (+) AND a.indate (+) = b.outdate AND b.outdate = c.backdate (+) GROUP BY b.mattype, b.matname, b.spec, b.outdate UNION SELECT c.mattype, c.matname, c.spec, c.backdate, SUM(nvl(a.amount, 0) - NVL(b.amount, 0) + NVL(c.amount, 0)) AS amount, SUM(nvl(a.matsum, 0) - NVL(b.matsum, 0) + NVL(c.matsum, 0)) AS matsum FROM (SELECT a.mattype AS mattype, a.matname AS matname, a.spec AS spec, b.indate AS indate, a.amount AS amount, a.matsum AS matsum, b.matsource AS matsource FROM materialin a, matin b WHERE b.serial = a.serial) a, matuse b, matback c WHERE a.mattype (+) = c.mattype AND b.mattype (+) = c.mattype AND a.matname (+) = c.matname AND c.matname = b.matname (+) AND c.spec = a.spec (+) AND c.spec = b.spec (+) AND c.backdate = b.outdate (+) AND c.backdate = a.indate (+) GROUP BY c.mattype, c.matname, c.spec, c.backdate) a, (SELECT a.mattype, a.matname, a.spec, a.indate, SUM(nvl(a.amount, 0) - NVL(b.amount, 0) + NVL(c.amount, 0)) AS amount, SUM(nvl(a.matsum, 0) - NVL(b.matsum, 0) + NVL(c.matsum, 0)) AS matsum FROM (SELECT a.mattype AS mattype, a.matname AS matname, a.spec AS spec, b.indate AS indate, a.amount AS amount, a.matsum AS matsum, b.matsource AS matsource FROM materialin a, matin b WHERE b.serial = a.serial) a, matuse b, matback c WHERE a.mattype = b.mattype (+) AND a.mattype = c.mattype (+) AND a.matname = b.matname (+) AND a.matname = c.matname (+) AND a.spec = b.spec (+) AND a.spec = c.spec (+) AND a.indate = b.outdate (+) AND a.indate = c.backdate (+) GROUP BY a.mattype, a.matname, a.spec, a.indate UNION SELECT b.mattype, b.matname, b.spec, b.outdate AS indate, SUM(nvl(a.amount, 0) - NVL(b.amount, 0) + NVL(c.amount, 0)) AS amount, SUM(nvl(a.matsum, 0) - NVL(b.matsum, 0) + NVL(c.matsum, 0)) AS matsum FROM (SELECT a.mattype AS mattype, a.matname AS matname, a.spec AS spec, b.indate AS indate, a.amount AS amount, a.matsum AS matsum, b.matsource AS matsource FROM materialin a, matin b WHERE b.serial = a.serial) a, matuse b, matback c WHERE a.mattype (+) = b.mattype AND b.mattype = c.mattype (+) AND a.matname (+) = b.matname AND b.matname = c.matname (+) AND a.spec (+) = b.spec AND b.spec = c.spec (+) AND a.indate (+) = b.outdate AND b.outdate = c.backdate (+) GROUP BY b.mattype, b.matname, b.spec, b.outdate UNION SELECT c.mattype, c.matname, c.spec, c.backdate AS indate, SUM(nvl(a.amount, 0) - NVL(b.amount, 0) + NVL(c.amount, 0)) AS amount, SUM(nvl(a.matsum, 0) - NVL(b.matsum, 0) + NVL(c.matsum, 0)) AS matsum FROM (SELECT a.mattype AS mattype, a.matname AS matname, a.spec AS spec, b.indate AS indate, a.amount AS amount, a.matsum AS matsum, b.matsource AS matsource FROM materialin a, matin b WHERE b.serial = a.serial) a, matuse b, matback c WHERE a.mattype (+) = c.mattype AND b.mattype (+) = c.mattype AND a.matname (+) = c.matname AND c.matname = b.matname (+) AND c.spec = a.spec (+) AND c.spec = b.spec (+) AND c.backdate = b.outdate (+) AND c.backdate = a.indate (+) GROUP BY c.mattype, c.matname, c.spec, c.backdate) b WHERE TO_CHAR(b.indate, 'yyyy-mm') <= TO_CHAR(a.indate, 'yyyy-mm') AND a.mattype = b.mattype (+) AND a.matname = b.matname (+) AND a.spec = b.spec (+) GROUP BY a.mattype, a.matname, a.spec, TO_CHAR(a.indate, 'yyyy-mm')) b WHERE a.mattype = b.mattype (+) AND a.matname = b.matname (+) AND a.spec = b.spec (+) AND TO_DATE(a.indate, 'yyyy-mm') = ADD_MONTHS(TO_DATE(b.indate, 'yyyy-mm'), 1) --语句完毕
Top 回复人: zhjzh_zjz(虚心学习,望多指教) ( ) 信誉:117 2003-3-28 15:15:27 得分:0
上面用的是Oracle数据库。由于涉及到双向外连接,所以很长
Top 回复人: zhongjz(小海螺) ( ) 信誉:105 2003-3-28 15:46:39 得分:5
从数据库中去一年的各单位电话费统计(电话费定额贺电化肥清单两个表来源) SELECT a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, 'yyyy') AS telyear, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '01', a.factration)) AS JAN, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '02', a.factration)) AS FRI, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '03', a.factration)) AS MAR, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '04', a.factration)) AS APR, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '05', a.factration)) AS MAY, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '06', a.factration)) AS JUE, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '07', a.factration)) AS JUL, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '08', a.factration)) AS AGU, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '09', a.factration)) AS SEP, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '10', a.factration)) AS OCT, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '11', a.factration)) AS NOV, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '12', a.factration)) AS DEC FROM (SELECT a.userper, a.tel, a.standfee, b.telfeedate, b.factration FROM TELFEESTAND a, TELFEE b WHERE a.tel = b.telfax) a GROUP BY a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, 'yyyy')
Top 回复人: zzlcn() ( ) 信誉:86 2003-3-28 17:24:15 得分:0
请问如果有 10 万 条数据 该怎么查询,我一查询就出错 如果是3万条还可以接受,但是到了3万条以上就不稳定! 我用了 sqlserver 也没有用 10 万条数据来一次分页显示居然要 10-15秒的超长时间!!!!! 如果在分页显示中查询,立即告吹!!! asp 和 asp.net 我都试用过(+ ms sqlserver) 均是如此 是不是 asp 和 asp.net 都是垃圾中的垃圾!!!
Top 回复人: yonghengdizhen(刹那←→永恒) ( ) 信誉:112 2003-3-28 17:40:16 得分:0
大量复杂的数据分析,在结构设计上就应该要考虑进去.适当设置一些字段或表存储分段统计信息. 否则再好的数据库都撑不住的..
Top 回复人: sishuo(思铄) ( ) 信誉:100 2003-3-29 9:39:01 得分:0
好,收了。
Top 回复人: huijunzi(Cyril) ( ) 信誉:97 2003-3-30 16:49:18 得分:5
有意思,我也来一个,解决跳号的问题: select min(bh)+1 from Table1 where bh+1 not in(select bh from Table1)
Top 回复人: yonghengdizhen(刹那←→永恒) ( ) 信誉:112 2003-3-31 9:50:13 得分:0
进行复杂数据分析还有一种趋势那就是使用数据仓库(Data Houseware)和OLAP.
Top 回复人: bkss(白开水水) ( ) 信誉:100 2003-4-1 11:09:20 得分:0
哈哈,我也来凑热闹。。。 DECLARE @QuitMedNo char(13) DECLARE @PreRecipeNo char(13),@PreRecipeXNo char(3) Declare @zyxh char(12),@kdks char(8) DECLARE @DepotName char(10) BEGIN TRAN if not exists(select 摆药单号 from 摆药单 where 状态='00' and 摆药单号=@PutMedNo) begin return 0 end EXECUTE Sp_GetBillNo @QuitMedNo output,'D' INSERT INTO [退药]([退药序号], [操作员], [退药时间], [退药说明]) VALUES(@QuitMedNo,@Operator,cast(getdate() as smalldatetime),@Intro) if (@@error<>0 or @@rowcount=0) begin rollback transaction raiserror('插入退药表失败',16,-1) return 1 end DECLARE PutMed_Cursor CURSOR FOR SELECT DISTINCT A.处方号,A.处方序号,B.住院序号,B.科室,C.名称 as 摆药区 FROM 摆药单明细 A inner join 处方 B ON A.处方号 =B.处方号 AND A.处方序号=B.处方序号 INNER JOIN 摆药区 C ON B.摆药区=C.代码 WHERE A.摆药单号=@PutMedNo OPEN PutMed_Cursor FETCH NEXT FROM PutMed_Cursor INTO @PreRecipeNo, @PreRecipeXNo,@zyxh,@kdks,@DepotName WHILE @@FETCH_STATUS = 0 BEGIN EXEC usp_QuitBillDetail @QuitMedNo ,@PutMedNo,@PreRecipeNo,@PreRecipeXNo,@zyxh,@kdks,@fsks,@DepotName --另一个存储过程 if (@@error<>0) begin rollback transaction CLOSE PutMed_Cursor --DEALLOCATE PutMed_Cursor raiserror('更新退单处方明细失败',16,-1) return 1 end FETCH NEXT FROM PutMed_Cursor INTO @PreRecipeNo, @PreRecipeXNo,@zyxh,@kdks END
Top 回复人: bkss(白开水水) ( ) 信誉:100 2003-4-1 11:21:36 得分:10
个人认为,偶写了这么久的SQL,只说语法很无聊,语句的涵义与灵活的组合很重要,下面这个是写的一个药品管理的存储过程的一句,很经典: UPDATE 药房库存 SET 库存数量=库存数量-B.用量 FROM [药房库存] A , ( Select sum(用量) as 用量,药品价码 FROM 处方项 WHERE 处方号=@RecipeNo AND 处方序号=@RecipeXNo GROUP BY 药品价码 ) B WHERE A.药品价码=B.药品价码 AND A.库房名称=@DepotName 这是一个参照B表将A表中库存一一修改,而B表中存在一个求和,A表也与B表关联,同时要满足A表条件。
Top 回复人: yonghengdizhen(刹那←→永恒) ( ) 信誉:112 2003-4-1 11:26:50 得分:0
是的..SQL中应用的经典应该只考虑SQL查询的应用.. 存储过程已经使用控制语法操作了,和别的程序设计语言设计算法没什么区别..
Top 回复人: yexiao(叶开) ( ) 信誉:100 2003-4-1 14:11:35 得分:0
gz
Top 回复人: rong451(rong451) ( ) 信誉:100 2003-4-1 15:43:10 得分:0
very good 我一定收藏!!!
Top 回复人: gengwei80(gengwei) ( ) 信誉:99 2003-4-2 9:40:27 得分:0
create or replace procedure p_table ( p_g3e_fno g3e_features_optable.g3e_fno%type ) as v_xlmc VARCHAR2(12); v_XLDM VARCHAR2(5); cursor c_table IS select g3e_table from g3e_component where g3e_cno=(select g3e_primaryattributecno from g3e_features_optable where g3e_fno=p_g3e_fno); type t_sor is ref cursor; v_sor t_sor; str varchar2(50); begin for v_table in c_table loop str:='select xlmc,xldm from '||v_table.g3e_table; dbms_output.put_line(v_table.g3e_table); open v_sor for str; loop fetch v_sor into v_xlmc,v_xldm; dbms_output.put_line('hello g3e_table'); dbms_output.put_line(v_xlmc||' '||v_xldm); exit when v_sor%notfound; end loop; close v_sor; end loop; end p_table;
|