SQL Server复杂语句记录

注意：

1. 固定因数用${}如表名，其它条件用#{}如id、age`。
2. 我使用的是Navicat 16版本

1.将id设置为行号(一个自增的值)

方式一：（推荐）

UPDATE x 
SET x.id = x.rowid,
x.tenant_id = '000000',
x.create_user = '1123598821738675201',
x.create_dept = '1123598813738675201',
x.create_time = '2022-05-01 14:52:49.000',
x.update_user = '1123598821738675201',
x.update_time = '2022-05-01 14:52:49.000',
x.status = 1,
x.is_deleted = 0 
FROM
	(
	SELECT
		id,
		tenant_id,
		create_user,
		create_dept,
		create_time,
		update_user,
		update_time,
		status,
		is_deleted,
		ROW_NUMBER ( ) OVER ( ORDER BY BILLNO ) AS rowid 
	FROM
		WEIGHTLOG 
	) x

方式二：新建视图方式

-- 1.创建视图(带有行号)
CREATE VIEW BS_TRUCKINFO_VIEW AS SELECT row_number() OVER (ORDER BY SHIPNAME) AS rowNum, * FROM BS_TRUCKINFO

-- 2.将id设置为行号
UPDATE BS_TRUCKINFO SET id = v.rowNum
FROM BS_TRUCKINFO_VIEW v
WHERE BS_TRUCKINFO.SHIPNAME = v.SHIPNAME

或者(上面是分开操作，下面是一起执行 )

CREATE VIEW BS_CLIENT_VIEW AS SELECT row_number() OVER (ORDER BY OWNERID) AS rowNum, * FROM BS_CLIENT
GO
UPDATE BS_CLIENT SET id = v.rowNum
FROM BS_CLIENT_VIEW v
WHERE BS_CLIENT.OWNERID = v.OWNERID
GO

2.将一张表中数据插入到领一张表

# 将一张表中数据插入到领一张表（跨库，字段结构不同）(我这儿是把旧表的GOODSID插入为新表的id了，其实在Navicat把id设为自增就可以了)
INSERT INTO goods (
	[id],[tenant_id],[create_user],[create_dept],[create_time],[update_user],[update_time],[status],[is_deleted],[goods_code],[goods_name],[abbr_name],[per_cask_weight],[density],[modulus],[qty_rate],[remark],[sort] 
	) (
	SELECT
		GOODSID,'000000',1123598821738675201,1123598813738675201,'2021-09-20 16:01:11.000',1123598821738675201,'2021-09-20 16:01:15.000',1,0,GOODSID,GOODSNAME,SHORTCODE,PERWEIGHT,0.8490,1.00000,1.00000,NULL,1 
	FROM
		cjwms.dbo.GOODS 
	)

3.查询当前时间明天的数据

# 查询当前时间明天的数据
select  * 
from kj_set_bill_num
where convert(varchar,create_time,102) = convert(varchar,DATEADD(day,1,GETDATE()),102)

4.查询字段信息

# 查询某个表的字段信息
SELECT
    表名 = case when a.colorder = 1 then d.name else '' end,
    表说明 = case when a.colorder = 1 then isnull(f.value, '') else '' end,
    字段序号 = a.colorder,
    字段名 = a.name,
    标识 = case when COLUMNPROPERTY(a.id, a.name, 'IsIdentity')= 1 then '√'else '' end,
    主键 = case when exists(SELECT 1 FROM sysobjects where xtype = 'PK' and parent_obj = a.id and name in (
               SELECT name FROM sysindexes WHERE indid in(SELECT indid FROM sysindexkeys WHERE id = a.id AND colid = a.colid))) then '√' else '' end,
    类型 = b.name,
    占用字节数 = a.length,
    长度 = COLUMNPROPERTY(a.id, a.name, 'PRECISION'),
    小数位数 = isnull(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0),
    允许空 = case when a.isnullable = 1 then '√'else '' end,
    默认值 = isnull(e.text, ''),
    字段说明 = isnull(g.[value], '')
FROM
    syscolumns a
left join
    systypes b
on
    a.xusertype = b.xusertype
inner join
    sysobjects d
on
    a.id = d.id  and d.xtype = 'U' and d.name <> 'dtproperties'
left join
    syscomments e
on
    a.cdefault = e.id
left join
sys.extended_properties g
on
    a.id = G.major_id and a.colid = g.minor_id
