這里是sql語句的索引

SQL語句集錦


--語?句????????????????????????????????功?能
--數據操作
SELECT??????--從數據庫表中檢索數據行和列
INSERT??????--向數據庫表添加新數據行
DELETE??????--從數據庫表中刪除數據行
UPDATE??????--更新數據庫表中的數據
--數據定義
CREATE?TABLE????--創建一個數據庫表
DROP?TABLE?????--從數據庫中刪除表
ALTER?TABLE?????--修改數據庫表結構
CREATE?VIEW?????--創建一個視圖
DROP?VIEW?????--從數據庫中刪除視圖
CREATE?INDEX????--為數據庫表創建一個索引
DROP?INDEX?????--從數據庫中刪除索引
CREATE?PROCEDURE???--創建一個存儲過程
DROP?PROCEDURE????--從數據庫中刪除存儲過程
CREATE?TRIGGER????--創建一個觸發器
DROP?TRIGGER????--從數據庫中刪除觸發器
CREATE?SCHEMA????--向數據庫添加一個新模式
DROP?SCHEMA?????--從數據庫中刪除一個模式
CREATE?DOMAIN????--創建一個數據值域
ALTER?DOMAIN????--改變域定義
DROP?DOMAIN?????--從數據庫中刪除一個域
--數據控制
GRANT??????--授予用戶訪問權限
DENY??????--拒絕用戶訪問
REVOKE??????--解除用戶訪問權限
--事務控制
COMMIT??????--結束當前事務
ROLLBACK?????--中止當前事務
SET?TRANSACTION????--定義當前事務數據訪問特征
--程序化SQL
DECLARE??????--為查詢設定游標
EXPLAN??????--為查詢描述數據訪問計劃
OPEN??????--檢索查詢結果打開一個游標
FETCH??????--檢索一行查詢結果
CLOSE??????--關閉游標
PREPARE??????--為動態執行準備SQL?語句
EXECUTE??????--動態地執行SQL?語句
DESCRIBE?????--描述準備好的查詢


?

---局部變量
declare?@id?char(10)
--set?@id?=?'10010001'
select?@id?=?'10010001'


?

---全局變量
---必須以@@開頭


?

--IF?ELSE
declare?@x?int?@y?int?@z?int
select?@x?=?1?@y?=?2?@z=3
if?@x?>?@y
print?'x?>?y'?--打印字符串'x?>?y'
else?if?@y?>?@z
print?'y?>?z'
else?print?'z?>?y'


?

--CASE
use?pangu
update?employee
set?e_wage?=
case
??when?job_level?=?’1’?then?e_wage*1.08
??when?job_level?=?’2’?then?e_wage*1.07
??when?job_level?=?’3’?then?e_wage*1.06
??else?e_wage*1.05
end


?

--WHILE?CONTINUE?BREAK
declare?@x?int?@y?int?@c?int
select?@x?=?1?@y=1
while?@x?<?3
begin
??print?@x?--打印變量x?的值
??while?@y?<?3
???begin
????select?@c?=?100*@x?+?@y
????print?@c?--打印變量c?的值
????select?@y?=?@y?+?1
???end
??select?@x?=?@x?+?1
??select?@y?=?1
end


?

--WAITFOR
--例?等待1?小時2?分零3?秒后才執行SELECT?語句
waitfor?delay?’01:02:03’
select?*?from?employee
--例?等到晚上11?點零8?分后才執行SELECT?語句
waitfor?time?’23:08:00’
select?*?from?employee


?

***SELECT***


?

???select?*(列名)?from?table_name(表名)?where?column_name?operator?value
???ex:(宿主)
??select?*?from?stock_information?where?stockid???=?str(nid)
?????stockname?=?'str_name'?
?????stockname?like?'%?find?this?%'?
?????stockname?like?'[a-zA-Z]%'?---------?([]指定值的范圍)
?????stockname?like?'[^F-M]%'???---------?(^排除指定范圍)
?????---------?只能在使用like關鍵字的where子句中使用通配符)
?????or?stockpath?=?'stock_path'
?????or?stocknumber?<?1000
?????and?stockindex?=?24
?????not?stock***?=?'man'
?????stocknumber?between?20?and?100
?????stocknumber?in(10,20,30)
?????order?by?stockid?desc(asc)?---------?排序，desc-降序，asc-升序
?????order?by?1,2?---------?by列號
?????stockname?=?(select?stockname?from?stock_information??where?stockid??=?4)
?????---------?子查詢
?????---------?除非能確保內層select只返回一個行的值，
?????---------?否則應在外層where子句中用一個in限定符
??select?distinct?column_name?form?table_name?---------?distinct指定檢索獨有的列值，不重復
??select?stocknumber?,"stocknumber?+?10"?=?stocknumber?+?10?from?table_name
??select?stockname?,?"stocknumber"?=?count(*)?from?table_name?group?by?stockname
??????????????????????????????????????---------?group?by?將表按行分組,指定列中有相同的值
??????????having?count(*)?=?2??---------??having選定指定的組
????????
??select?*?
??from?table1,?table2??????????????????
??where?table1.id?*=?table2.id?--------?左外部連接，table1中有的而table2中沒有得以null表示
?????table1.id?=*?table2.id?--------?右外部連接?


?

??select?stockname?from?table1
??union?[all]??-----??union合并查詢結果集，all-保留重復行
??select?stockname?from?table2


?

***insert***


?

??insert?into?table_name?(Stock_name,Stock_number)?value?("xxx","xxxx")
??????????????value?(select?Stockname?,?Stocknumber?from?Stock_table2)---value為select語句


?

***update***


?

??update?table_name?set?Stockname?=?"xxx"?[where?Stockid?=?3]
?????????Stockname?=?default
?????????Stockname?=?null
?????????Stocknumber?=?Stockname?+?4


?

***delete***


?

??delete?from?table_name?where?Stockid?=?3
??truncate?table_name?-----------?刪除表中所有行，仍保持表的完整性
??drop?table?table_name?---------------?完全刪除表


?

***alter?table***?---?修改數據庫表結構


?

??alter?table?database.owner.table_name?add?column_name?char(2)?null?.....
??sp_help?table_name?----?顯示表已有特征
??create?table?table_name?(name?char(20),?age?smallint,?lname?varchar(30))
??insert?into?table_name?select?.........?-----?實現刪除列的方法（創建新表）
??alter?table?table_name?drop?constraint?Stockname_default?----?刪除Stockname的default約束
????
***function(/*常用函數*/)***


?

----統計函數----
AVG????--求平均值
COUNT???--統計數目
MAX????--求最大值
MIN????--求最小值
SUM????--求和


?

--AVG
use?pangu
select?avg(e_wage)?as?dept_avgWage
from?employee
group?by?dept_id


?

--MAX
--求工資最高的員工姓名
use?pangu
select?e_name
from?employee
where?e_wage?=
(select?max(e_wage)
??from?employee)


?

--STDEV()
--STDEV()函數返回表達式中所有數據的標準差


?

--STDEVP()
--STDEVP()函數返回總體標準差


?

--VAR()
--VAR()函數返回表達式中所有值的統計變異數


?

--VARP()
--VARP()函數返回總體變異數


?

----算術函數----


?

/***三角函數***/
SIN(float_expression)?--返回以弧度表示的角的正弦
COS(float_expression)?--返回以弧度表示的角的余弦
TAN(float_expression)?--返回以弧度表示的角的正切
COT(float_expression)?--返回以弧度表示的角的余切
/***反三角函數***/
ASIN(float_expression)?--返回正弦是FLOAT?值的以弧度表示的角
ACOS(float_expression)?--返回余弦是FLOAT?值的以弧度表示的角
ATAN(float_expression)?--返回正切是FLOAT?值的以弧度表示的角
ATAN2(float_expression1,float_expression2)?
????????--返回正切是float_expression1?/float_expres-sion2的以弧度表示的角
DEGREES(numeric_expression)
???????????????????????--把弧度轉換為角度返回與表達式相同的數據類型可為
????????--INTEGER/MONEY/REAL/FLOAT?類型
RADIANS(numeric_expression)?--把角度轉換為弧度返回與表達式相同的數據類型可為
????????--INTEGER/MONEY/REAL/FLOAT?類型
EXP(float_expression)??--返回表達式的指數值
LOG(float_expression)??--返回表達式的自然對數值
LOG10(float_expression)--返回表達式的以10?為底的對數值
SQRT(float_expression)?--返回表達式的平方根
/***取近似值函數***/
CEILING(numeric_expression)??--返回>=表達式的最小整數返回的數據類型與表達式相同可為
????????--INTEGER/MONEY/REAL/FLOAT?類型
FLOOR(numeric_expression)????--返回<=表達式的最小整數返回的數據類型與表達式相同可為
????????--INTEGER/MONEY/REAL/FLOAT?類型
ROUND(numeric_expression)????--返回以integer_expression?為精度的四舍五入值返回的數據
????????--類型與表達式相同可為INTEGER/MONEY/REAL/FLOAT?類型
ABS(numeric_expression)??????--返回表達式的絕對值返回的數據類型與表達式相同可為
????????--INTEGER/MONEY/REAL/FLOAT?類型
SIGN(numeric_expression)?????--測試參數的正負號返回0?零值1?正數或-1?負數返回的數據類型
????????--與表達式相同可為INTEGER/MONEY/REAL/FLOAT?類型
PI()???????--返回值為π?即3.1415926535897936
RAND([integer_expression])???--用任選的[integer_expression]做種子值得出0-1?間的隨機浮點數


