数据库sql技巧

1.sql语句当注意的

当我们写像下面这样的sql语句的时候看看当注意些什么

在 (select text from datadictionary where  g.category=keyName and recycleFlag=0 and g.recycleFlag=0) cateKeyName和
(select text from datadictionary where  g.brand=keyName and recycleFlag=0 and g.recycleFlag=0) brandKeyName中，
我们注意到了，只在此两个复杂查询中用到了datadictionary，那么我们在from中也一定要声明上datadictonary d这样的标记，
还有在where中一定要建立一下我们在两个复杂查询中所用到的两个表的关系（d.keyName=g.category），
否则多会出现Subquery return more than 1 rows这样的sql异常

select g.category,g.brand,g.sizeNum,e.quantity,e.cost,e.totalCost,e.comment,e.enterRepertoryID,
    (select text from datadictionary where  g.category=keyName and recycleFlag=0 and g.recycleFlag=0) cateKeyName,
    (select text from datadictionary where  g.brand=keyName and recycleFlag=0 and g.recycleFlag=0) brandKeyName
        from  enterrepertory e, goods g ,datadictionary d
        where g.goodsID = e.goodsID and g.recycleFlag=0 and e.recycleFlag=0 and d.keyName=g.category
        and e.enterRepertoryID=<%=request.getParameter("enterRepertoryID") %>