left join
sys.extended_properties f
on
    d.id = f.major_id and f.minor_id = 0
where
    d.name = 'blade_notice'
order by
    a.id,a.colorder

# 查询某个表的字段信息2
SELECT
column_name = a.name,
is_required = case when (a.isnullable=0 AND NOT exists(SELECT 1 FROM sysobjects where xtype = 'PK' and parent_obj = a.id and name in (
               SELECT name FROM sysindexes WHERE indid in(SELECT indid FROM sysindexkeys WHERE id = a.id AND colid = a.colid))))  then '1' else null end,
 is_pk = case when exists(SELECT 1 FROM sysobjects where xtype = 'PK' and parent_obj = a.id and name in (
               SELECT name FROM sysindexes WHERE indid in(SELECT indid FROM sysindexkeys WHERE id = a.id AND colid = a.colid))) then 1 else 0 end,
 sort = a.colorder,
 column_comment = isnull(g.[value], ''),
 is_increment = 0,
 column_type = b.name
FROM
    syscolumns a
left join
    systypes b
on
    a.xusertype = b.xusertype
inner join
    sysobjects d
on
    a.id = d.id  and d.xtype = 'U' and d.name != 'dtproperties'
left join
    syscomments e
on
    a.cdefault = e.id
left join
sys.extended_properties g
on
    a.id = G.major_id and a.colid = g.minor_id
left join
sys.extended_properties f
on
    d.id = f.major_id and f.minor_id = 0
where
    d.name = 'blade_notice'
order by
    a.id,a.colorder

# 查询某个表的字段信息3
SELECT * FROM SysColumns WHERE id=Object_Id('blade_notice')

# 查询当前表名
Select Name From Master..SysDataBases Where DbId=(Select Dbid From Master..SysProcesses Where Spid = @@spid)

5.查询重复单号

# 查询重复单号，COUNT(*) > 1 的就是重复的
SELECT COUNT(*) count,BILLNO from CHANGE GROUP BY BILLNO

# 查询重复单号，COUNT(*) > 1 的
SELECT COUNT(*) count,BILLNO from CHANGE GROUP BY BILLNO HAVING COUNT(*) > 1

# 删除某个表中有重复单号的所有条目
DELETE 
FROM
	CHANGE 
WHERE
	BILLNO IN ( SELECT BILLNO FROM CHANGE GROUP BY BILLNO HAVING COUNT ( * ) > 1 )

6.给某个表添加字段

# CLIENT表添加ISFOREIGN：是否外商、OWNERID_FOREIGN外商客户代码
IF NOT EXISTS(SELECT * FROM SYSCOLUMNS A INNER JOIN SYSOBJECTS B ON A.ID=B.ID WHERE B.NAME='CLIENT' AND A.NAME='ISFOREIGN')
BEGIN
	ALTER TABLE CLIENT
	ADD
	ISFOREIGN	INT  NULL DEFAULT 0,
	OWNERID_FOREIGN VARCHAR(60) NULL

EXEC('UPDATE CLIENT SET ISFOREIGN=0 WHERE ISFOREIGN IS NULL')

END

7.更新某个表的某个字段

!!注意：固定因数用${}如表名，其它条件用#{}如id、age。
UPDATE和INNER JOIN的结合用法，此处tablename使用#{}会报错，因为到了数据库中执行的时候就会变成‘表名’，加了引号数据库是不识别的，因此像这种固定因数表名作为参数只能使用${}，其它参数使用#{}。

/**
 * 更新标志
 *
 * @param tablename 表名
 * @param billno 单号
 */
void updateHold(@Param("tablename") String tablename, @Param("billno") String billno);

# UPDATE和INNER JOIN的结合用法
<update id="updateHoldlocation" >
    UPDATE A SET ISHOLDLOCATION = B.ISHOLDLOCATION FROM ${tablename} A
        INNER JOIN (
            SELECT
                CONTRACTNO,
                ISHOLDLOCATION
            FROM
                INHOUSE
            WHERE
                FLOWTYPE = 1
                AND STATE >= 4

            UNION ALL

            SELECT
                INCONTRACTNO,
                ISHOLDLOCATION
            FROM
                CHANGE
            WHERE
                STATE >= 2
                AND CHANGETYPE > 1
        ) B ON A.OUTCONTRACTNO = B.CONTRACTNO
    WHERE
        A.BILLNO = #{billno}
</update>