1.把某個(gè)字段重新生氣序列(從1到n):
DECLARE @i int
Set @i = 0
Update Table1 Set @i = @i + 1,Field1 = @i
2.按成績(jī)排名次
Update 成績(jī)表
Set a.名次 = (
Select Count(*) + 1
From 成績(jī)表 b
Where a.總成績(jī) < b.總成績(jī)
)
From 成績(jī)表 a
3.查詢外部數(shù)據(jù)庫
Select a.*
From OpenRowSet('Microsoft.Jet.OLEDB.4.0','c:\test.mdb';'admin';'',Table1) a
4.查詢Excel文件
Select *
From OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source="c:\test.xls";User ID=Admin;Password=;Extended properties=Excel 8.0')...Sheet1$
5.在查詢中指定排序規(guī)則
Select * From Table1 Order By Field1 COLLATE Chinese_PRC_BIN
為什么要指定排序規(guī)則呢?參見:
http://www.delphibbs.com/delphibbs/dispq.asp?lid=1633985
例,檢查數(shù)據(jù)庫中的Pub_Users表中是否存在指定的用戶:
Select Count(*) From Pub_Users Where [UserName]='admin' And [PassWord]='aaa' COLLATE Chinese_PRC_BIN
默認(rèn)比較是不區(qū)分大小寫的,如果不加COLLATE Chinese_PRC_BIN,那么密碼aaa與AAA是等效的,這當(dāng)然與實(shí)際不符.注意的是,每個(gè)條件都要指定排序規(guī)則,上例中用戶名就不區(qū)分大小寫.
6.Order By的一個(gè)小技巧
Order By可以指定列序而不用指定列名,在下面的例子里說明它的用處(注意,第三列未指定別名)
Select a.ID,a.Name,(Select Count(*) From TableB b Where a.ID=b.PID) From TableA a Order By 3
待續(xù)...
2003-10-6 13:53:00
發(fā)表評(píng)語»»»
2003-12-6 19:36:00 7.字符串之Sum???例如，有個(gè)表
ID NAME
------------------
1 T
2 H
3 A
4 N
5 K
要得到
THANK
declare @s varchar(100);
set @s='';
select @s=@s+[Name] from 表 order by id;
select @s;
http://www.delphibbs.com/delphibbs/dispq.asp?lid=2290831
2005-10-10 12:56:05 再來一個(gè)排名的,沒有排名字段查詢排名create table t1
(khid varchar(10), xsje money)
insert into t1 values ('001',100)
insert into t1 values ('002',105)
insert into t1 values ('003',220)
insert into t1 values ('004',89)
insert into t1 values ('001',150)
insert into t1 values ('002',50)
insert into t1 values ('003',38)
select (
select count(*) + 1 from (
select khid,sum(xsje) as xsje from t1 group by khid
) b where a.xsje < b.xsje
) mc,* from (
select khid,sum(xsje) as xsje from t1 group by khid
) a order by mc
優(yōu)點(diǎn):允許并列排名,純查詢
缺點(diǎn):要做兩次sum掃描源表
問題見:http://www.delphibbs.com/delphibbs/dispq.asp?lid=3231593
修改一下以適應(yīng)于ACCESS
select (
select count(*) + 1 from (
select khid,sum(xsje) as xsje_sum from t1 group by khid
) as b where a.xsje_sum < b.xsje_sum
) as mc,* from (
select khid,sum(xsje) as xsje_sum from t1 group by khid
) as a order by xsje_sum desc
1. 行列轉(zhuǎn)換
假設(shè)有張學(xué)生成績(jī)表(CJ)如下
Name Subject Result
張三 語文 80
張三 數(shù)學(xué) 90
張三 物理 85
李四 語文 85
李四 數(shù)學(xué) 92
李四 物理 82
想變成
姓名 語文 數(shù)學(xué) 物理
張三 80 90 85
李四 85 92 82
解決如下
declare @sql varchar(4000)
set @sql = 'select Name'
select @sql = @sql + ',sum(case Subject when '''+Subject+''' then Result end) ['+Subject+']'
from (select distinct Subject from CJ) as a
select @sql = @sql+' from test group by name'
exec(@sql)
另外在Access中還提供了TransForm來實(shí)現(xiàn)行列轉(zhuǎn)換
TRANSFORM count(Result) AS number
SELECT 姓名
FROM 學(xué)生成績(jī)表
GROUP BY 姓名
PIVOT Subject;
TransForm 用法如下:
=========================================================
TRANSFORM aggfunction
selectstatement
PIVOT pivotfield [IN (value1[, value2[, ...>)]
TRANSFORM 語句可分為以下幾個(gè)部分：
部分 描述
aggfunction 在選定數(shù)據(jù)上運(yùn)作的 SQL 合計(jì)函數(shù)。
selectstatement SELECT 語句。
pivotfield 在查詢的結(jié)果集中創(chuàng)建列標(biāo)題時(shí)用的字段或表達(dá)式。
value1, value2 用來創(chuàng)建列標(biāo)題的固定值。
說明
使用交叉表查詢來摘要數(shù)據(jù)時(shí)，從指定的字段或表達(dá)式中選定值作為列標(biāo)題，
這樣，可以用比選定查詢更緊湊的格式來觀察數(shù)據(jù)。
TRANSFORM 是可選的，但在使用它時(shí)，要作為 SQL 字符串中的第一個(gè)語句。
它出現(xiàn)在 SELECT 語句（指定作為行標(biāo)題的字段的）之前，還出現(xiàn)在 GROUP BY 子句
（指定行分組的）之前?？梢杂羞x擇地包含其它子句，例如 WHERE 子句，它指定附
加的選擇或排序條件。也可以將子查詢當(dāng)作謂詞，特別是在叉表查詢的 WHERE 子句中。
pivotfield 返回的值被用作查詢結(jié)果集中的列標(biāo)題。
例如，在交叉表查詢中，將根據(jù)銷售圖表按銷售月份創(chuàng)建 12 個(gè)列。
可以限制 pivotfield 用列在可選的 IN 子句中的固定值（value1, value2）來創(chuàng)建標(biāo)題。
也可以用沒有數(shù)據(jù)存在的固定值來創(chuàng)建附加的列。
==================================================================================
2. 列行轉(zhuǎn)換
暫時(shí)保留
3. 行列轉(zhuǎn)換--加合并
有表A,
id pid
1 1
1 2
1 3
2 1
2 2
3 1
如何化成表B:
id pid
1 1,2,3
2 1,2
3 1
創(chuàng)建一個(gè)合并的函數(shù)
create function fmerg(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+','+cast(pid as varchar) from 表A where id=@id
set @str=right(@str,len(@str)-1)
return(@str)
End
go
--調(diào)用自定義函數(shù)得到結(jié)果
select distinct id,dbo.fmerg(id) from 表A
4. 如何取得一個(gè)數(shù)據(jù)表的所有列名
方法如下：先從SYSTEMOBJECT系統(tǒng)表中取得數(shù)據(jù)表的SYSTEMID,然后再SYSCOLUMN表中取得該數(shù)據(jù)表的所有列名。
SQL語句如下：
declare @objid int,@objname char(40)
set @objname = 'tablename'
select @objid = id from sysobjects where id = object_id(@objname)
select 'Column_name' = name from syscolumns where id = @objid order by colid
是不是太簡(jiǎn)單了？ 呵呵 不過經(jīng)常用阿.
5. 通過SQL語句來更改用戶的密碼
修改別人的,需要sysadmin role
EXEC sp_password NULL, 'newpassword', 'User'
如果賬號(hào)為SA執(zhí)行EXEC sp_password NULL, 'newpassword', sa
6.怎么判斷出一個(gè)表的哪些字段不允許為空？
select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where IS_NULLABLE='NO' and TABLE_NAME=tablename
7.如何在數(shù)據(jù)庫里找到含有相同字段的表？
a. 查已知列名的情況
SELECT b.name as TableName,a.name as columnname
From syscolumns a INNER JOIN sysobjects b
ON a.id=b.id
AND b.type='U'
AND a.name='你的字段名字'
b. 未知列名查所有在不同表出現(xiàn)過的列名
Select o.name As tablename,s1.name As columnname
From syscolumns s1, sysobjects o
Where s1.id = o.id
And o.type = 'U'
And Exists (
Select 1 From syscolumns s2
Where s1.name = s2.name
And s1.id <> s2.id
)
8.查詢第xxx行數(shù)據(jù)
假設(shè)id是主鍵：
select *
from (select top xxx * from yourtable) aa
where not exists(select 1 from (select top xxx-1 * from yourtable) bb where aa.id=bb.id)
如果使用游標(biāo)也是可以的
fetch absolute [number] from [cursor_name]
行數(shù)為絕對(duì)行數(shù)
9.SQL Server日期計(jì)算
a. 一個(gè)月的第一天
SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)
b. 本周的星期一
SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)
c. 一年的第一天
SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
d. 季度的第一天
SELECT DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)
e. 上個(gè)月的最后一天
SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))
f. 去年的最后一天
SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))
g. 本月的最后一天
SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))
h. 本月的第一個(gè)星期一
select DATEADD(wk, DATEDIFF(wk,0,
dateadd(dd,6-datepart(day,getdate()),getdate())
), 0)
i. 本年的最后一天
SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0))。
聯(lián)系客服
