SQL 和T-SQL學習(一)

1. 行列轉換--普通

假設有張學產生績表(CJ)如下
Name Subject Result
張三 語文 80
張三 數學 90
張三 物理 85
李四 語文 85
李四 數學 92
李四 物理 82

想變成
姓名 語文 數學 物理
張三 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)

2. 行列轉換--合并

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

建立一個合并的函數
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

--調用自訂函數得到結果
select distinct id,dbo.fmerg(id) from 表A

3. 如何取得一個資料表的所有列名

方法如下：先從SYSTEMOBJECT系統資料表中取得資料表的SYSTEMID,然後再SYSCOLUMN表中取得該資料表的所有列名。
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

是不是太簡單了？ 呵呵 不過經常用阿.

4. 通過SQL語句來更改使用者的密碼

修改別人的,需要sysadmin role
EXEC sp_password NULL, 'newpassword', 'User'

如果帳號為SA執行EXEC sp_password NULL, 'newpassword', sa

5. 怎麼判斷出一個表的哪些欄位不允許為空白？

select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where IS_NULLABLE='NO' and TABLE_NAME=tablename

6. 如何在資料庫裡找到含有相同欄位的表？
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. 未知列名查所有在不同表出現過的列名
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
 )

7. 查詢第xxx行資料

假設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)
 
如果使用遊標也是可以的
fetch absolute [number] from [cursor_name]
行數為絕對行數

8. SQL Server日期計算
a. 一個月的第一天
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. 上個月的最後一天
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. 本月的第一個星期一
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))。

CREATE Procedure CMRC_ProductSearch
(
    @Search nvarchar(255)
)
AS

SELECT
    ProductID,
    ModelName,
    ModelNumber,
    UnitCost,
    ProductImage

FROM
    CMRC_Products

WHERE
    ModelNumber LIKE '%' + @Search + '%'
   OR
    ModelName LIKE '%' + @Search + '%'
   OR
    Description LIKE '%' + @Search + '%'

GO