?


----字符串函數----
ASCII()?????????--函數返回字符表達式最左端字符的ASCII?碼值
CHAR()???--函數用于將ASCII?碼轉換為字符
????--如果沒有輸入0?~?255?之間的ASCII?碼值CHAR?函數會返回一個NULL?值
LOWER()???--函數把字符串全部轉換為小寫
UPPER()???--函數把字符串全部轉換為大寫
STR()???--函數把數值型數據轉換為字符型數據
LTRIM()???--函數把字符串頭部的空格去掉
RTRIM()???--函數把字符串尾部的空格去掉
LEFT(),RIGHT(),SUBSTRING()??--函數返回部分字符串
CHARINDEX(),PATINDEX()??--函數返回字符串中某個指定的子串出現的開始位置
SOUNDEX()??--函數返回一個四位字符碼?
????--SOUNDEX函數可用來查找聲音相似的字符串但SOUNDEX函數對數字和漢字均只返回0?值?????
DIFFERENCE()????--函數返回由SOUNDEX?函數返回的兩個字符表達式的值的差異
????--0?兩個SOUNDEX?函數返回值的第一個字符不同
????--1?兩個SOUNDEX?函數返回值的第一個字符相同
????--2?兩個SOUNDEX?函數返回值的第一二個字符相同
????--3?兩個SOUNDEX?函數返回值的第一二三個字符相同
????--4?兩個SOUNDEX?函數返回值完全相同
???????????????????????????????????????


?

QUOTENAME()??--函數返回被特定字符括起來的字符串
/*select?quotename('abc',?'{')?quotename('abc')
運行結果如下
----------------------------------{
{abc}?[abc]*/


?

REPLICATE()?????--函數返回一個重復character_expression?指定次數的字符串
/*select?replicate('abc',?3)?replicate(?'abc',?-2)
運行結果如下
-----------?-----------
abcabcabc?NULL*/


?

REVERSE()???????--函數將指定的字符串的字符排列順序顛倒
REPLACE()???????--函數返回被替換了指定子串的字符串
/*select?replace('abc123g',?'123',?'def')
運行結果如下
-----------?-----------
abcdefg*/


?

SPACE()???--函數返回一個有指定長度的空白字符串
STUFF()???--函數用另一子串替換字符串指定位置長度的子串


?


----數據類型轉換函數----
CAST()?函數語法如下
CAST()?(<expression>?AS?<data_?type>[?length?])
CONVERT()?函數語法如下
CONVERT()?(<data_?type>[?length?],?<expression>?[,?style])


?

select?cast(100+99?as?char)?convert(varchar(12),?getdate())
運行結果如下
------------------------------?------------
199???Jan?15?2000


?

----日期函數----
DAY()???--函數返回date_expression?中的日期值
MONTH()???--函數返回date_expression?中的月份值
YEAR()???--函數返回date_expression?中的年份值
DATEADD(<datepart>?,<number>?,<date>)?
????--函數返回指定日期date?加上指定的額外日期間隔number?產生的新日期
DATEDIFF(<datepart>?,<number>?,<date>)
????--函數返回兩個指定日期在datepart?方面的不同之處
DATENAME(<datepart>?,?<date>)??--函數以字符串的形式返回日期的指定部分
DATEPART(<datepart>?,?<date>)??--函數以整數值的形式返回日期的指定部分
GETDATE()??--函數以DATETIME?的缺省格式返回系統當前的日期和時間


?

----系統函數----
APP_NAME()??????--函數返回當前執行的應用程序的名稱
COALESCE()??--函數返回眾多表達式中第一個非NULL?表達式的值
COL_LENGTH(<'table_name'>,?<'column_name'>)?--函數返回表中指定字段的長度值
COL_NAME(<table_id>,?<column_id>)???--函數返回表中指定字段的名稱即列名
DATALENGTH()?--函數返回數據表達式的數據的實際長度
DB_ID(['database_name'])?--函數返回數據庫的編號
DB_NAME(database_id)??--函數返回數據庫的名稱
HOST_ID()?????--函數返回服務器端計算機的名稱
HOST_NAME()?????--函數返回服務器端計算機的名稱
IDENTITY(<data_type>[,?seed?increment])?[AS?column_name])
--IDENTITY()?函數只在SELECT?INTO?語句中使用用于插入一個identity?column列到新表中
/*select?identity(int,?1,?1)?as?column_name
into?newtable
from?oldtable*/
ISDATE()??--函數判斷所給定的表達式是否為合理日期
ISNULL(<check_expression>,?<replacement_value>)?--函數將表達式中的NULL?值用指定值替換
ISNUMERIC()??--函數判斷所給定的表達式是否為合理的數值
NEWID()???--函數返回一個UNIQUEIDENTIFIER?類型的數值
NULLIF(<expression1>,?<expression2>)
--NULLIF?函數在expression1?與expression2?相等時返回NULL?值若不相等時則返回expression1?的值
精妙SQL語句
說明：復制表(只復制結構,源表名：a?新表名：b)
SQL:?select?*?into?b?from?a?where?1<>1
說明：拷貝表(拷貝數據,源表名：a?目標表名：b)
SQL:?insert?into?b(a,?b,?c)?select?d,e,f?from?b;
說明：顯示文章、提交人和最后回復時間
SQL:?select?a.title,a.username,b.adddate?from?table?a,(select?max(adddate)?adddate?from?table?where?table.title=a.title)?b
說明：外連接查詢(表名1：a?表名2：b)
SQL:?select?a.a,?a.b,?a.c,?b.c,?b.d,?b.f?from?a?LEFT?OUT?JOIN?b?ON?a.a?=?b.c
說明：日程安排提前五分鐘提醒
SQL:?select?*?from?日程安排?where?datediff('minute',f開始時間,getdate())>5

