是那个家伙对数据下了手脚^_^
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(ｅ梦缘) ( ) 信誉：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(ｅ峰.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;
此内容由惯性聚合(RSS阅读器)自动聚合整理，仅供阅读参考。 原文来自 — 版权归原作者所有。