mssqlserver常用sql语句

-- 获取SqlServer中表结构 SELECT syscolumns.name,systypes.name,syscolumns.isnullable, syscolumns.length FROM syscolumns,systypes WHERE syscolumns.xusertype = systypes.xusertype AND syscolumns.id = object_id ( ' 你的表名 ' ) -- 单独查询表递

-- 获取SqlServer中表结构

SELECT syscolumns.name,systypes.name,syscolumns.isnullable,

syscolumns.length

FROM syscolumns, systypes

WHERE syscolumns.xusertype = systypes.xusertype

AND syscolumns.id =object_id('你的表名')

-- 单独查询表递增字段

select [name] from syscolumns where

id=object_id(N'你的表名') and COLUMNPROPERTY(id,name,'IsIdentity')=1

-- 获取表主外键约束

exec sp_helpconstraint '你的表名'

-- 自定义函数递归调用

此方法适用于无限级分类情况下取出所有的父分类数据

CREATE function sp_GetAllParentByClassID

(

@classID int --参数

)

returns varchar(500)

as

begin

declare @ParentClassID varchar(15) --变量父ID

declare @result varchar(500) --变量@result

set @result = ''

--首先根据传入的ClassID获取其父ID，ParentClassID

SELECT @ParentClassID = ParentClassID FROM tClass

where ClassID = @classID

if (@ParentClassID 0)--如果不是根节点

begin

-- 再将@ParentClassID父ID作为ClassID传入 进行自调用

set @result = dbo.sp_GetAllParentByClassID(@ParentClassID) + @ParentClassID+'_'

end

return @result

end

有的SQL版本运行上述SQL语句在dbo.sp_GetAllParentByClassID(@ParentClassID)会报错。

原因是，此时正的创建dbo.sp_GetAllParentByClassID函数，而还未创建，

在又在此处调用dbo.sp_GetAllParentByClassID(@ParentClassID)，因此会提示没有这个对象，

解决办法是先去掉dbo.sp_GetAllParentByClassID(@ParentClassID)创建后再Alter修改 即可！

-- 如何将exec执行结果放入变量 num 中(自定义sql语句传出参数)

declare @num int,

@sqls nvarchar(4000)

set @sqls='select @a=count(*) from tableName '

exec sp_executesql @sqls,N'@a int output',@num output

select @num

--------------统计类-------------

SELECT ZIP FROM CustomersWHEREState = ''KY'' GROUP BY ALL ZIP

SELECT ZIP FROM CustomersWHEREState = ''KY'' GROUP BY ZIP

SELECT ZIP, Count(ZIP) AS CustomersByZIP FROM Customers GROUP BY ZIP HAVING Count(ZIP) = 1

SELECT OrderID, Sum(Cost * Quantity) AS OrderTotal FROM Orders GROUP BY OrderID

SELECT Customer, OrderNumber, Sum(Cost * Quantity) AS OrderTotal FROM Orders GROUP BY Customer, OrderNumber WITH ROLLUP

SELECT Customer, OrderNumber, Sum(Cost * Quantity) AS OrderTotal FROM Orders GROUP BY Customer, OrderNumber WITH CUBE

-----------------------------------

当CUBE的结果令人迷惑时（它经常是这样），可以添加一个GROUPING函数，如下所示：

SELECT GROUPING(Customer), OrderNumber, Sum(Cost * Quantity) AS OrderTotal FROM Orders GROUP BY Customer, OrderNumber WITH CUBE

结果中每行包含两个额外的值：

值1表示左边的值是一个统计值mssql 语句快速参考，是ROLLUP或CUBE的操作符。

值0表示左边的值是一条由最初的GROUP BY语句产生的详细记录。

----------------

SELECT region, SUM(population), SUM(area)FROM bbc GROUP BY region HAVING SUM(area)>1000000

-------------------------------------

Select

课程名,

[成绩>=85]=SUM(Case When 成绩>=85 Then 1 Else 0 End) ,

[85>成绩>70]=SUM(Case When 成绩>=70 And 成绩 [成绩 总人数=Count(1)

from TableName

Group By 课程名

Order By 课程名

----------------分组分段统计-----------------------

select

t.fromaccountid,

onglogtimes=sum(case when t.logtimes1>0 then 1 else 0 end),

twologtimes=sum(case when t.logtimes2=2 then 1 else 0 end),

regnum=sum(t.regStatus),

t.regdate

from

(select

a.fromaccountid,

count(1) as logtimes1,--当日有登录记录的统计

sum(case when datediff(mm,regdate,datetime)=0 then 1 else 0 end) as logtimes2, --在注册当月登录的统计

max(case when datediff(dd,regdate,datetime)=0 then 1 else 0 end) as regStatus, --当日为注册操作的统计

convert(char(10),a.regdate,120) as regdate

from

vgameuser a,loginlog b

where

a.accountid=b.playerid

group by

a.fromaccountid,convert(char(10),a.regdate,120))t

group by

t.fromaccountid,t.regdate

--------------------------

（编辑：航空爱好网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!