Home >Database >Mysql Tutorial >ms sqlserver常用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
-- 自定义函数递归调用 此方法适用于无限级分类情况下取出所有的父分类数据 CREATE function sp_GetAllParentByClassID declare @ParentClassID varchar(15) --变量父ID if (@ParentClassID 0)--如果不是根节点 有的SQL版本运行上述SQL语句在dbo.sp_GetAllParentByClassID(@ParentClassID)会报错。
-- 获取表主外键约束
exec sp_helpconstraint '你的表名'
(
@classID int --参数
)
returns varchar(500)
as
begin
declare @result varchar(500) --变量@result
set @result = ''
--首先根据传入的ClassID获取其父ID,ParentClassID
SELECT @ParentClassID = ParentClassID FROM tClass
where ClassID = @classID
begin
-- 再将@ParentClassID父ID作为ClassID传入 进行自调用
set @result = dbo.sp_GetAllParentByClassID(@ParentClassID) + @ParentClassID+'_'
end
return @result
end
原因是,此时正的创建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表示左边的值是一个统计值,是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
--------------------------