說明：兩張關聯表，刪除主表中已經在副表中沒有的信息
SQL:?
delete?from?info?where?not?exists?(?select?*?from?infobz?where?info.infid=infobz.infid?)?
說明：--
SQL:?
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
說明：--
SQL:?
select?*?from?studentinfo?where?not?exists (select?*?from?student?where?studentinfo.id=student.id)?and?系名稱='"& strdepartmentname&"'?and?專業名稱='"&strprofessionname& "'?order?by?性別,生源地,高考總成績
說明：
從數據庫中去一年的各單位電話費統計(電話費定額賀電化肥清單兩個表來源）
SQL:?
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')
說明：四表聯查問題：
SQL:?select?*?from?a?left?inner?join?b?on?a.a=b.b?right?inner?join?c?on?a.a=c.c?inner?join?d?on?a.a=d.d?where?.....
說明：得到表中最小的未使用的ID號
SQL:
SELECT?(CASE?WHEN?EXISTS(SELECT?*?FROM?Handle?b?WHERE?b.HandleID?=?1)?THEN?MIN(HandleID)?+?1?ELSE?1?END)?as?HandleID
FROM?Handle
WHERE?NOT?HandleID?IN?(SELECT?a.HandleID?-?1?FROM?Handle?a)

:?我在ms?sql中建了一個表,可由于種種原因有些記錄重復了
:?記錄完全的一模一樣。
:?現在我想把重復的都刪掉,只保留重復記錄中的第一條。
:?我在database好象看到有介紹oracle的，


select?distinct?*?into?#table_name?from?table_name
delete?from?table_name
select?*?into?table_name?from?#table_name
drop?table?#table_name

與此相關的是“select?into”選項，可以在數據庫屬性
對話框中，勾起來此項，或者在Query?Analyzer中執行
execute?sp_dboption?'db_name','select?into','true'
開啟。默認值是關閉的。
?






SQL:SELECT?NEWID()


自連接取出榮于數據
把所有姓名相同的只取出一個
select?a.name?from?table_name?a?where?a.id?in?
(select?b.id?from?table_name?b?where?a.id<>b.id)
同理刪除榮譽數據
delete?from?table_name??where?table_name.id?in?
(select?b.id?from?table_name?b?where?table_name.id<>b.id)




SELECT?DISTINCT?TSD.Time_Sheet_Dtl_Record_No,TSD.User_Record_No,TP.Period_Start_Date?,TP.Period_End_Date?INTO?#temp?FROM?Time_Sheet_Details?TSD,?Time_Sheet_Period?TP?,User_Group_User_Relationship?UGUR,User_Group_Master?UGM?,User_Data_Access_Right?UDAR?WHERE?TSD.status?='TS_WFMGRA'?AND?DATEDIFF(dd,TP.period_start_date,TSD.work_Date)>=0?AND?DATEDIFF(dd,TP.period_end_date,TSD.work_Date)<=0?AND?UGUR.User_Record_No?=?TSD.User_Record_No?AND?UGM.User_Group_Record_No=UGUR.User_Group_Record_No?AND?UGM.User_Group_Name?IN('Technician','Engineer')?AND?UDAR.User_Record_No?=?TSD.User_Record_No?AND?UDAR.Division_Record_No?IN(1)?SELECT?DISTINCT?A.User_Record_No,?B.Staff_No,?B.Full_Name,B.Job_Title,?SUM(working_hour)?AS?Working_Hours,SUM(ot)?AS?OT_HOURS,?C.Period_Start_Date,C.Period_End_Date?INTO?#temp2?FROM?Time_Sheet_Details?A?INNER?JOIN?User_Master?B?ON?B.User_Record_No=?A.User_Record_No?INNER?JOIN?#temp?C?ON?C.Time_Sheet_Dtl_Record_No?=?A.Time_Sheet_Dtl_Record_No?GROUP?BY?A.User_Record_No,?B.Staff_No,B.Full_Name,B.Job_Title,?C.Period_Start_Date,C.Period_End_Date?HAVING?COUNT(*)?=?DATEDIFF(d,C.Period_Start_Date,C.Period_End_Date)+1?Select?A.User_Record_No,A.Staff_No,A.Full_Name,A.Job_Title?,A.Working_Hours,A.OT_HOURS,A.Period_Start_Date,A.Period_End_Date?,DM.Division_Code?INTO?#temp3?FROM?#temp2?AS?A?INNER?JOIN?User_Data_Access_Right?UDAR?ON?UDAR.User_Record_No?=?A.User_Record_No?INNER?JOIN?Division_Master?DM?ON?DM.Division_Record_No?=?UDAR.Division_Record_No?SELECT?*?From?#temp3?order?by?1,7,8;?Select?Count(Distinct?User_Record_No+Period_Start_Date+Period_End_Date)?From?#temp3?DROP?TABLE?#temp?,#temp2,#temp3




select?a,b,c?from?tablename?ta?where?a=(select?max(a)?from?tablename?tb?where?tb.b=ta.b)
選擇在每一組b值相同的數據中對應的a最大的(換成average或別的函數或子查詢,你會有意想不到的發現)記錄的所有信息.
類似這樣的用法可以用于論壇每月排行榜,每月熱銷產品分析,按科目成績排名,等等.
上面的許多同志對子查詢存在有偏見與誤解,其實在一個好的數據分析程序中,子查詢可以簡化很多程序邏輯.



獲益不淺啊.
SELECT?DISTINCT?TSD.Time_Sheet_Dtl_Record_No,TSD.User_Record_No,TP.Period_Start_Date?,TP.Period_End_Date?INTO?#temp?FROM?Time_Sheet_Details?TSD,?Time_Sheet_Period?TP?,User_Group_User_Relationship?UGUR,User_Group_Master?UGM?,User_Data_Access_Right?UDAR?WHERE?TSD.status?='TS_WFMGRA'?AND?DATEDIFF(dd,TP.period_start_date,TSD.work_Date)>=0?AND?DATEDIFF(dd,TP.period_end_date,TSD.work_Date)<=0?AND?UGUR.User_Record_No?=?TSD.User_Record_No?AND?UGM.User_Group_Record_No=UGUR.User_Group_Record_No?AND?UGM.User_Group_Name?IN('Technician','Engineer')?AND?UDAR.User_Record_No?=?TSD.User_Record_No?AND?UDAR.Division_Record_No?IN(1)?SELECT?DISTINCT?A.User_Record_No,?B.Staff_No,?B.Full_Name,B.Job_Title,?SUM(working_hour)?AS?Working_Hours,SUM(ot)?AS?OT_HOURS,?C.Period_Start_Date,C.Period_End_Date?INTO?#temp2?FROM?Time_Sheet_Details?A?INNER?JOIN?User_Master?B?ON?B.User_Record_No=?A.User_Record_No?INNER?JOIN?#temp?C?ON?C.Time_Sheet_Dtl_Record_No?=?A.Time_Sheet_Dtl_Record_No?GROUP?BY?A.User_Record_No,?B.Staff_No,B.Full_Name,B.Job_Title,?C.Period_Start_Date,C.Period_End_Date?HAVING?COUNT(*)?=?DATEDIFF(d,C.Period_Start_Date,C.Period_End_Date)+1?Select?A.User_Record_No,A.Staff_No,A.Full_Name,A.Job_Title?,A.Working_Hours,A.OT_HOURS,A.Period_Start_Date,A.Period_End_Date?,DM.Division_Code?INTO?#temp3?FROM?#temp2?AS?A?INNER?JOIN?User_Data_Access_Right?UDAR?ON?UDAR.User_Record_No?=?A.User_Record_No?INNER?JOIN?Division_Master?DM?ON?DM.Division_Record_No?=?UDAR.Division_Record_No?SELECT?*?From?#temp3?order?by?1,7,8;?Select?Count(Distinct?User_Record_No+Period_Start_Date+Period_End_Date)?From?#temp3?DROP?TABLE?#temp?,#temp2,#temp3
:)
cpp2017(長安不見使人愁)這么長一句，少見，能否介紹介紹它的功力？^_^
SELECT?DISTINCT?TSD.Time_Sheet_Dtl_Record_No,TSD.User_Record_No,TP.Period_Start_Date?,TP.Period_End_Date?INTO?#temp?FROM?Time_Sheet_Details?TSD,?Time_Sheet_Period?TP?,User_Group_User_Relationship?UGUR,User_Group_Master?UGM?,User_Data_Access_Right?UDAR?WHERE?TSD.status?='TS_WFMGRA'?AND?DATEDIFF(dd,TP.period_start_date,TSD.work_Date)>=0?AND?DATEDIFF(dd,TP.period_end_date,TSD.work_Date)<=0?AND?UGUR.User_Record_No?=?TSD.User_Record_No?AND?UGM.User_Group_Record_No=UGUR.User_Group_Record_No?AND?UGM.User_Group_Name?IN('Technician','Engineer')?AND?UDAR.User_Record_No?=?TSD.User_Record_No?AND?UDAR.Division_Record_No?IN(1)?SELECT?DISTINCT?A.User_Record_No,?B.Staff_No,?B.Full_Name,B.Job_Title,?SUM(working_hour)?AS?Working_Hours,SUM(ot)?AS?OT_HOURS,?C.Period_Start_Date,C.Period_End_Date?INTO?#temp2?FROM?Time_Sheet_Details?A?INNER?JOIN?User_Master?B?ON?B.User_Record_No=?A.User_Record_No?INNER?JOIN?#temp?C?ON?C.Time_Sheet_Dtl_Record_No?=?A.Time_Sheet_Dtl_Record_No?GROUP?BY?A.User_Record_No,?B.Staff_No,B.Full_Name,B.Job_Title,?C.Period_Start_Date,C.Period_End_Date?HAVING?COUNT(*)?=?DATEDIFF(d,C.Period_Start_Date,C.Period_End_Date)+1?Select?A.User_Record_No,A.Staff_No,A.Full_Name,A.Job_Title?,A.Working_Hours,A.OT_HOURS,A.Period_Start_Date,A.Period_End_Date?,DM.Division_Code?INTO?#temp3?FROM?#temp2?AS?A?INNER?JOIN?User_Data_Access_Right?UDAR?ON?UDAR.User_Record_No?=?A.User_Record_No?INNER?JOIN?Division_Master?DM?ON?DM.Division_Record_No?=?UDAR.Division_Record_No?SELECT?*?From?#temp3?order?by?1,7,8;?Select?Count(Distinct?User_Record_No+Period_Start_Date+Period_End_Date)?From?#temp3?DROP?TABLE?#temp?,#temp2,#temp3



