華閱文章網編寫T-SQL語句,定義規則Email-rule,限制字符串中必
ifobject_id('fCheckEmail')isnotnulldropfunctionfCheckEmailGO----創建驗證函數，返回值為1表示正確，否則格式錯誤createfunctionfCheckEmail(@Emailvarchar(1000)) ('')>0or/*含有空格*/len(@email)-len(replace(@email,'。
',''))>1or/*'。'超過1個*/len(@email)-len(replace(@email,'@',''))>1or/*'@'超過1個*/right(@email,1)='。
'or/*以'。'結尾*/right(@email,1)='@'or/*以'@'結尾*/left(@email,1)='。
'or/*以'。'開頭*/left(@email,1)='@'or/*以'。
'開頭*/charindex('。')-charindex（'@') '相鄰*/charindex('。
')-charindex('@')=1/*'@'與'。'相鄰*/ /*僅供參考，可以靈活添加\刪除或修改某些條件*/。
SQL語句的編寫
1:
use master
go
if exists (select name from *ses where name = 'xuesheng')
drop database xuesheng
go
create database xuesheng
go
use xuesheng
--建立員工類型表
go
create table XS(
Id varchar(10) primary key,
Name varchar(10) not null,
Sex bit,
Age int
)
go
2:
insert into XS values('2008010001'，'馬麗'，false,20)
go
3:
update XS set Name = '馬小麗' where name = '馬麗'
go
4:
alter table XS ADD adress varchar(40),score decimal(4, 1)
go
5:
update XS set score = score * 1.05 where id = '200801001'
go
6:
select * from XS where score > 80
go
7:
select sum(score) from XS where score > 90
go
8:
delete XS from XS where id = '200810001'
go
9:
select * into XS_bk from XS
go
10:
drop table XS_bk
應該編寫什么樣的sql語句才能將下列數據篩選出來呢？我單位ora？ 愛
select * from table1 a where (field1,field2) in ( select field1,field2 from t_yqx_1 a where to_number(substr(field3,1,instr(field3,'/')-1))>=3 group by field1,field2 having count(*)>=2 ） 表名為table1，字段名為field1,field2,field3 修改一下： 我把表名字貼錯了，t_yqx_1是我使用的臨時表，應該換成table1 如果篩選的最小時間單位是小時，并且只提取這1個小時的數據，那么修改為： select * from table1 where (field1,to_char(field2,'yyyymmddhh24')) in ( select field1,to_char(field2,'yyyymmddhh24') from table1 where to_number(substr(field3,1,instr(field3,'/')-1))>=3 group by field1,to_char(field2,'yyyymmddhh24') having count(*)>=2 ） 樓主能否解釋一下“2。
然后將全天的數據挑出來”是什么意思？。
怎么編寫這個sql語句
select name ,sum(case 科目 when '語文' then 分數 end) as 語文，
sum(case 科目 when '數學' then 分數 end) as 數學，
sum(case 科目 when '英語' then 分數 end) as 英語
from 表
group by name
通用性的如下：
表student(name,course,score)
declare @sql varchar(8000)
begin
set @sql = 'select name as 姓名'
select @sql = @sql + ',SUM(case when course=''' + [course] + ''' then score end) as '+ course + ''
from Student group by name
set @sql = @sql + ',sum(score) as 總分 from student group by name'
end
exec(@sql)
用sql語言編寫程序
====
SELECT *
FROM za
SELECT *
FROM zb
SELECT za.學號， za.姓名， zb.分數 AS Basic, zb_1.分數 AS C語言， zb_2.分數 AS 數據庫， zb_3.分數 AS Java, (zb.分數 + zb_1.分數 + zb_2.分數 + zb_3.分數)
/ 4 AS 平均分
FROM za INNER JOIN
zb ON za.學號 = zb.學號 INNER JOIN
zb AS zb_1 ON za.學號 = zb_1.學號 INNER JOIN
zb AS zb_3 ON za.學號 = zb_3.學號 INNER JOIN
zb AS zb_2 ON za.學號 = zb_2.學號
WHERE (zb.課程 = 'BASIC') AND (zb_1.課程 = 'C語言') AND (zb_2.課程 = '數據庫') AND (zb_3.課程 = 'JAVA')
====
==========
除分數是INT外 其他全是 NVCHAR
