一些SQL語句

在工作中收集了一些有用的語句

 

加密/解密

DECLARE @ClearPWD varchar(255)
dECLARE @EncryptedPWD varbinary(255)
SELECT @ClearPWD = 'test'
SELECT @EncryptedPWD = CONVERT(varbinary(255), pwdencrypt(@ClearPWD))
SELECT @EncryptedPWD

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)

 

自動產生流水號

select a.el_no as el_ono,a.cu_no,b.el_no
into vcuno_1
from vcuno a join ieel09h b on a.el_no=b.el_ono

select IDENTITY(int, 1,1) as ad_seq,a.el_no,b.cu_no
into vcuno_2
from ieel00h a join
(select a.*,b.bom_no
from vcuno_1 a join ieel00h b on a.el_no=b.el_no) b on a.bom_no=b.bom_no

insert into ieqc07d1 (ad_seq,el_no,su_no,su_no1)
select right(ad_seq+10000,4),el_no,cu_no,cu_no from vcuno_2

抓同一組最小(大)值的經典sql

select su_no, el_no,el_price from iesu04d4 A(nolock)
where not exists (select 1 from iesu04d4 (nolock) where el_no=A.el_no and el_price<A.el_price)

把表中的一列數據變成一行

declare @sql varchar(2000)
set @sql = ''
select @sql = @sql + ',' + name from syscolumns where id = object_id('iepb99h') and name <> 'ie_user'
select @sql
select @sql= stuff(@sql,1,1,'')
print('select '+@sql+' from 表名')

按漢字筆劃排序

Select * From iepb99h Order By er_remk Collate Chinese_PRC_Stroke_ci_as