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