工作一年,做NET的开发,做过两三个项目,C/S,B/S架构的都做过.
解决方案 »
- 请大家帮我扫盲,C/S应用该如何做
- txt导入sql时汉字乱码
- 假设图片是一个流程图,如何能够判断用户是否单击图片中的按钮?
- sqlserver2005下使用存储过程,为何ExecuteNonQuery()总是返回-1
- 大伙儿来看看!!用C#时自动生成了好多文件呀,下面这些文件都起些什么作用?
- 关于c#操作sql时用parameter的问题?在线等啊,不然周末没休息啊
- 如何对一个datatable进行检索啊
- 【江湖救急,希望老大出手~~~~~】
- 新手请教:C# 关于自定义类的使用,谁能告诉我,关键字的含义及语句的使用目的?
- 请教!100分
- 偶尔出现 “无法找到表 1” 的错误 不知是为什么?
- 跨页面控制控件的属性
go
use student
go
create table tb_student --create table
(
sname varchar(20),
object varchar (20),
score int
)
go
insert tb_student select 'a','EN',89 union all --add data to table
select 'a','CH',78 union all
select 'a','HO',99 union all
select 'b','EN',34 union all
select 'b','CH',88 union all
select 'b','HO',0
go
select * from tb_student --view data sourse
go
declare @sql varchar(8000)
set @sql = 'select sname'
select @sql = @sql + ',sum(case object when ''' + object + ''' then score else '''' end) ['+ object+']'
from (select distinct object from tb_student) as t
select @sql = @sql + 'from tb_student group by sname'
exec (@sql)--清理数据
if db_ID('student') IS NOT NULL
begin
use master
drop database student
end
select distinct * into #temp from tab
truncate table tab
insert tab select * from #temp
drop table #temp2.DISTINCT 是 SUM、AVG 和 COUNT 的可选关键字。如果使用 DISTINCT,那么在计算总和、平均值或计数之前,先消除重复的值。如果使用 DISTINCT 关键字,表达式必须只包含列名。而不能包含算术表达式。
以下查询返回商务书籍的平均价格(不包括重复的值):
USE pubs
SELECT AVG(DISTINCT price)
FROM titles
WHERE type = 'business'3.DISTINCT 关键字可从 SELECT 语句的结果中除去重复的行,distinct 后面的字段可以是多个或*,是一个那就各军兵种那个字段来取不重复的,如果是多个,那就是筛选所选的字短都相同的记录.
USE pubs
SELECT DISTINCT au_id--按照一个字段筛选
FROM titleauthorUSE pubs
SELECT DISTINCT au_id,au_name --按照两个字段筛选
FROM titleauthor4.怎样返回数据库中用户表的表单名
select name from sysobjects where xtype='U'
select name from sysobjects where xtype = 'u' and status >=05.一个表中A字段是int型的自动编号,B字段是首先要获取A字段已有的自动编号数据再经过加入时间等后生成的数据,表如下C,D(日期),E为其他数据
列名 A(自动递加) B(A字段数据+日期等) C D E
---------------------------------------------------
1 A+D . . .
2 A+D . . .---- 建立测试环境:
create table table1(a int identity,b varchar(20),c datetime,d datetime,e int)create proc proc1
@c datetime,
@d datetime,
@e int
as
declare @f int
insert table1 (c,d,e) values(@c,@d,@e)
select @f=@@identity
if @@error=0
begin
update table1 set b=convert(varchar,a)+convert(varchar(12),d,120) where a=@f
end---执行存储过程
exec proc1 '2001-10-01','2001-10-20',45select * from table1内联结/外联结
--返回两个表中共有的所有记录
select *
from testTable as a
inner join TestTableChild as b on a.id = b.parentid--返回(左表)TestTable所有记录
select *
from testTable as a
left outer join TestTableChild as b on a.id = b.parentid--返回(右表)TestTableChild的所有记录
select *
from testTable as a
right outer join TestTableChild as b on a.id = b.parentid--- 返回 两个表里共有的记录,且不重复
select a.id,a.name,b.name
from testTable as a
inner join TestTableChild as b on a.id = b.parentid
group by a.id,a.name,b.name--返回(左表)TestTable所有记录
select a.id,a.name,b.name
from testTable as a
left outer join TestTableChild as b on a.id = b.parentid
group by a.id,a.name,b.name---判断临时表是否存在
if exists(select 1 from tempdb..sysobjects where id=object_id('tempdb..#表名') and xtype='U')
drop table #表名--字段值为NULL转化为0,多看系统方法
isnull(字段,0)--table1和talbe2交叉联接的结果集再和table3左联接
select a.*,c.others from
(select a.id,a.name,b.re
from table1 a,table2 b) a
left join table3 c on a.id = c.parentid随机数自定义方法
--通过View来获得随即数字.在方法里直接使用rand()不可以的.
create view uv_GetRandom
as
select rand() as RandomValue
go
--生成n位随机数的方法
--select locationServiceNew.dbo.getRandom(10)
--比较郁闷..好麻烦
alter function GetRandom(@nLength int=4)
returns int
as
begin
declare @nStart int,@vchLength varchar(50)
set @nstart = 1
set @vchLength = '1'
if @nLength >9 set @nLength = 9 --如果随机数位数大于9那么将其修改为9
if @nLength <1 set @nLength = 1 --如果随机数位数小于1那么将其修改为1
while @nStart <= @nLength
begin
set @vchLength = @vchLength+'0'
set @nStart = @nStart+1
end declare @floatNum float,@intNum int
--set @num=cast(left(rand(),8) as float)*1000000
select @floatNum = RandomValue from uv_GetRandom
set @intNum = cast(@floatNum*cast(@vchLength as int) as int)
return (@intNum)
end
go--测试
select locationServiceNew.dbo.getRandom(7)用户定义函数中不允许使用会对每个调用返回不同数据的内置函数
其中就有getdate哦
用户定义函数中不允许使用以下内置函数:@@CONNECTIONS
@@PACK_SENT
GETDATE
@@CPU_BUSY
@@PACKET_ERRORS
GetUTCDate
@@IDLE
@@TIMETICKS
NEWID
@@IO_BUSY
@@TOTAL_ERRORS
RAND
@@MAX_CONNECTIONS
@@TOTAL_READ
TEXTPTR
@@PACK_RECEIVED
@@TOTAL_WRITE
--创建指定位的随即数
create proc up_GetRandom
@nLength int = 4, --随即数的位数.
@vchValue int output
as
declare @nStart int,@vchLength varchar(50)
set @nstart = 1
set @vchLength = '1'
if @nLength >9 set @nLength = 9 --如果随机数位数大于9那么将其修改为9
if @nLength <1 set @nLength = 1 --如果随机数位数小于1那么将其修改为1
while @nStart <= @nLength
begin
set @vchLength = @vchLength+'0'
set @nStart = @nStart+1
end set @vchValue= cast(rand()*cast(@vchLength as int) as int)
go
--测试
declare @randomValue int
Exec up_getRandom 8,@randomValue output
select @randomValue