常用SQL
--循环使用
declare @count int
declare @row int
select @row=1
while @row<=100
begin
select @count=ISNULL(@count,0)+@row
select @row=@row+1
end
print @count
--SQL新特性,XML字段操作
declare @myDoc xml
set @myDoc = '<Root>
<ProductDescription ProductID="1" ProductName="Road Bike">
<Features>
<Warranty>1 year parts and labor</Warranty>
<Maintenance>3 year parts and labor extended maintenance is available</Maintenance>
</Features>
</ProductDescription>
</Root>'
SELECT @myDoc.query('/Root/ProductDescription/Features')
--SQL日期的操作
select convert(datetime,convert(varchar(12),DATEADD ( day , -1, SYSDATETIME() ),112)) --昨天日期
DECLARE @myval decimal (5, 2)
SET @myval = 193.57
SELECT CAST(CAST(@myval AS varbinary(20)) AS decimal(10,5))
-- Or, using CONVERT
SELECT CONVERT(decimal(10,5), CONVERT(varbinary(20), @myval))
--汇总分析统计
select ArrangeID,PaperID,
(sum(case when TxID = 1 then PgScore else 0 end)+
sum(case when TxID = 2 then PgScore else 0 end)+
sum(case when TxID = 3 then PgScore else 0 end)+
sum(case when TxID = 4 then PgScore else 0 end)+
sum(case when TxID = 6 then PgScore else 0 end)+
sum(case when TxID = 7 then PgScore else 0 end)+
sum(case when TxID = 8 then PgScore else 0 end)+
sum(case when TxID = 9 then PgScore else 0 end)) as ExamScore
from tb_Teach_Test_Answer_13 where status = 1 group by ArrangeID,PaperID
--相关表
select* FROM [DigitalSchool].[dbo].[Tb_Base_StuInfor]
--随机
Select top 5 * From tb_Tch_Keli_13 order By NewID()
--查询出Bas_name重复的所有数据
select * from [DigitalSchool].[dbo].[Tb_Base_StuInfor] a where (a.Bas_name)
in (select Bas_name from [DigitalSchool].[dbo].[Tb_Base_StuInfor] group by Bas_name having count(*) > 1)
--查出重复次数和(表中不存条件字段,使用having)
select count(Bas_name) as '重复次数',Bas_name from [DigitalSchool].[dbo].[Tb_Base_StuInfor]
group by Bas_name having count(*)>1 order by Bas_name desc