pwdencrypt
密碼加密？
有些什么作用，不明白
隨機取出10條數據
select?top?10?*?from?tablename?order?by?newid()

功能：
type???vender?pcs
電腦???A????????1
電腦???A????????1
光盤???B????????2
光盤???A????????2
手機???B????????3
手機???C????????3
select?type,sum(case?vender?when?'A'?then?pcs?else?0?end),sum(case?vender?when?'C'?then?pcs?else?0?end),sum(case?vender?when?'B'?then?pcs?else?0?end)?FROM?tablename?group?by?type



Microsoft?SQL?Server是如何加密口令的？未公開的加密函數？?
????????
如果對MSSQL的用戶信息有興趣的，可能會發現master.dbo.sysxlogins里面存放著用戶的口令，可是呢，password字段如果不是null就是一堆看不懂的binary，這個口令是怎么加密的呢？
其實只要仔細看看master.dbo.sp_addlogin就知道了，MSSQL的sp都可以看到代碼，真是不錯。
讓我們來看看它是怎么做的，注意這一行select?@passwd?=?pwdencrypt(@passwd)，這個時后@passwd就被加密了，讓我們也來試一下
DECLARE?@ClearPWD?varchar(255)?
DECLARE?@EncryptedPWD?varbinary(255)
SELECT?@ClearPWD?=?'test'
SELECT?@EncryptedPWD?=?CONVERT(varbinary(255),?pwdencrypt(@ClearPWD))
SELECT?@EncryptedPWD
看上去不錯，確實被加密了，可是我怎么還原呢？?
呵呵，這就沒戲了，口令加密都是單向的，用加密后的密文來比較就可以了。
繼續看看其它用戶相關的sp，可以發現master.dbo.sp_password里面有口令比較的內容。
pwdcompare(@old,?password,?(CASE?WHEN?xstatus&2048?=?2048?THEN?1?ELSE?0?END))
不用去理會xstatus，這是一個狀態掩碼，一般我們用的時候就直接用0就可以了
DECLARE?@ClearPWD?varchar(255)?
DECLARE?@EncryptedPWD?varbinary(255)
SELECT?@ClearPWD?=?'test'
SELECT?@EncryptedPWD?=?CONVERT(varbinary(255),?pwdencrypt(@ClearPWD))
SELECT?pwdcompare(@ClearPWD,?@EncryptedPWD,?0)
SELECT?pwdcompare('ErrorPassword',?@EncryptedPWD,?0)
這樣我們就可以使用這兩個函數來加密自己的密碼了，怎么樣，還不錯吧？

引用自
http://www.bgchina.com/daily/bd_1/



