//在用友表中自动添加物资分类的父代码
SELECT cInvCName, cInvCCode, ParentCode = (CASE WHEN len(cInvCCode)
= 3 THEN SubString(cInvCCode, 1, 2) WHEN len(cInvCCode)
= 2 THEN SubString(cInvCCode, 1, 1) ELSE '0' END) FROM [dbo].[InventoryClass]
SELECT cInvCName, cInvCCode, ParentCode = (CASE WHEN len(cInvCCode)
= 3 THEN SubString(cInvCCode, 1, 2) WHEN len(cInvCCode)
= 2 THEN SubString(cInvCCode, 1, 1) ELSE '0' END) FROM [dbo].[InventoryClass]
//我想让一字符字段的值如“1号”……“10号”,按前面的数值顺序排列,不考虑后面的字符,在SQL应该怎么写
select * from table1 order by convert(int,left(字符字段,charindex('号',字符字段)-1))
select * from table1 order by convert(int,left(字符字段,charindex('号',字符字段)-1))
select * from table order by substring(字段,0,charindex('号',字段))
//找空值,并找顺序字段
SELECT TOP 9 id, '' AS pro_myname, '' AS Expr1,
(SELECT COUNT(*)
FROM Buy_listall
WHERE id <= t.id) AS xh
FROM Buy_listall t
(如果无id字段):
SELECT xh = IDENTITY (int, 1, 1), dbo.productmateria.*
INTO #a
FROM dbo.productmateria
WHERE constractnumeric = 38
SELECT *
FROM #a
SELECT TOP 9 id, '' AS pro_myname, '' AS Expr1,
(SELECT COUNT(*)
FROM Buy_listall
WHERE id <= t.id) AS xh
FROM Buy_listall t
(如果无id字段):
SELECT xh = IDENTITY (int, 1, 1), dbo.productmateria.*
INTO #a
FROM dbo.productmateria
WHERE constractnumeric = 38
SELECT *
FROM #a
//
declare aa int
set aa=0
select aa=aa+1 AS ID_Num, a.* into #temp from uds_document a
//2字段包含关系可以用:速度很慢,最好慎用,考虙用其它替代,比如調整表結構...
1.select * from a,b where charindex(b.b,a.a)>0
2.select * from a,b where a.a like '%'+b.b+'%'
declare aa int
set aa=0
select aa=aa+1 AS ID_Num, a.* into #temp from uds_document a
//2字段包含关系可以用:速度很慢,最好慎用,考虙用其它替代,比如調整表結構...
1.select * from a,b where charindex(b.b,a.a)>0
2.select * from a,b where a.a like '%'+b.b+'%'
//--目的表不存在(db1:源库;tb1:源表;db2:目的库;tb2:目的表)
select * into db2..tb2 from db1..tb1
//--目的表存在
insert into db2..tb2 select * from db..tb1
select * into db2..tb2 from db1..tb1
//--目的表存在
insert into db2..tb2 select * from db..tb1
//联表的update
UPDATE a
SET a.ParentCode = b.ClassCode
FROM Docu_ContClas a, Docu_ContClas b
WHERE b.Classname = a.ParentCode
UPDATE a
SET a.ParentCode = b.ClassCode
FROM Docu_ContClas a, Docu_ContClas b
WHERE b.Classname = a.ParentCode
a表
id userid count_num
1 aa 1
1 bb 2
b表
id userid count_num
1 aa 2
2 aa 3
id userid count_num
1 aa 1
1 bb 2
b表
id userid count_num
1 aa 2
2 aa 3
update a set count_nun=abc.count_num
from a,(select max(count_num) as count_num,userid from b group by userid)abc where abc.userid=a.userid
from a,(select max(count_num) as count_num,userid from b group by userid)abc where abc.userid=a.userid
//SQL限定小数--try
--方法1:
round(字段,3) --后台可能看不出来显示,但前台调用的时候,就保留3位小数了
--方法2:
select convert(decimal(7,3),字段) from 表 --后台可以看到显示结果
--方法1:
round(字段,3) --后台可能看不出来显示,但前台调用的时候,就保留3位小数了
--方法2:
select convert(decimal(7,3),字段) from 表 --后台可以看到显示结果
select cast(字段 as numeric(10,3)) from 表名
//SQL自动增长字段
select id=(select count(*) from t where 主键<=a.主键),* from t a
select id=(select count(*) from t where 主键<=a.主键),* from t a
//删除数据库中重复数据
string st="DELETE FROM produce WHERE 记录编号 IN (SELECT MAX(记录编号) FROM produce GROUP BY 物资名称,数量,规格型号,下达时间,编号,单价 HAVING COUNT(记录编号)>1)";
1、计算某一时间到现在的分钟数
SELECT ID, DATEDIFF(mi, LastDate, GETDATE()) AS minutes FROM yourtable
如果LastDate格式为2004-9-28 09:12:12,当前时间为2004-9-28 09:45:12,则的的结果将是33分钟
string st="DELETE FROM produce WHERE 记录编号 IN (SELECT MAX(记录编号) FROM produce GROUP BY 物资名称,数量,规格型号,下达时间,编号,单价 HAVING COUNT(记录编号)>1)";
1、计算某一时间到现在的分钟数
SELECT ID, DATEDIFF(mi, LastDate, GETDATE()) AS minutes FROM yourtable
如果LastDate格式为2004-9-28 09:12:12,当前时间为2004-9-28 09:45:12,则的的结果将是33分钟
2、获取一个日期的年月日
SELECT CONVERT(varchar(10), LastDate, 120) AS yyyyMMdd FROM yourtable
如果LastDate格式为2004-9-28 09:12:12,通过转换后得到的结果为2004-9-28
/////////////////////////////////////////////////////////////////////
只要日期:
datetime类型在数据库中存储后就是这种格式的'2005-04-13 00:00:00.000'
2个办法:
(1) 将字段类型修改成 varchar(10) , 但会增加前台代码的编程负担;
select convert(char(10),getdate(),120)
只要日期:
datetime类型在数据库中存储后就是这种格式的'2005-04-13 00:00:00.000'
2个办法:
(1) 将字段类型修改成 varchar(10) , 但会增加前台代码的编程负担;
select convert(char(10),getdate(),120)
(2) Select REPLACE(CONVERT(varchar, 字段名称,102),'.','-') From 表名称 -- (被推荐的方法)
只要时间:
select substring (convert(char(16),getdate(),120),11,6)
用varchar存儲
select substring (convert(char(16),getdate(),120),11,6)
用varchar存儲
select CONVERT(CHAR(8),getdate() ,108)//推荐