sqlcat

㈠ sql翻譯器怎麼實現

標准sql+一個翻頁支持就可以了。

提供一個mssql的
package com.cat.xtools.data;

import java.io.PrintStream;
import org.hibernate.dialect.SQLServerDialect;

public class MsSqlDialect extends SQLServerDialect
{

public MsSqlDialect()
{
}

public String getLimitString(String sql, int offset, int limit)
{
if(offset == 0)
{
return super.getLimitString(sql, 0, limit);
}
String sqlTemp = super.getLimitString(sql, 0, limit);
String orderby = getOrderby(sql);
if(orderby.equals(""))
{
sqlTemp = super.getLimitString((new StringBuilder("select * from (")).append(sqlTemp).append(") cat_sql_temp_000").toString(), 0, (limit - offset) + 1);
} else
{
sqlTemp = super.getLimitString((new StringBuilder("select * from (")).append(sqlTemp).append(") cat_sql_temp_000 order by ").append(orderby).toString(), 0, (limit - offset) + 1);
}
return sqlTemp;
}

private static String getOrderby(String sql)
{
String orderby = "";
int lastk = sql.lastIndexOf(")");
int lasto = sql.toLowerCase().lastIndexOf("order by");
if(lastk > lasto && lastk > -1 || lastk == lasto)
{
return "";
}
orderby = sql.substring(lasto + 8, sql.length());
String os[] = orderby.split(",");
String nob = "";
for(int i = 0; i < os.length; i++)
{
int dian = os[i].indexOf(".");
if(dian > -1)
{
os[i] = os[i].substring(dian + 1, os[i].length());
}
String colums[] = os[i].trim().split(" ");
String cm = colums[0];
int as = 0;
as = sql.indexOf((new StringBuilder(" ")).append(colums[0]).append(" as ").toString());
if(as < 0)
{
as = sql.indexOf((new StringBuilder(",")).append(colums[0]).append(" as ").toString());
}
if(as < 0)
{
as = sql.indexOf((new StringBuilder(".")).append(colums[0]).append(" as ").toString());
}
if(as > -1)
{
as += (new StringBuilder(".")).append(colums[0]).append(" as ").toString().length();
int end = sql.indexOf(" ", as);
if(end < 0 || end > sql.indexOf(",", as) && sql.indexOf(",", as) > -1)
{
end = sql.indexOf(",", as);
}
if(end > -1)
{
cm = sql.substring(as, end);
}
}
if(colums.length < 2)
{
nob = (new StringBuilder(String.valueOf(nob))).append(",").append(cm).append(" desc").toString();
} else
if(colums[1].toLowerCase().equals("asc"))
{
nob = (new StringBuilder(String.valueOf(nob))).append(",").append(cm).append(" desc").toString();
} else
{
nob = (new StringBuilder(String.valueOf(nob))).append(",").append(cm).append(" asc").toString();
}
}

if(nob.length() > 0)
{
nob = nob.substring(1, nob.length());
}
return nob;
}

public static void main(String args[])
{
MsSqlDialect d = new MsSqlDialect();
System.out.println(d.getLimitString("SELECT * FROM HtmlLabelIndex", 1, 1));
}
}

㈡ SQL編寫規范