SELECT語法：(基本)
SELECT?[DISTINCT]
(column?[{,?column?}?]?)|?*
FROM?table?[?{?,?table}?]
[ORDER?BY?column?[ASC]?|?[DESC
[{?,?column?[ASC]?|?[DESC?}?]?]
WHERE?predicate?[?{?logical-connector?predicate?}?];
------------------------------------------------------
INSERT語法：
INSERT?INTO?table
[(column?{?,column})]
VALUES
(columnvalue?[{,columnvalue}]);
------------------------------------------------------
UPDATE語法：
UPDATE?table
SET?column?=?value?[{,?column?=?value}]
[?WHERE?predicate?[?{?logical-connector?predicate}]];
------------------------------------------------------
DELETE語法：
DELETE?FROM?table
[WHERE?predicate?[?{?logical-connector?predicate}?]?];
------------------------------------------------------

常識補充
統計函數：
AVG(字段名)?得出一個表格欄平均值
COUNT(*|字段名)?對數據行數的統計或對某一欄有值的數據行數統計
MAX(字段名)?取得一個表格欄最大的值
MIN(字段名)?取得一個表格欄最小的值
SUM(字段名)?把數據欄的值相加
eg：
sql="select?sum(字段名)?as?別名?from?數據表?where?條件表達式"

select?name?from?sysobjects?where?type='U''列出數據庫里所有的表名
select?name?from?syscolumns?where?id=object_id('TableName')'列出表里的所有的




source?data
kzx4dm?xbdm?jylsfsdm?...
11??????2???????10
11??????2???????10?
12??????2???????10
12??????1???????20?
12??????1???????20
destination:
kzx4dm??bys_count?yjs_count?jy_ratio
11????????2???????????2????????1.00
12????????3???????????1????????1/3
SELECT?DISTINCT?kzx4dm,(SELECT?COUNT(jylsfsdm)?FROM?tablename?WHERE?kzx4dm=TA.kzx4dm)?AS?bys_count,(SELECT?COUNT(jylsfsdm)?FROM?tablename?WHERE?kzx4dm=TA.kzx4dm?WHERE?jylsfsdm=10)?AS?yjs_count,yjs_count/bys_count?AS?jy_ratio
FROM?tablename?AS?TA

SELECT?DISTINCT?kzx4dm,(SELECT?COUNT(jylsfsdm)?FROM?tablename?WHERE?kzx4dm=TA.kzx4dm)?AS?bys_count,(SELECT?COUNT(jylsfsdm)?FROM?tablename?WHERE?kzx4dm=TA.kzx4dm?AND?jylsfsdm=10)?AS?yjs_count,yjs_count/bys_count?AS?jy_ratio
FROM?tablename?AS?TA



二維表?T(F1,F2,F3,F4,F5,F6,F7)?表示如下關系:
??學生ID????學生姓名????課程ID????課程名稱?????成績??????教師ID????教師姓名?
????S3????????王五????????K4????????政治????????53?????????T4???????趙老師??
????S1????????張三????????K1????????數學????????61?????????T1???????張老師??
????S2????????李四????????K3????????英語????????88?????????T3???????李老師??
????S1????????張三????????K4????????政治????????77?????????T4???????趙老師??
????S2????????李四????????K4????????政治????????67?????????T5???????周老師??
????S3????????王五????????K2????????語文????????90?????????T2???????王老師??
????S3????????王五????????K1????????數學????????55?????????T1???????張老師??
????S1????????張三????????K2????????語文????????81?????????T2???????王老師??
????S4????????趙六????????K2????????語文????????59?????????T1???????王老師??
????S1????????張三????????K3????????英語????????37?????????T3???????李老師??
????S2????????李四????????K1????????數學????????81?????????T1???????張老師??
??請以一句?T-SQL?(Ms?SQL?Server)?或?Jet?SQL?(Ms?Access)?在?原表?T?基礎上作答
1.如果?T?表還有一字段?F0?數據類型為自動增量整型(唯一,不會重復),
??而且?T?表中含有除?F0?字段外,請刪除其它字段完全相同的重復多余的臟記錄數據:
delete?from?t??where?f0?in(select?max(f0)?from?t?group?by?f1,f2,f3,f4,f5,f6,f7?having?count(f0)>1)
2.列印各科成績最高和最低的記錄:?(就是各門課程的最高、最低分的學生和老師)
??課程ID,課程名稱,最高分,學生ID,學生姓名,教師ID,教師姓名,最低分,學生ID,學生姓名,教師ID,教師姓名
select?tb.f4,tb.f3,tb1.f5,tb.f1,tb.f2,tb.f6,tb.f7,tb2.f5,tb2.f1,tb2.f2,tb2.f6,tb2.f7?from?t?tb?where?
f5=(select?max(f5)?from?t?where?t.f4=tb.f4)
join?select?f2,f7?from?t?tb2?where?
f5=(select?min(f5)?from?t?where?t.f4=tb2.f4)
on?tb.f4=tb2.f4
先完成一個,想想在做下一個.
3.按成績從高到低順序,列印所有學生四門(數學,語文,英語,政治)課程成績:?(就是每個學生的四門課程的成績單)
??學生ID,學生姓名,數學,語文,英語,政治,有效課程數,有效平均分
??(注:?有效課程即在?T?表中有該學生的成績記錄,如不明白可不列印"有效課程數"和"有效平均分")
select?tb1.f1,tb1.f2,count(tb1.f5)?as?scores,sum(tb1.f5)?as?scoresum,?avg(tb1.f5)?AS?average,?
tb2.f5,tb3.f5,tb4.f5,tb5.f5?
from?t?as?tb1?
left?join?t?as?tb2
on?tb1.f0=tb2.f0?and?tb2.f3=k4
left?join?t?as?tb3
on?tb1.f0=tb3.f0?and?tb3.f3=k3
left?join?t?as?tb4
on?tb1.f0=tb4.f0?and?tb4.f3=k2
left?join?t?as?tb5
on?tb1.f0=tb5.f0?and?tb5.f3=k1
grout?by?tb1.f2?order?by?tb1.scoresum?desc
4.按各科不及格率的百分數從低到高和平均成績從高到低順序,統計并列印各科平均成績和不及格率的百分數(用"N行"表示):?(就是分析哪門課程難)
??課程ID,課程名稱,平均成績,及格百分數
select??f3,f4,?(select?count(f1)?from?t?where?t.f4=tb.f4?and?f5<60)/(select?count(f1)?from?t?where?t.f4=tb.f4)?as?failper,((select?sum(f5)?from?t?where?t.f4=tb.f4)/?(select?count(f5)?from?t?where?t.f4=tb.f4))?as?averagescore
from?t?tb?order?by?failper?asc,?as?averagescore?desc


/******?Object:??Stored?Procedure?dbo.dt_checkoutobject????Script?Date:?2003-3-12?9:25:26?******/
create?proc?dbo.dt_checkoutobject
????@chObjectType??char(4),
????@vchObjectName?varchar(255),
????@vchComment????varchar(255),
????@vchLoginName??varchar(255),
????@vchPassword???varchar(255),
????@iVCSFlags?????int?=?0,
????@iActionFlag???int?=?0/*?0?=>?Checkout,?1?=>?GetLatest,?2?=>?UndoCheckOut?*/
as
set?nocount?on
declare?@iReturn?int
declare?@iObjectId?int
select?@iObjectId?=0
declare?@VSSGUID?varchar(100)
select?@VSSGUID?=?'SQLVersionControl.VCS_SQL'
declare?@iReturnValue?int
select?@iReturnValue?=?0
declare?@vchTempText?varchar(255)
/*?this?is?for?our?strings?*/
declare?@iStreamObjectId?int
select?@iStreamObjectId?=?0
????declare?@iPropertyObjectId?int
????select?@iPropertyObjectId?=?(select?objectid?from?dbo.dtproperties?where?property?=?'VCSProjectID')
????declare?@vchProjectName???varchar(255)
????declare?@vchSourceSafeINI?varchar(255)
????declare?@vchServerName????varchar(255)
????declare?@vchDatabaseName??varchar(255)
????exec?dbo.dt_getpropertiesbyid_vcs?@iPropertyObjectId,?'VCSProject',???????@vchProjectName???OUT
????exec?dbo.dt_getpropertiesbyid_vcs?@iPropertyObjectId,?'VCSSourceSafeINI',?@vchSourceSafeINI?OUT
????exec?dbo.dt_getpropertiesbyid_vcs?@iPropertyObjectId,?'VCSSQLServer',?????@vchServerName????OUT
????exec?dbo.dt_getpropertiesbyid_vcs?@iPropertyObjectId,?'VCSSQLDatabase',???@vchDatabaseName??OUT
????if?@chObjectType?=?'PROC'
????begin
????????/*?Procedure?Can?have?up?to?three?streams
???????????Drop?Stream,?Create?Stream,?GRANT?stream?*/
????????exec?@iReturn?=?sp_OACreate?@VSSGUID,?@iObjectId?OUT
????????if?@iReturn?<>?0?GOTO?E_OAError
????????exec?@iReturn?=?sp_OAMethod?@iObjectId,
????????????????????????????????????'CheckOut_StoredProcedure',
????????????????????????????????????NULL,
????????????????????????????????????@sProjectName?=?@vchProjectName,
????????????????????????????????????@sSourceSafeINI?=?@vchSourceSafeINI,
????????????????????????????????????@sObjectName?=?@vchObjectName,
????????????????????????????????????@sServerName?=?@vchServerName,
????????????????????????????????????@sDatabaseName?=?@vchDatabaseName,
????????????????????????????????????@sComment?=?@vchComment,
????????????????????????????????????@sLoginName?=?@vchLoginName,
????????????????????????????????????@sPassword?=?@vchPassword,
????????????????????????????????????@iVCSFlags?=?@iVCSFlags,
????????????????????????????????????@iActionFlag?=?@iActionFlag
????????if?@iReturn?<>?0?GOTO?E_OAError
????????exec?@iReturn?=?sp_OAGetProperty?@iObjectId,?'GetStreamObject',?@iStreamObjectId?OUT
????????if?@iReturn?<>?0?GOTO?E_OAError
????????create?table?#commenttext?(id?int?identity,?sourcecode?varchar(255))
????????select?@vchTempText?=?'STUB'
????????while?@vchTempText?IS?NOT?NULL
????????begin
????????????exec?@iReturn?=?sp_OAMethod?@iStreamObjectId,?'GetStream',?@iReturnValue?OUT,?@vchTempText?OUT
????????????if?@iReturn?<>?0?GOTO?E_OAError
????????????if?(@vchTempText?IS?NOT?NULL)?insert?into?#commenttext?(sourcecode)?select?@vchTempText
????????end
????????select?'VCS'=sourcecode?from?#commenttext?order?by?id
????????select?'SQL'=text?from?syscomments?where?id?=?object_id(@vchObjectName)?order?by?colid
????end
CleanUp:
????return
E_OAError:
????exec?dbo.dt_displayoaerror?@iObjectId,?@iReturn
????GOTO?CleanUp
GO


1.如果?T?表還有一字段?F0?數據類型為自動增量整型(唯一,不會重復),
??而且?T?表中含有除?F0?字段外,請刪除其它字段完全相同的重復多余的臟記錄數據:
delete?from?t??where?f0?in(select?max(f0)?from?t?group?by?f1,f2,f3,f4,f5,f6,f7?having?count(f0)>1)
上面這個sql有問題
正確的如下
DELETE?Legal_Dispute_Lawyer?WHERE?Lawyer_Record_No?IN(SELECT?Lawyer_Record_No?FROM?Legal_Dispute_Lawyer?LDL?WHERE?Lawyer_Record_No(SELECT?TOP?1?Lawyer_Record_No?FROM?Legal_Dispute_Lawyer?WHERE?LD=LDL.LD?AND?Name=LDL.Name?AND?Email=LDL.Email?AND?Phone_No=LDL.Phone_No?AND?Fax_No=LDL.Fax_No))
消除Legal_Dispute_Lawyer?表中除Lawyer_Record_No(自增字段)外其余數據完全相同的記錄.
表結構如下
if?exists?(select?*?from?dbo.sysobjects?where?id?=?object_id(N'[dbo].[Legal_Dispute_Lawyer]')?and?OBJECTPROPERTY(id,?N'IsUserTable')?=?1)
drop?table?[dbo].[Legal_Dispute_Lawyer]
GO
CREATE?TABLE?[dbo].[Legal_Dispute_Lawyer]?(
[Lawyer_Record_No]?[int]?IDENTITY?(1,?1)?NOT?NULL?,
[LD]?[int]?NOT?NULL?,
[Name]?[int]?NOT?NULL?,
[Phone_No]?[varchar]?(255)?COLLATE?Chinese_PRC_CI_AS?NULL?,
[Fax_No]?[varchar]?(255)?COLLATE?Chinese_PRC_CI_AS?NULL?,
?[varchar]?(255)?COLLATE?Chinese_PRC_CI_AS?NULL?
)?ON?[PRIMARY]
GO
以這種方式還可以實現組內消除重復值..

Top


DELETE?Legal_Dispute_Lawyer?WHERE?Lawyer_Record_No?IN(SELECT?Lawyer_Record_No?FROM?Legal_Dispute_Lawyer?LDL?WHERE?Lawyer_Record_No<>(SELECT?TOP?1?Lawyer_Record_No?FROM?Legal_Dispute_Lawyer?WHERE?LD=LDL.LD?AND?Name=LDL.Name?AND?Email=LDL.Email?AND?Phone_No=LDL.Phone_No?AND?Fax_No=LDL.Fax_No))


table1
kzx4dm?xbdm?jylsfsdm?...
10??????2???????8
11??????2???????9?
12??????2???????19
13??????1???????18?
14??????1???????19
select?top?3?with?ties?jylsfsdm?from?table1
結果：
kzx4dm?xbdm?jylsfsdm?...
12??????2???????19
14??????1???????19
13??????1???????18?
11??????2???????9?
說明：取出并列排行的所有記錄

Top
回復人：?lsqteng(阿琦)?(?)?信譽：102?2003-3-21?14:18:11?得分:0

說到語法我就來興趣了，哈哈，請看:
SELECT?statement::=
<query_experssion>
[ORDER?BY?{order_by_expression|Column_position[ASC|DESC]}
[,...n]]
[COMPUTE
{{AVG|COUNT|MAX|MIN|SUM}(exression)}[,...n]
[BY?expression[,...n]]
]
[FOR?{BROWSE?|XML{RAW|AUTO|EXPLICIT}
??[,XMLDATA]
??[,ELEMENTS]
??[,BINARY?base64]
}
]
[OPTION(<query_hint>[,...n])]
???<query?expression>::=
{<query?specification>|(<query?expression>)}
[UNION[ALL]<query?specification|(<query?expression<)[...n]]
<query?specification>::=
SELECT?[ALL|DISTINCT]
??[{TOP?integer|TOP?integer?PERCENT}[WITH?TIES]]
<select?_list>
[INTO?new_table]
[FROM{<table_source>}[,...n]]
[WHERE?<search_condition>]
[GROUP?BY?[ALL]group_by_expression[,...n]
??[WITH{CUBE|ROLLUP}]
]
[HAVING<search_condition>}
哎，行了吧，這還是select?語句的形式。要看完全的，喔，那可不得了喔。我不寫了，累死了，呵呵


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)


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


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')?&brvbar;&brvbar;?'/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??


SELECT?*?FROM?table?ORDER?BY?id
SELECT?*?FROM?table?ORDER?BY?id?DESC
正反排序,厲害吧!



select?*?into?b?from?a?where?1<>1
這樣生成的b表訪問的用戶沒有select的權限？
請問sql?DX們這個問題怎么解決？

Select?left(field,1)?as?field1?from?table_name?order?by?field?desc



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

條件刪除
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)
)

