CREATE TABLE [dbo].[TAB_调度常用司机](
[司机] nvarchar NOT NULL,
[司机编号] nvarchar NULL,
[创建人] nvarchar NOT NULL,
[创建人编号] nvarchar NULL,
[次数] [int] NULL,
CONSTRAINT [PK_TAB_调度常用司机] PRIMARY KEY CLUSTERED
(
[司机] ASC,
[创建人] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
MERGE INTO TAB_调度常用司机 t1
USING (
SELECT distinct
max(司机编号) as 司机编号, 司机,创建人,创建人编号
FROM Tab_运单表
where 司机<>’’
group by 司机,创建人,创建人编号
) t2
ON (t2.司机=t1.司机 and t2.创建人=t1.创建人)
WHEN NOT MATCHED THEN
INSERT (司机编号, 司机,创建人,创建人编号) VALUES (t2.司机编号, 司机,创建人,创建人编号);
可以单独执行 前提是两张表都存在 TAB_调度常用司机 Tab_运单表
MERGE INTO TAB_调度常用司机 t1
USING (
SELECT distinct
max(司机编号) as 司机编号, 司机,创建人,创建人编号 ,count(*) as 次数
FROM Tab_运单表
where 司机<>’’
group by 司机,创建人,创建人编号
) t2
ON (t2.司机=t1.司机 and t2.创建人=t1.创建人)
WHEN MATCHED THEN
update set t1.次数=t2.次数
WHEN NOT MATCHED THEN
INSERT (司机编号, 司机,创建人,创建人编号,次数) VALUES (t2.司机编号, 司机,创建人,创建人编号,次数);
MERGE INTO Tab_万金司机油户 t1
USING (
SELECT distinct
max(编号) as 编号, 姓名,身份证,手机号 ,count(*) as 次数
FROM Tab_司机
where 姓名<>’’
group by 编号, 姓名,身份证,手机号
) t2
ON (t2.编号=t1.司机万金账号)
WHEN MATCHED THEN
update set t1.次数=t2.次数
WHEN NOT MATCHED THEN
INSERT (司机万金账号, 司机姓名,身份证号,手机号) VALUES (t2.编号, t2.姓名,t2.身份证,t2.手机号);
select * from tab_运单表 t
where 运单状态 in (‘装运’,‘运输中’,‘已到达’,‘完成’)
and 车牌号 is not null and 车牌号<>