書寫格式 示例代碼 存儲過程SQL文書寫格式例selectc dealerCode round(sum(c submitSubletAmountDLR + c submitPartsAmountDLR + c submitLaborAmountDLR) / count(*) ) as avg decode(null x xx CNY )from (selecta dealerCode a submitSubletAmountDLR a submitPartsAmountDLR a submitLaborAmountDLRfrom SRV_C_F awhere (to_char(a ORIGSUBMITTIME yyyy/mm/dd ) >= Date Range(start) and to_char(a ORIGSUBMITTIME yyyy/mm/dd ) <= Date Range(end) and nvl(a deleteflag ) <> )union allselectb dealerCode b submitSubletAmountDLR b submitPartsAmountDLR b submitLaborAmountDLRfrom SRV_CHistory_F bwhere (to_char(b ORIGSUBMITTIME yyyy/mm/dd ) >= Date Range(start) and to_char(b ORIGSUBMITTIME yyyy/mm/dd ) <= Date Range(end) and nvl(b deleteflag ) <> )) cgroup by c dealerCodeorder by avg desc;Java source里的SQL字元串書寫格式例strSQL = insert into Snd_FinanceHistory_Tb + (DEALERCODE + REQUESTSEQUECE + HANDLETIME + JOBFLAG + FRAMENO + INMONEY + REMAINMONEY + DELETEFLAG + UPDATECOUNT + CREUSER + CREDATE + HONORCHECKNO + SEQ) + values ( + draftInputDetail dealerCode + + + draftInputDetail requestsequece + + sysdate + + + frameNO + + requestMoney + + remainMoney + + + + + draftStruct employeeCode + + sysdate + + draftInputDetail honorCheckNo + + index + ) ; ) 縮進對於存儲過程文件 縮進為 個空格對於Java source里的SQL字元串 不可有縮進 即每一行字元串不可以空格開頭 ) 換行 > Select/From/Where/Order by/Group by等子句必須另其一行寫 > Select子句內容如果只有一項 與Select同行寫 > Select子句內容如果多於一項 每一項單獨佔一行 在對應Select的基礎上向右縮進 個空格（Java source無縮進） > From子句內容如果只有一項 與From同行寫 > From子句內容如果多於一項 每一項單獨佔一行 在對應From的基礎上向右縮進 個空格（Java source無縮進） > Where子句的條件如果有多項 每一個條件佔一行 以AND開頭 且無縮進 > (Update)Set子句內容每一項單獨佔一行 無縮進 > Insert子句內容每個表欄位單獨佔一行 無縮進 values每一項單獨佔一行 無縮進 > SQL文中間不允許出現空行 > Java source里單引號必須跟所屬的SQL子句處在同一行 連接符（ + ）必須在行首 ) 空格 > SQL內算數運算符 邏輯運算符連接的兩個元素之間必須用空格分隔 > 逗號之後必須接一個空格 > 關鍵字 保留字和左括弧之間必須有一個空格 不等於統一使用 <> Oracle認為 != 和 <> 是等價的 都代表不等於的意義 為了統一 不等於一律使用 <> 表示 使用表的別名 資料庫查詢 必須使用表的別名 SQL文對表欄位擴展的兼容性 在Java source里使用Select *時 嚴禁通過getString( )的形式得到查詢結果 必須使用getString( 欄位名 )的形式使用Insert時 必須指定插入的欄位名 嚴禁不指定欄位名直接插入values 減少子查詢的使用 子查詢除了可讀性差之外 還在一定程度上影響了SQL運行效率請盡量減少使用子查詢的使用 用其他效率更高 可讀性更好的方式替代 適當添加索引以提高查詢效率 適當添加索引可以大幅度的提高檢索速度請參看ORACLE SQL性能優化系列 對資料庫表操作的特殊要求 本項目對資料庫表的操作還有以下特殊要求 ) 以邏輯刪除替代物理刪除注意 現在資料庫表中數據沒有物理刪除 只有邏輯刪除以deleteflag欄位作為刪除標志 deleteflag= 代表此記錄被邏輯刪除 因此在查詢數據時必須考慮deleteflag的因素deleteflag的標准查詢條件 NVL(deleteflag ) <> ) 增加記錄狀態欄位資料庫中的每張表基本都有以下欄位 DELETEFLAG UPDATECOUNT CREDATE CREUSER UPDATETIME UPDATEUSER要注意在對標進行操作時必須考慮以下欄位插入一條記錄時要置DELETEFLAG= UPDATECOUNT= CREDATE=sysdate CREUSER=登錄User查詢一條記錄時要考慮DELETEFLAG 如果有可能對此記錄作更新時還要取得UPDATECOUNT作同步檢查修改一條記錄時要置UPDATETIME=sysdate UPDATEUSER=登錄User UPDATECOUNT=(UPDATECOUNT+ ) mod 刪除一條記錄時要置DELETEFLAG= ) 歷史表資料庫里部分表還存在相應的歷史表 比如srv_c_f和srv_chistory_f在查詢數據時除了檢索所在表之外 還必須檢索相應的歷史表 對二者的結果做Union（或Union All） 用執行計劃分析SQL性能 EXPLAIN PLAN是一個很好的分析SQL語句的工具 它可以在不執行SQL的情況下分析語句通過分析 我們就可以知道ORACLE是怎樣連接表 使用什麼方式掃描表（索引掃描或全表掃描） 以及使用到的索引名稱按照從里到外 從上到下的次序解讀分析的結果EXPLAIN PLAN的分析結果是用縮進的格式排列的 最內部的操作將最先被解讀 如果兩個操作處於同一層中 帶有最小操作號的將首先被執行目前許多第三方的工具如PLSQL Developer和TOAD等都提供了極其方便的EXPLAIN PLAN工具PG需要將自己添加的查詢SQL文記入log 然後在EXPLAIN PLAN中進行分析 盡量減少全表掃描 ORACLE SQL性能優化系列 選擇最有效率的表名順序(只在基於規則的優化器中有效) ORACLE的解析器按照從右到左的順序處理FROM子句中的表名 因此FROM子句中寫在最後的表（基礎表driving table）將被最先處理在FROM子句中包含多個表的情況下 必須選擇記錄條數最少的表作為基礎表當ORACLE處理多個表時 會運用排序及合並的方式連接它們首先 掃描第一個表（FROM子句中最後的那個表)並對記錄進行排序 然後掃描第二個表（FROM子句中最後第二個表) 最後將所有從第二個表中檢索出的記錄與第一個表中合適記錄進行合並例如:表 TAB 條記錄表 TAB 條記錄選擇TAB 作為基礎表 (最好的方法)select count(*) from tab tab 執行時間 秒選擇TAB 作為基礎表 (不佳的方法)select count(*) from tab tab 執行時間 秒如果有 個以上的表連接查詢 那就需要選擇交叉表（intersection table）作為基礎表 交叉表是指那個被其他表所引用的表例如:EMP表描述了LOCATION表和CATEGORY表的交集SELECT *FROM LOCATION L CATEGORY C EMP EWHERE E EMP_NO BEEEN AND AND E CAT_NO = C CAT_NOAND E LOCN = L LOCN將比下列SQL更有效率SELECT *FROM EMP E LOCATION L CATEGORY CWHERE E CAT_NO = C CAT_NOAND E LOCN = L LOCNAND E EMP_NO BEEEN AND WHERE子句中的連接順序 ORACLE採用自下而上的順序解析WHERE子句根據這個原理 表之間的連接必須寫在其他WHERE條件之前 那些可以過濾掉最大數量記錄的條件必須寫在WHERE子句的末尾例如 (低效 執行時間 秒)SELECT *FROM EMP EWHERE SAL > AND JOB = MANAGER AND < (SELECT COUNT(*) FROM EMP WHERE MGR=E EMPNO);(高效 執行時間 秒)SELECT *FROM EMP EWHERE < (SELECT COUNT(*) FROM EMP WHERE MGR=E EMPNO)AND SAL > AND JOB = MANAGER ; SELECT子句中避免使用 * 當你想在SELECT子句中列出所有的COLUMN時 使用動態SQL列引用 * 是一個方便的方法 不幸的是 這是一個非常低效的方法實際上 ORACLE在解析的過程中 會將 * 依次轉換成所有的列名這個工作是通過查詢數據字典完 lishixin/Article/program/Oracle/201311/18246