選擇前數據庫里前10條記錄：
1、select?top?10?*?from?table
2、set?rowcount?10
???select?*?from?table


今天才把合計函數搞定：
???Set?rs=conn.execute("Select?min(id)?as?minID?from?TABLE")?^_^


select?*?from?日程安排?where?datediff('minute',f開始時間,getdate())>5
日程安排提前五分鐘提醒。



??select?*?into?b?from?a?where?1<>1
這樣生成的b表訪問的用戶沒有select的權限？
請問sql?DX們這個問題怎么解決？
指定dbo前綴應該可以解決問題了..


mark!


<%
'取出隨機記錄
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")
%>


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表中



下面這個更實用，就是兩張關聯表，刪除主表中已經在副表中沒有的信息
delete?from?info?where?not?exists?(?select?*?from?infobz?where?info.infid=infobz.infid?)?
這條語句就是刪除?INFO表中infid字段在infobz中不存在的記錄
此語句用來維護數據庫很有用哦。
樓主給點分吧



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?;



數據庫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




select?a,b,c?from?tablename?ta?where?a=(select?max(a)?from?tablename?tb?where?tb.b=ta.b)
選擇在每一組b值相同的數據中對應的a最大的(換成average或別的函數或子查詢,你會有意想不到的發現)記錄的所有信息.
類似這樣的用法可以用于論壇每月排行榜,每月熱銷產品分析,按科目成績排名,等等.
上面的許多同志對子查詢存在有偏見與誤解,其實在一個好的數據分析程序中,子查詢可以簡化很多程序邏輯.

<%
'取出隨機記錄
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()



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



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.
大概再加幾層嵌套,查詢引擎都可以崩潰了..


下面的語句不是精華.但是卻是用很多用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...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;
?

個人主頁?|?引用?|?返回???
?


回復:[分享]SQl語句學習專題
晴天發表評論于2004-10-26?11:18:00??
Top
回復人：?bloodsha(huangxi)?(?)?信譽：98?2003-4-3?8:58:06?得分:0

學

Top
回復人：?csdntoll(低調慣了)?(?)?信譽：147?2003-4-3?11:40:16?得分:0

回復人：?csdntoll(toll)?(?)?信譽：100??2003-3-21?10:02:00??得分:0?
??
我決定：把200分中的180分，獎給貼出最精妙的SQL的高手！
-------------------------------------------------------------------
想揭貼，可是為難了：
我要遵守自己說過的話（如上），可我菜，鑒別能力有限，怎么辦？

Top
回復人：?wilsonGao(笑傲江湖)?(?)?信譽：101?2003-4-3?13:29:11?得分:0

如何用一句語句實現兩個表的關鍵字倒換？

Top
回復人：?pyz8000(黑洞)?(?)?信譽：101?2003-4-3?18:48:04?得分:0

暈倒，，，收藏～～

Top
回復人：?chinahuman(枯)?(?)?信譽：105?2003-4-3?18:50:07?得分:0

有沒有辦法一條語句實現以下功能：比如一個新聞系統里有很多個管理員，在添加過程中每條都記錄管理員的ID，現想根據用戶ID來實現統計它們添加的條數？

Top
回復人：?yonghengdizhen(剎那←→永恒)?(?)?信譽：112?2003-4-3?19:29:42?得分:0

to??chinahuman(枯)?
一個group?by和一個聚合函數(count)就可以了..
select?count(Title)?from?news?group?by?creade_by
如果需要得到除非聚合字段外的詳細信息那么就得使用相關子查詢了..

Top
回復人：?ld_key(什么名字也想)?(?)?信譽：100?2003-4-4?9:59:55?得分:0

markable

Top
回復人：?superdullwolf(超級大笨狼)?(?)?信譽：99?2003-4-4?22:04:45?得分:0

看我的，四表聯查問題：
select?*?from?a?left?inner?join?b?on?a.a=b.b?right?inner?join?c?on?a.a=c.c??inner?join?d?on?a.a=d.d?where?.....

Top
回復人：?superdullwolf(超級大笨狼)?(?)?信譽：99?2003-4-4?22:11:39?得分:10

應聘做了一個小程序,在多人中表現最好,高興,散分100!!是關于sql查詢顯示的
題目大概是SQL?????表1：班級/老師????表2學生/班級????????表三數學/學生??????表4語文/學生
查詢顯示結果大概是這樣：
班級1??老師1
學生1???數學??語文
學生2???數學??語文
班級2??老師1
學生1???數學??語文
學生2???數學??語文
不及格：
學生1???班級1?老師1??數學??語文?
學生1???班級1?老師1??數學??語文
還有錄入學生和成績的界面
用到了多表之間的inner?join??on?語句，而且我顯示的很漂亮，靠，做了一下午，我好想抽煙啊，當時：）
看我的，四表聯查問題：
select?*?from?a?left?inner?join?b?on?a.a=b.b?right?inner?join?c?on?a.a=c.c??inner?join?d?on?a.a=d.d?where?.....

Top
回復人：?yonghengdizhen(剎那←→永恒)?(?)?信譽：112?2003-4-7?9:43:55?得分:0

左連接右連接內連接全部上馬,的確經典哦.
據說你寫過上萬行代碼,大概這就是最經典的一段代碼了吧.

Top
回復人：?zady(森林木)?(?)?信譽：100?2003-4-7?14:56:01?得分:5

需求：
得到表中最小的未使用的ID號。
例：
table?Name:Handle
HandleID
--------
1
2
5
6
7
--5?Records
執行結果須為3
解決：
SELECT?(CASE?WHEN?EXISTS(SELECT?*?FROM?Handle?b?WHERE?b.HandleID?=?1)?THEN?MIN(HandleID)?+?1?ELSE?1?END)?as?HandleID
FROM??Handle
WHERE?NOT?HandleID?IN?(SELECT?a.HandleID?-?1?FROM?Handle?a)

Top
回復人：?Iamfish(呆魚)?(?)?信譽：105?2003-4-8?13:36:00?得分:80

