-- 获取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表示左边的值是一个统计值，是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
--------------------------