㈢ sql如何取交集

select * from mytable where pet in (select pet from mytable group by pet having count(pet)>1)

獲取同一pet數量大於1的pet並select出相關信息

select pet,count(pet) as per_count from mytable group by pet order by count(pet) desc limit 10

獲取pet擁有量前十的pet和數量

㈣ 淺談hql和sql的區別，描述特別需要注意的地方

1.hql與sql的區別
sql 面向資料庫表查詢
hql 面向對象查詢
hql ： from 後面跟的 類名＋類對象 where 後 用 對象的屬性做條件
sql： from 後面跟的是表名 where 後 用表中欄位做條件
查詢
在Hibernate中使用查詢時，一般使用Hql查詢語句。
HQL（Hibernate Query Language），即Hibernate的查詢語言跟SQL非常相像。不過HQL與SQL的最根本的區別，就是它是面向對象的。
使用HQL時需要注意以下幾點：
l 大小寫敏感
因為HQL是面向對象的，而對象類的名稱和屬性都是大小寫敏感的，所以HQL是大小寫敏感的。
Eg.
HQL語句：from Cat as cat where cat.id > 1;與from Cat as cat where cat.ID > 1;是不一樣的，這點與SQL不同。
l from子句
Eg. from Cat，該句返回Cat對象實例，開發人員也可以給其加上別名，eg. from Cat as cat，對於多表查詢的情況，可參考如下：
from Cat as cat, Dog as dog
其它方面都與SQL類似，在此不再贅述。
接下來講一個在Hibernate中查詢的例子。
1.1簡單查詢
List list = session.createQuery("from User as user order by user.loginName").list();
1.2帶單個參數的查詢
List list = session.find("from User as user where user.loginName=?",
loginName,
Hibernate.STRING);
1.3多個參數的查詢
Eg1. 此例採用「?」佔位符的方式
String hql = "from User as user where user.loginName=? and user.orgId=? ";
Query query = session.createQuery(hql);
query.setParameter(1, 'amigo');
query.setParameter(2, new Long(1)) ;
List list = query .list();
Eg2. 此例採用「:paramName」的方式
String hql = "from User as user where user.loginName=:loginName and user.orgId=:orgId ";
Query query = session.createQuery(hql);
query.setParameter('loginName', 'amigo');
query.setParameter('orgId', new Long(1)) ;
List list = query .list();
1.4查詢數量
int count = (Integer) session.createQuery("select count(*) from User").uniqueResult().intValue();
1.5限制查詢起始值和數量的查詢
這種一般是在記錄需要分頁的時候需要用到，例如，在如下的代碼中，限制查詢的開始記錄的位置為50，最大查詢條數為50。
String hql = "from User as user order by user.loginName";
int firstResult= 50;
int maxResults = 50;
Query query = session.createQuery(hql);
query = query.setFirstResult(firstResult);
query.setMaxResults(maxResults);
1.6子查詢
在某些情況下，也需要用到子查詢，例如在下面的例子中，User為用戶對象，UserRole為用戶與角色關聯對象。如下HQL語句將沒有分配角色的用戶對象查找出來。
String hql = "from User user where user.loginName"
+ " not in(select ur.user.loginName from UserRole ur) ";
List list = (session.createQuery(hql)).list();
1.7原生SQL查詢
對於某些復雜的查詢語句，需要調用某種特定的資料庫的特定函數才能解決，Hibernate雖然不推薦使用原生SQL語句來查詢，因為這將破壞資料庫的易移植性，但是Hibernate中也提供了使用原生SQL進行查詢的方法，只需要獲得連接即可。
Eg. 在下面的例子中，用到了Sql Server資料庫中的原生sql語句，如下所示：
String timeUnit = "13";
String sql = "select count(*) count, CONVERT(VARCHAR(" + timeUnit +"), log.gen_datetime,121) timeUnit " + "from Log log";
SQLQuery query = session.createSQLQuery(sql)
.addScalar("count", Hibernate.INTEGER)
.addScalar("timeUnit", Hibernate.STRING);
List list = query.list();
2 新增
在資料庫中新增記錄在Hibernate中不需要使用insert命令，只需要構造新增的對象後，調用Session對象的save(…)方法即可。
2.1新增單個對象
新增單個對象的實例如下，該實例將在用戶表中新增一條記錄。
Session session = HibernateSessionFactory.getSession();
Transaction ts = null;
try {
ts = session.beginTransaction();
User user = new User();
user.setLoginName("amigo");
user.setFullName("阿蜜果");
……
session.save(user) ;
ts.commit();
} catch (Exception e) {
if (ts != null) {
ts.rollback();
}
} finally {
HibernateSessionFactory.closeSession();
}
2.2批量新增對象
對於批量新增對象的情況，需要在新增一部分對象後flush和clear一次，例如，沒批量新增20個對象時手動的flush一次，假設在list為一個用戶列表，裡麵包含很多User對象，那麼要將實現這些對象的批量新增，可採用如下方法：
Session session = HibernateSessionFactory.getSession();
Transaction ts = null;
try {
ts = session.beginTransaction();
for (int i = 0; i < list.size(); i++) {
User user = (User) list.get(i);
session.save(user) ;
if (i % 20 == 0) {
session.flush();
session.clear();
}
}
ts.commit();
} catch (Exception e) {
if (ts != null) {
ts.rollback();
}
} finally {
HibernateSessionFactory.closeSession();
}
3 更新
在hibernate中，更新對象前不需要使用查詢語句：update…，一般需要在取得需要更新的持久化對象後，執行Session對象的update(…)方法。例如：
Session session = HibernateSessionFactory.getSession();
Transaction ts = null;
try {
ts = session.beginTransaction();
//取得持久化對象
User user = session.get(User.class, "amigo");
//對需要修改的屬性進行修改
user.setFullName("阿蜜果");
……
session.update(user) ;
ts.commit();
} catch (Exception e) {
if (ts != null) {
ts.rollback();
}
} finally {
HibernateSessionFactory.closeSession();
}
4 刪除
4.1刪除單個對象
一般在取得某對象後，開發人員可以調用Session對象的delete(…)方法刪除該對象。
Eg. 下面的實例中取得loginName（主鍵）為「amigo」的User對象後，將它刪除。
Session session = HibernateSessionFactory.getSession();
Transaction ts = null;
try {
ts = session.beginTransaction();
//取得持久化對象
User user = session.get(User.class, "amigo");
session.delete(user) ;
ts.commit();
} catch (Exception e) {
if (ts != null) {
ts.rollback();
}
} finally {
HibernateSessionFactory.closeSession();
}
4.2批量刪除對象
對於批量刪除對象的情況，開發人員可以在取得待刪除的對象列表後，一個一個的將對象刪除，對於每個對象的刪除方法，見3.4.1小節。開發人員還可以hql語句來做批量刪除。
Eg. 該實例通過delete語句來刪除記錄，除了loginName為「amigo」的對象為，其餘都刪除，代碼如下所示：
Session session = HibernateSessionFactory.getSession();
Transaction ts = null;
try {
ts = session.beginTransaction();
String hql = "delete User as user where user.loginName != 'amigo'";
Query query = session.createQuery(hql);
int count = query.executeUpdate();
ts.commit();
System.out.println("delete count : " + count); //刪除條數
} catch (Exception e) {
if (ts != null) {
ts.rollback();
}
} finally {
HibernateSessionFactory.closeSession();
}

㈤ SQL 每個分類顯示一個記錄

SELECT Pic.Cat_ID，Cat_Name，Pic_Url，Pic_Date
FROM [Pic]
INNER JOIN [Pic_Cat]
ON (Pic.Cat_ID = Pic_Cat.Cat_ID)
WHERE (Pic.Pic_ID IN (
SELECT MAX(Pic_ID)
FROM [Pic]
GROUP BY Pic_Cat
))
這只是一種用IN子查詢的思路，也可以用相關子查詢的方法。