兩臺SQL服務器上的一個數據表同步!
drop?procedure?dbSync
GO
/*?????數據同步??????????????????????*/
CREATE?PROCEDURE?dbSync?
???????@sTabelName??varchar(255),????--要同步的表名
???????@sKeyField???varchar(255),????--關鍵字段
???????@sServer?????varchar(255),????--服務器名稱或IP
???????@sUserName???varchar(255),????--登錄到服務器的用戶名，一般為sa
???????@sPassWord???varchar(32)??????--用戶登錄到服務器的密碼?
AS
???/*刪除臨時表*/
??if?exists?(select?*?from?dbo.sysobjects?where?id?=?object_id(N'tempTbl')?and?OBJECTPROPERTY(id,?N'IsUserTable')?=?1)
??drop?table?tempTbl
??
????????????????
??declare?@sql?VARCHAR(2000)?
??/*把表@sTabelName[遠程]的數據拷貝到臨時表*/
???
??set?@sql='select?*?into?tempTbl?from?'
??set?@sql=@sql?+?'?OPENDATASOURCE(?'
??set?@sql=@sql?+?'''SQLOLEDB.1'','
??set?@sql=@sql?+?'''Persist?Security?Info=True;User?ID='?+?@sUserName
??set?@sql=@sql?+?';Password='?+?@sPassWord
??set?@sql=@sql?+?';Initial?Catalog=toys;Data?Source='?+?@sServer
??set?@sql=@sql?+?''').toys.dbo.'+@sTabelName
??EXEC(@sql)??
??
??/*?把@sTabelName[本地]中的@sTabelName[遠程]表中沒有的數據插入到臨時表中*/
??set?@sql='insert?into?tempTbl?select?*?from?'+@sTabelName+'?where?['+@sKeyField+']?not?in?(select?['+@sKeyField+']?from?tempTbl)'
??EXEC(@sql)
??
??/*清空表@sTabelName[本地]*/
??set?@sql='truncate?table?'+@sTabelName
??EXEC(@sql)
??
??--取得列名
??declare?@MySql?VARCHAR(2000)
??set?@MySql=''
??declare?@title?varchar(20)
??DECLARE?titles_cursor?CURSOR?FOR?
??SELECT?name?from?syscolumns?where?id=object_id(@sTabelName)
??OPEN?titles_cursor
??FETCH?NEXT?FROM?titles_cursor?INTO?@title
??WHILE?@@FETCH_STATUS?=?0
??BEGIN
????if?@title<>'id'?
????begin
??????if?@MySql?=?''
????????set?@MySql?=?@MySql?+?@title
??????else
????????set?@MySql?=?@MySql?+?','?+?@title
????end
????FETCH?NEXT?FROM?titles_cursor?INTO?@title
??END
??CLOSE?titles_cursor
??DEALLOCATE?titles_cursor
??--取列名結束
??/*把臨時表的內容插入到表@sTabelName[本地]*/
??set?@sql='insert?into?'+@sTabelName+'?select?'+@MySql+'?from?tempTbl'
??EXEC(@sql)?
??/*刪除臨時表*/
??if?exists?(select?*?from?dbo.sysobjects?where?id?=?object_id(N'tempTbl')?and?OBJECTPROPERTY(id,?N'IsUserTable')?=?1)
??drop?table?tempTbl
GO

Top
回復人：?lizongqi(英雄啊)?(?)?信譽：100?2003-4-8?15:02:07?得分:0

收藏ing..

Top
回復人：?Swanzy(志遠)?(?)?信譽：100?2003-4-8?18:06:08?得分:0

收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏收藏

Top
回復人：?zhyx21century(zhyx)?(?)?信譽：100?2003-4-9?11:19:17?得分:0

UP

Top
回復人：?kingkout(kingkout)?(?)?信譽：100?2003-4-9?14:05:22?得分:0

GZ

Top
回復人：?xxl0914(microlin)?(?)?信譽：105?2003-4-9?14:23:49?得分:0

用sql語句對不同的幾張表做笛卡爾積

Top
回復人：?lizongqi(英雄啊)?(?)?信譽：100?2003-4-10?10:44:56?得分:0

收藏ing....

Top
回復人：?xieyj(快樂天使)?(?)?信譽：103?2003-4-11?8:36:16?得分:5

查找一個已知字段所在的表名：
Select?Name?From?Sysobjects?Where?Id?in?(select?id?from?syscolumns?where?name='字段名')

Top
回復人：?leeeel(三角比三星差？)?(?)?信譽：100?2003-4-11?8:55:15?得分:0

強列地反對多個　Select?套在一個語句中使用　！

Top
回復人：?zhaoming1112(隨風往事)?(?)?信譽：100?2003-4-11?11:44:58?得分:0

SELECT套在一起，好用，我不反對。

Top
回復人：?csdnshao(如水人生)?(?)?信譽：105?2003-4-11?11:50:17?得分:0

mark

Top
回復人：?cuoban(搓板)?(?)?信譽：100?2003-4-11?14:57:58?得分:5

前些日子剛寫的
說明：進行判斷，得出兩種應納稅額。
SELECT? 姓名,?BM?AS?部門,?SF1?AS?實發,?SFZH?AS?身份證,?Jjje?AS?獎金,?SF1?+?Jjje?-?860?AS?應納所得,?XL?AS?學歷,?NTG?AS?性別,?GWW?AS?職務,?round((CASE?WHEN?(SF1?+?Jjje?- ?860?<?500)?THEN?(SF1?+?Jjje?-?860)?*?0.05?ELSE?CASE?WHEN?(SF1?+?Jjje? -?860?>?500)?THEN?(SF1?+?GJJ?-?860)?*?0.10?-?25?END?END),?2)?AS?應納稅額 ?FROM?GZ20029?WHERE?(sf1?+?Jjje)?>?860

Top
回復人：?lanyd(山雨欲來風滿樓)?(?)?信譽：100?2003-4-11?16:13:44?得分:0

強！
看完這些，我也成sql專家了，哈哈

Top
回復人：?750906(750906)?(?)?信譽：105?2003-4-11?16:29:33?得分:0

SELECT?A.*,?B.ColumnString?AS?ColumnString,
??????????(SELECT?COUNT(StatuteId)
?????????FROM?StatuteLib_Body
?????????WHERE?ColumnId?IN
???????????????????(SELECT?ColumnId
??????????????????FROM?StatuteLib_ColumnString
??????????????????WHERE?ColumnString?LIKE?B.ColumnString?+?'%'))?AS?StatuteCount
FROM?cw25109.StatuteLib_Column?A?INNER?JOIN
??????cw25109.StatuteLib_ColumnString?B?ON?A.ColumnId?=?B.ColumnId

Top
回復人：?Swanzy(志遠)?(?)?信譽：100?2003-4-12?11:04:11?得分:0

如何查詢表中為空值的所有字段內容？

Top
回復人：?xdk(冼德錕)?(?)?信譽：97?2003-4-12?11:37:01?得分:0

好...我是來搶分加收藏的...

Top
回復人：?yu_shi_bin(清風)?(?)?信譽：100?2003-4-14?13:13:18?得分:0

mark

Top
回復人：?sohi(阿梓)?(?)?信譽：100?2003-4-15?8:20:17?得分:0

good

Top
回復人：?laker_tmj(laker)?(?)?信譽：100?2003-4-15?12:22:45?得分:0

up

Top
回復人：?cboy2003(三碗（cpubook.com))?(?)?信譽：100?2003-4-15?12:26:13?得分:0

不錯
全部收了，我把常用的，最最普通的貼出來大家看看吧，我是對上面的某些半知，對自己貼的
還能用用，哈哈
SQL常用命令使用方法：?
(1)?數據記錄篩選：?
sql="select?*?from?數據表?where?字段名=字段值?order?by?字段名?[desc]"?
sql="select?*?from?數據表?where?字段名?like?'%字段值%'?order?by?字段名?[desc]"?
sql="select?top?10?*?from?數據表?where?字段名?order?by?字段名?[desc]"?
sql="select?*?from?數據表?where?字段名?in?('值1','值2','值3')"?
sql="select?*?from?數據表?where?字段名?between?值1?and?值2"?
(2)?更新數據記錄：?
sql="update?數據表?set?字段名=字段值?where?條件表達式"?
sql="update?數據表?set?字段1=值1,字段2=值2?……?字段n=值n?where?條件表達式"?
(3)?刪除數據記錄：?
sql="delete?from?數據表?where?條件表達式"?
sql="delete?from?數據表"?(將數據表所有記錄刪除)?
(4)?添加數據記錄：?
sql="insert?into?數據表?(字段1,字段2,字段3?…)?valuess?(值1,值2,值3?…)"?
sql="insert?into?目標數據表?select?*?from?源數據表"?(把源數據表的記錄添加到目標數據表)?
(5)?數據記錄統計函數：?
AVG(字段名)?得出一個表格欄平均值?
COUNT(*|字段名)?對數據行數的統計或對某一欄有值的數據行數統計?
MAX(字段名)?取得一個表格欄最大的值?
MIN(字段名)?取得一個表格欄最小的值?
SUM(字段名)?把數據欄的值相加?
引用以上函數的方法：?
sql="select?sum(字段名)?as?別名?from?數據表?where?條件表達式"?
set?rs=conn.excute(sql)?
用?rs("別名")?獲取統的計值，其它函數運用同上。?
(5)?數據表的建立和刪除：?
CREATE?TABLE?數據表名稱(字段1?類型1(長度),字段2?類型2(長度)?……?)?
例：CREATE?TABLE?tab01(name?varchar(50),datetime?default?now())?
DROP?TABLE?數據表名稱?(永久性刪除一個數據表)?

Top
回復人：?zigzag81814()?(?)?信譽：100?2003-4-15?13:09:38?得分:0

好

Top
回復人：?skyswan(施望)?(?)?信譽：100?2003-4-15?13:33:36?得分:0

怎么都是用在Sql?Server上的。
有好多是不能用在Oracle的PL/SQL上的
?
?
SQL語法參考手冊?
2001年3月22日?
DB2? 提供了關連式資料庫的查詢語言?ＳＱＬ?(Structured?Query?Language)，是一種非常口語化、既易學又易懂的語法。此一語言幾乎是每個資料庫系統都必須提供的，用以表示關連式的操作，包含了資料的定義（ＤＤＬ）以及資料的處理（ＤＭＬ）。SQL原來拼成SEQUEL，這語言的原型以"系統?R"的名字在?IBM?圣荷西實驗室完成，經過IBM內部及其他的許多使用性及效率測試，其結果相當令人滿意，并決定在系統R?的技術基礎發展出來?IBM?的產品。而且美國國家標準學會（ANSI）及國際標準化組織（ISO）在1987遵循一個幾乎是以?IBM?SQL?為基礎的標準關連式資料語言定義。
一、資料定義?ＤＤＬ（Data?Definition?Language)?
資料定語言是指對資料的格式和形態下定義的語言，他是每個資料庫要建立時候時首先要面對的，舉凡資料分哪些表格關系、表格內的有什麼欄位主鍵、表格和表格之間互相參考的關系等等，都是在開始的時候所必須規劃好的。
１、建表格：
CREATE?TABLE?table_name(?
column1?DATATYPE?[NOT?NULL]?[NOT?NULL?PRIMARY?KEY],?
column2?DATATYPE?[NOT?NULL],
...）
說明：　
DATATYPE?--是資料的格式，詳見表。
NUT?NULL?--可不可以允許資料有空的（尚未有資料填入）。
PRIMARY?KEY?--是本表的主鍵。
２、更改表格　
ALTER?TABLE?table_name?
ADD?COLUMN?column_name?DATATYPE?
說明：增加一個欄位（沒有刪除某個欄位的語法。
ALTER?TABLE?table_name
ADD?PRIMARY?KEY?(column_name)
說明：更改表得的定義把某個欄位設為主鍵。
ALTER?TABLE?table_name
DROP?PRIMARY?KEY?(column_name)
說明：把主鍵的定義刪除。
３、建立索引　
CREATE?INDEX?index_name?ON?table_name?(column_name)
說明：對某個表格的欄位建立索引以增加查詢時的速度。
４、刪除　
DROP?table_name
DROP?index_name
二、的資料形態?DATATYPEs
smallint
16?位元的整數。
interger
32?位元的整數。
decimal(p,s)
p?精確值和?s?大小的十進位整數，精確值p是指全部有幾個數(digits)大小值，s是指小數
點後有幾位數。如果沒有特別指定，則系統會設為?p=5;?s=0?。?
float
32位元的實數。
double
64位元的實數。
char(n)
n?長度的字串，n不能超過?254。
varchar(n)
長度不固定且其最大長度為?n?的字串，n不能超過?4000。
graphic(n)
和?char(n)?一樣，不過其單位是兩個字元?double-bytes，?n不能超過127。這個形態是為
了支援兩個字元長度的字體，例如中文字。
vargraphic(n)
可變長度且其最大長度為?n?的雙字元字串，n不能超過?2000。
date
包含了?年份、月份、日期。
time
包含了?小時、分鐘、秒。
timestamp
包含了?年、月、日、時、分、秒、千分之一秒。
三、資料操作?ＤＭＬ?（Data?Manipulation?Language)
資料定義好之後接下來的就是資料的操作。資料的操作不外乎增加資料（insert)、查詢資料（query）、更改資料（update)?、刪除資料（delete）四種模式，以下分?別介紹他們的語法：
１、增加資料：
INSERT?INTO?table_name?(column1,column2,...)
VALUES?(?value1,value2,?...)
說明：
1.若沒有指定column?系統則會按表格內的欄位順序填入資料。
2.欄位的資料形態和所填入的資料必須吻合。
3.table_name?也可以是景觀?view_name。
INSERT?INTO?table_name?(column1,column2,...)
SELECT?columnx,columny,...?FROM?another_table
說明：也可以經過一個子查詢（subquery）把別的表格的資料填入。
２、查詢資料：
基本查詢
SELECT?column1,columns2,...
FROM?table_name
說明：把table_name?的特定欄位資料全部列出來
SELECT?*
FROM?table_name
WHERE?column1?=?xxx?
[AND?column2?>?yyy]?[OR?column3?<>?zzz]
說明：
1.'*'表示全部的欄位都列出來。
2.WHERE?之後是接條件式，把符合條件的資料列出來。
SELECT?column1,column2
FROM?table_name
ORDER?BY?column2?[DESC]
說明：ORDER?BY?是指定以某個欄位做排序，[DESC]是指從大到小排列，若沒有指明，則是從小到大
排列
組合查詢
組合查詢是指所查詢得資料來源并不只有單一的表格，而是聯合一個以上的
表格才能夠得到結果的。
SELECT?*
FROM?table1,table2
WHERE?table1.colum1=table2.column1
說明：
1.查詢兩個表格中其中?column1?值相同的資料。
2.當然兩個表格相互比較的欄位，其資料形態必須相同。
3.一個復雜的查詢其動用到的表格可能會很多個。
整合性的查詢：
SELECT?COUNT?(*)?
FROM?table_name
WHERE?column_name?=?xxx
說明：
查詢符合條件的資料共有幾筆。
SELECT?SUM(column1)
FROM?table_name
說明：
1.計算出總和，所選的欄位必須是可數的數字形態。
2.除此以外還有?AVG()?是計算平均、MAX()、MIN()計算最大最小值的整合性查詢。
SELECT?column1,AVG(column2)
FROM?table_name
GROUP?BY?column1
HAVING?AVG(column2)?>?xxx
說明：
1.GROUP?BY:?以column1?為一組計算?column2?的平均值必須和?AVG、SUM等整合性查詢的關鍵字
一起使用。?
2.HAVING?:?必須和?GROUP?BY?一起使用作為整合性的限制。
復合性的查詢
SELECT?*
FROM?table_name1
WHERE?EXISTS?(
SELECT?*
FROM?table_name2
WHERE?conditions?)
說明：
1.WHERE?的?conditions?可以是另外一個的?query。
2.EXISTS?在此是指存在與否。
SELECT?*
FROM?table_name1
WHERE?column1?IN?(
SELECT?column1?
FROM?table_name2
WHERE?conditions?)
說明：　
1.?IN?後面接的是一個集合，表示column1?存在集合里面。
2.?SELECT?出來的資料形態必須符合?column1。?
其他查詢
SELECT?*
FROM?table_name1
WHERE?column1?LIKE?'x%'?
說明：LIKE?必須和後面的'x%'?相呼應表示以?x為開頭的字串。
SELECT?*
FROM?table_name1
WHERE?column1?IN?('xxx','yyy',..)
說明：IN?後面接的是一個集合，表示column1?存在集合里面。
SELECT?*
FROM?table_name1
WHERE?column1?BETWEEN?xx?AND?yy
說明：BETWEEN?表示?column1?的值介於?xx?和?yy?之間。?
３、更改資料：
UPDATE?table_name
SET?column1='xxx'
WHERE?conditoins
說明：
1.更改某個欄位設定其值為'xxx'。
2.conditions?是所要符合的條件、若沒有?WHERE?則整個?table?的那個欄位都會全部被更改。
４、刪除資料：
DELETE?FROM?table_name
WHERE?conditions
說明：刪除符合條件的資料。
說明：關于WHERE條件后面如果包含有日期的比較，不同數據庫有不同的表達式。具體如下：
(1)如果是ACCESS數據庫，則為：WHERE?mydate>#2000-01-01#?
(2)如果是ORACLE數據庫，則為：WHERE?mydate>cast('2000-01-01'?as?date)
或：WHERE?mydate>to_date('2000-01-01','yyyy-mm-dd')
在Delphi中寫成：
thedate='2000-01-01';
query1.SQL.add('select?*?from?abc?where?mydate>cast('+''''+thedate+''''+'?as?date)');?
如果比較日期時間型，則為：
WHERE?mydatetime>to_date('2000-01-01?10:00:01','yyyy-mm-dd?hh24:mi:ss')

轉載于:https://www.cnblogs.com/hugongs/archive/2006/12/02/579637.html

總結

以上是生活随笔為你收集整理的收集得最全的sql 语句的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。