2007100813460457720.txt

java书店管理系统 java书店管理系统 java书店管理系统

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