--向表中插入一条记录,然后返回刚插入的这条记录的主键tClassId的值。在Ado.Net中通过ExecuteScalar方法执行该SQL
insert into TblClass(tClassName,tClassDesc) output inserted.tClassId values('黑12','nnnnnnn')
select * from dbo.BBSUsers
select name,等级=(
case level
when 1 then '大虾'
when 2 then '女神'
when 3 then '菜鸟'
else '黑马'
end
)
from BBSUsers
select * from dbo.TblScore
------区间判断-----------
select tSId,数学成绩=(
case
when tMath>=90 then '优秀'
when tMath>=80 then '良好'
when tMath>=60 then '及格'
else '继续努力'
end
)
from TblScore
-----------------------------------------------
--在订单表中,统计每个销售员的总销售金额,列出销售员名、总销售金额、称号(>=6000金牌,>=5500银牌,>=4500铜牌,否则普通)
select * from MyOrders
select 销售员,销售金额=SUM(销售数量*销售价格),称号=(
case
when SUM(销售数量*销售价格)>=6000 then '金牌'
when SUM(销售数量*销售价格)>=5500 then '银牌'
when SUM(销售数量*销售价格)>=4500 then '铜牌'
else '普通'
end
)
from MyOrders group by 销售员
---------------------------
select * from casetest1
-- 单号 收入 支出
--Rk1 10 0
--Rk2 20 0
--Rk3 0 30
--Rk4 0 10
select number,收入=(
case
when amount>0 then amount
else 0
end
),
支出=(
case
when amount<0 then abs(amount)
else 0
end
) from casetest1
----------------------------------------------
select * from dbo.Aspx_Users
select * from dbo.TestIndex1002
select c3,c4 from TestIndex1002 where c4>800 and c4<1000 order by c4 asc
select c3 from TestIndex1002 where c3='abitpos'
drop Index TestIndex1002.IXc3
--创建聚集索引
create clustered index IXc4 on TestIndex1002(c4)
--非聚集索引
create nonclustered index IXc3 on TestIndex1002(c3)
CREATE NONCLUSTERED INDEX [_dta_index_TestIndex1002_8_821577965__K4_3] ON [dbo].[TestIndex1002]
(
[c4] ASC
)
INCLUDE ( [c3]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
insert into TblClass(tClassName,tClassDesc) output inserted.tClassId values('黑12','nnnnnnn')
select * from dbo.BBSUsers
select name,等级=(
case level
when 1 then '大虾'
when 2 then '女神'
when 3 then '菜鸟'
else '黑马'
end
)
from BBSUsers
select * from dbo.TblScore
------区间判断-----------
select tSId,数学成绩=(
case
when tMath>=90 then '优秀'
when tMath>=80 then '良好'
when tMath>=60 then '及格'
else '继续努力'
end
)
from TblScore
-----------------------------------------------
--在订单表中,统计每个销售员的总销售金额,列出销售员名、总销售金额、称号(>=6000金牌,>=5500银牌,>=4500铜牌,否则普通)
select * from MyOrders
select 销售员,销售金额=SUM(销售数量*销售价格),称号=(
case
when SUM(销售数量*销售价格)>=6000 then '金牌'
when SUM(销售数量*销售价格)>=5500 then '银牌'
when SUM(销售数量*销售价格)>=4500 then '铜牌'
else '普通'
end
)
from MyOrders group by 销售员
---------------------------
select * from casetest1
-- 单号 收入 支出
--Rk1 10 0
--Rk2 20 0
--Rk3 0 30
--Rk4 0 10
select number,收入=(
case
when amount>0 then amount
else 0
end
),
支出=(
case
when amount<0 then abs(amount)
else 0
end
) from casetest1
----------------------------------------------
select * from dbo.Aspx_Users
select * from dbo.TestIndex1002
select c3,c4 from TestIndex1002 where c4>800 and c4<1000 order by c4 asc
select c3 from TestIndex1002 where c3='abitpos'
drop Index TestIndex1002.IXc3
--创建聚集索引
create clustered index IXc4 on TestIndex1002(c4)
--非聚集索引
create nonclustered index IXc3 on TestIndex1002(c3)
CREATE NONCLUSTERED INDEX [_dta_index_TestIndex1002_8_821577965__K4_3] ON [dbo].[TestIndex1002]
(
[c4] ASC
)
INCLUDE ( [c3]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]