SELECT
[
ID
]
, [ ReportID ]
, [ ReportSort ]
, [ ReportCondition ]
, [ InsertTime ]
,ReportCondition.query( ' /root/UserName ' ).value( ' UserName[1] ' , ' varchar(50) ' )
FROM [ PSYDB_TestReport ] . [ dbo ] . [ PSYT_TS_ReportSort ]
WHERE ReportCondition.query( ' /root/UserName ' )
.value( ' UserName[1] ' , ' varchar(50) ' )
= ' mengwei1 '
, [ ReportID ]
, [ ReportSort ]
, [ ReportCondition ]
, [ InsertTime ]
,ReportCondition.query( ' /root/UserName ' ).value( ' UserName[1] ' , ' varchar(50) ' )
FROM [ PSYDB_TestReport ] . [ dbo ] . [ PSYT_TS_ReportSort ]
WHERE ReportCondition.query( ' /root/UserName ' )
.value( ' UserName[1] ' , ' varchar(50) ' )
= ' mengwei1 '
SELECT
[
ID
]
, [ ReportID ]
, [ ReportSort ]
, [ ReportCondition ]
, [ ReportXml ]
, [ InsertTime ]
FROM [ PSYDB_TestReport ] . [ dbo ] . [ PSYT_TS_ReportSort ]
WHERE Convert ( varchar ( 50 ), [ ReportCondition ] .query( ' /root/SystemID/text() ' )) = ' SEMS '
AND Convert ( varchar ( 50 ), [ ReportCondition ] .query( ' /root/ScaleNum/text() ' )) LIKE ' SEMSZz% '
获
取
对
象的属性
, [ ReportID ]
, [ ReportSort ]
, [ ReportCondition ]
, [ ReportXml ]
, [ InsertTime ]
FROM [ PSYDB_TestReport ] . [ dbo ] . [ PSYT_TS_ReportSort ]
WHERE Convert ( varchar ( 50 ), [ ReportCondition ] .query( ' /root/SystemID/text() ' )) = ' SEMS '
AND Convert ( varchar ( 50 ), [ ReportCondition ] .query( ' /root/ScaleNum/text() ' )) LIKE ' SEMSZz% '
select
xcol.value (
''
(
/
book
/
@genre
)
[
1
]''
,
''
varchar
(
50
)
''
),
xcol.value ( '' ( / book / title) [ 1 ]'' , '' varchar ( 50 ) '' ),
xcol.value ( '' ( / book / @isbn ) [ 1 ]'' , '' varchar ( 50 ) '' )
from t
修改Xml字段中的节点
xcol.value ( '' ( / book / title) [ 1 ]'' , '' varchar ( 50 ) '' ),
xcol.value ( '' ( / book / @isbn ) [ 1 ]'' , '' varchar ( 50 ) '' )
from t
Select
[
ReportCondition
]
From
[
PSYDB_TestReport
]
.
[
dbo
]
.
[
PSYT_TS_ReportSort
]
Where
[
ReportID
]
=
3554
UPDATE [ PSYDB_TestReport ] . [ dbo ] . [ PSYT_TS_ReportSort ]
SET [ ReportCondition ] .modify( ' replace value of (/root/Duration/text())[1] with "15" ' )
Where [ ReportID ] = 3554
Select [ ReportCondition ] From [ PSYDB_TestReport ] . [ dbo ] . [ PSYT_TS_ReportSort ] Where [ ReportID ] = 3554
SQL 获取年月日方法
UPDATE [ PSYDB_TestReport ] . [ dbo ] . [ PSYT_TS_ReportSort ]
SET [ ReportCondition ] .modify( ' replace value of (/root/Duration/text())[1] with "15" ' )
Where [ ReportID ] = 3554
Select [ ReportCondition ] From [ PSYDB_TestReport ] . [ dbo ] . [ PSYT_TS_ReportSort ] Where [ ReportID ] = 3554
SELECT
DATEPART
(
year
,
GETDATE
()),
DATEPART
(
month
,
GETDATE
()),
DATEPART
(
day
,
GETDATE
())
SELECT year ( GETDATE ()), month ( GETDATE ()), day ( GETDATE ())
SELECT year ( GETDATE ()), month ( GETDATE ()), day ( GETDATE ())
求时间差的函数:
DATEDIFF ( datepart , startdate , enddate )
enddate 减去 startdate。如果 startdate 晚于 enddate,则返回负值。
datepart 指定应在日期的哪一部分计算差额的参数。下表列出了 Microsoft SQL Server 2005 可识别的日期部分及其缩写。
日期部分 | 缩写 |
---|---|
year | yy, yyyy |
quarter | qq, q |
month | mm, m |
dayofyear | dy, y |
day | dd, d |
week | wk, ww |
Hour | hh |
minute | mi, n |
second | ss, s |
millisecond | ms |
示例 删除添加时间为空或添加时间超过5小时的记录
Delete
From
DoExamTable
Where
InsertTime
IS
NULL
OR
DATEDIFF
(hh,InsertTime,
GETDATE
())
>
5
--
--分割字符串
-- 创建函数
create function split
(
@SourceSql varchar ( 8000 ),
@StrSeprate varchar ( 10 )
)
returns @temp table (F1 varchar ( 100 ))
as
begin
declare @i int
set @SourceSql = rtrim ( ltrim ( @SourceSql ))
set @i = charindex ( @StrSeprate , @SourceSql )
while @i >= 1
begin
insert @temp values ( left ( @SourceSql , @i - 1 ))
set @SourceSql = substring ( @SourceSql , @i + 1 , len ( @SourceSql ) - @i )
set @i = charindex ( @StrSeprate , @SourceSql )
end
if @SourceSql <> ''
insert @temp values ( @SourceSql )
return
end
-- 执行
select * from split( ' 01_02_03 ' , ' _ ' )
-- 创建函数
create function split
(
@SourceSql varchar ( 8000 ),
@StrSeprate varchar ( 10 )
)
returns @temp table (F1 varchar ( 100 ))
as
begin
declare @i int
set @SourceSql = rtrim ( ltrim ( @SourceSql ))
set @i = charindex ( @StrSeprate , @SourceSql )
while @i >= 1
begin
insert @temp values ( left ( @SourceSql , @i - 1 ))
set @SourceSql = substring ( @SourceSql , @i + 1 , len ( @SourceSql ) - @i )
set @i = charindex ( @StrSeprate , @SourceSql )
end
if @SourceSql <> ''
insert @temp values ( @SourceSql )
return
end
-- 执行
select * from split( ' 01_02_03 ' , ' _ ' )
几个sql语句的使用
where 与 having 的区别
SQLServer2005的XML最佳实施策略
SQL Server 2005的XML数据修改语言(XML DML)
一步一步优化SQL语句(一):逻辑查询的各个阶段
一步一步优化SQL语句(二):物理查询处理
一道 SQL 面试题
关于 T-SQL 的几点小九九 (1)
SQL SERVER2005 分区表几何倍数提高网站性能
SQL函数大全
Inside T-SQL XML (一)XML数据类型