论坛 › PC系列 › SQL语句汇总

李维强-15级 发表于 2016-4-1 04:02:53

SQL语句汇总

本帖最后由 李维强-15级 于 2023-8-4 09:17 编辑

sql server 教程
https://www.yiibai.com/sqlserver

1楼给出目录以下楼层记录凡是我遇到的，需要记录下的SQL语句 都列出

2楼： like 的用法   2种通配符 “%”   与“_”
3楼：把时间转换为varchar的函数convert
6楼：inner join on 和where的区别

深入浅出SQL Server中的死锁
https://www.cnblogs.com/CareySon/archive/2012/09/19/2693555.html

SQL插入数据已经存在，则执行update更新
http://www.cqutlab.cn/forum.php?mod=redirect&goto=findpost&ptid=129&pid=5118&fromuid=8

sql server 递归查询
http://www.cqutlab.cn/forum.php?mod=redirect&goto=findpost&ptid=129&pid=5138&fromuid=8

exists语句用法
http://www.cqutlab.cn/forum.php?mod=redirect&goto=findpost&ptid=129&pid=5153&fromuid=8

SQL Server 在数据库中查找字符串（不知道表名的情况下 查找字符串）
http://www.cqutlab.cn/forum.php?mod=redirect&goto=findpost&ptid=129&pid=5219&fromuid=8

SET ANSI_NULLS OFF/ SET ANSI_NULLS ON   表示开启或者关闭 SQL-92 标准
当 SET ANSI_NULLS 为 ON 时：
即使 column_name 中包含空值，使用 WHERE column_name = NULL 的 SELECT 语句或 column_name 中包含非空值，使用 WHERE column_name <> NULL 的 SELECT 语句仍会返回零行。
当 SET ANSI_NULLS 为 OFF 时：
等于 (=) 和不等于 (<>) 比较运算符不遵从 SQL-92 标准。
使用 WHERE column_name = NULL 的 SELECT 语句返回 column_name 中包含空值的行。使用 WHERE column_name <> NULL 的 SELECT 语句返回列中包含非空值的行。
此外，使用 WHERE column_name <> XYZ_value 的 SELECT 语句返回所有不为 XYZ_value 也不为 NULL 的行。

李维强-15级 发表于 2016-4-1 04:03:12

like 的通配符有两种

%（百分号）：代表零个、一个或者多个字符。

_（下划线）：代表一个数字或者字符。



1. name以"李"开头

where name like '李%'



2. name中包含"云"，“云”可以在任何位置

where name like '%云%'



3. 第二个和第三个字符是0的值

where salary like '_00%'



4. 条件匹配以2开头，而且长度至少为3的值：

where salary like '2_%_%'



5. 以2结尾

where salary like '%2'



6. 第2个位置是2，以3结尾

where salary like '_2%3'

李维强-15级 发表于 2016-4-29 10:56:42

convert()第一个是varchar字符数，第二个是表里面的一个字段名，第3个是参数，代表不同的类型 后面是示例
Select CONVERT(varchar(100), GETDATE(), 0): 05 16 2006 10:57AM
Select CONVERT(varchar(100), GETDATE(), 1): 05/16/06
Select CONVERT(varchar(100), GETDATE(), 2): 06.05.16
Select CONVERT(varchar(100), GETDATE(), 3): 16/05/06
Select CONVERT(varchar(100), GETDATE(), 4): 16.05.06
Select CONVERT(varchar(100), GETDATE(), 5): 16-05-06
Select CONVERT(varchar(100), GETDATE(), 6): 16 05 06
Select CONVERT(varchar(100), GETDATE(), 7): 05 16, 06
Select CONVERT(varchar(100), GETDATE(), 8): 10:57:46
Select CONVERT(varchar(100), GETDATE(), 9): 05 16 2006 10:57:46:827AM
Select CONVERT(varchar(100), GETDATE(), 10): 05-16-06
Select CONVERT(varchar(100), GETDATE(), 11): 06/05/16
Select CONVERT(varchar(100), GETDATE(), 12): 060516
Select CONVERT(varchar(100), GETDATE(), 13): 16 05 2006 10:57:46:937
Select CONVERT(varchar(100), GETDATE(), 14): 10:57:46:967
Select CONVERT(varchar(100), GETDATE(), 20): 2006-05-16 10:57:47
Select CONVERT(varchar(100), GETDATE(), 21): 2006-05-16 10:57:47.157
Select CONVERT(varchar(100), GETDATE(), 22): 05/16/06 10:57:47 AM
Select CONVERT(varchar(100), GETDATE(), 23): 2006-05-16
Select CONVERT(varchar(100), GETDATE(), 24): 10:57:47
Select CONVERT(varchar(100), GETDATE(), 25): 2006-05-16 10:57:47.250
Select CONVERT(varchar(100), GETDATE(), 100): 05 16 2006 10:57AM
Select CONVERT(varchar(100), GETDATE(), 101): 05/16/2006
Select CONVERT(varchar(100), GETDATE(), 102): 2006.05.16
Select CONVERT(varchar(100), GETDATE(), 103): 16/05/2006
Select CONVERT(varchar(100), GETDATE(), 104): 16.05.2006
Select CONVERT(varchar(100), GETDATE(), 105): 16-05-2006
Select CONVERT(varchar(100), GETDATE(), 106): 16 05 2006
Select CONVERT(varchar(100), GETDATE(), 107): 05 16, 2006
Select CONVERT(varchar(100), GETDATE(), 108): 10:57:49
Select CONVERT(varchar(100), GETDATE(), 109): 05 16 2006 10:57:49:437AM
Select CONVERT(varchar(100), GETDATE(), 110): 05-16-2006
Select CONVERT(varchar(100), GETDATE(), 111): 2006/05/16
Select CONVERT(varchar(100), GETDATE(), 112): 20060516
Select CONVERT(varchar(100), GETDATE(), 113): 16 05 2006 10:57:49:513

李维强-15级 发表于 2016-5-3 02:59:38

本帖最后由 李维强-15级 于 2016-5-3 05:34 编辑

导入大量数据用到的sql

----------------------------------------------------------------------2016-5-2


--查看生产厂家哪些和系统对不起的。update把对不起的改成对得起的
--SELECT a.PickAddressId,a.[生产厂家],b.PickAddressId FROM temp3 a INNER JOIN T_M_PickAddress b ON a.[生产厂家]=b.Address
--ORDER BY a.PickAddressId
--UPDATE a SET a.[生产厂家]='南宁华润' FROM temp3 a where a.[生产厂家] not in(
--SELECT a.* FROM temp3 a WHERE a.[生产厂家] NOT IN(
--SELECT a.[生产厂家] FROM temp3 a INNER JOIN T_M_PickAddress b ON a.[生产厂家]=b.Address
--)
--把系统里面pickaddressID更新到temp表
--UPDATE a SET a.pickaddressID=b.pickaddressID FROM temp3 a INNER JOIN T_M_PickAddress b ON a.[生产厂家]=b.Address


--查看客户哪些和低筒对不起的
--SELECT a.[客户],a.clientID,b.clientID FROM temp3 a INNER JOIN T_M_ClientInformation b ON a.[客户]=b.clientName
--SELECT a.* FROM temp3 a WHERE a.[客户] not IN(SELECT a.[客户] FROM temp3 a INNER JOIN T_M_ClientInformation b ON a.[客户]=b.clientName)
--结合t_m_clientinfomation修改对不起的客户名
--UPDATE temp3 SET [客户]='龙岗合景天峻广场（个人现结）' WHERE [客户]='龙岗合景天骏广场（个人现结）'
--最后把clientID填到temp3里面去
--UPDATE a SET a.clientID=b.clientID FROM temp3 a INNER JOIN T_M_ClientInformation b ON a.[客户]=b.clientName

--检查pickaccountID对不对
--SELECT a.* FROM temp3 a where a.[提货账户] NOT IN(
--SELECT a.[提货账户] FROM temp3 a INNER JOIN T_M_PickAccount b ON a.[提货账户]=b.PickAccounT )
--扫描一次
--SELECT a.[提货账户],a.pickAccountID,b.PickAccountId FROM temp3 a INNER JOIN T_M_PickAccount b ON a.[提货账户]=b.PickAccounT
--把pickaccountID写到temp3里面去
--UPDATE a SET a.pickaccountID=b.pickAccountID FROM temp3 a INNER JOIN T_M_PickAccount b ON a.[提货账户]=b.PickAccounT

--检查规格相关操作
--SELECT a.[生产厂家],a.[水泥规格],a.cementTypeID,b.Address,b.GoodsType,b.GoodsTypeID FROM temp3 a INNER JOIN TypeView b ON a.[水泥规格]

=b.GoodsType AND a.pickaddressID=b.PickAddressId AND a.pickAccountID=b.PickAccountId
--把cementTypeID通过typeView写入temp里面
--UPDATE a SET a.cementTypeID=b.goodsTypeID FROM temp3 a INNER JOIN TypeView b ON a.[水泥规格]=b.GoodsType AND

a.pickaddressID=b.PickAddressId AND a.pickAccountID=b.PickAccountId

--检查operatorID
--SELECT a.* FROM temp3 a WHERE a.[业务员] NOT IN(SELECT a.[业务员] FROM temp3 a INNER JOIN T_P_Operator b ON a.[业务员]=b.name )
--把对不上的业务员纠错
--UPDATE temp3 SET [业务员]='陆伟明业务' WHERE [业务员]='陆伟明'
--扫描最终结果看行数是否一致
--SELECT a.[业务员],a.operatorID,b.name,b.operatorID FROM temp3 a INNER JOIN T_P_Operator b ON a.[业务员]=b.name
--吧t_p_operator表里面的operatorID写到temp3里面去
--UPDATE a SET a.operatorID=b.operatorID FROM temp3 a INNER JOIN T_P_Operator b ON a.[业务员]=b.name

--查看发货时间是否唯一
--SELECT COUNT(发货时间) as aaa,发货时间 FROM temp3 GROUP BY 发货时间 ORDER BY aaa DESC
--SELECT * FROM temp3 where 发货时间='2016-03-31 10:40:23'
--
--DELETE FROM temp3 WHERE 发货时间='2016-03-31 10:40:23'
--确定时间唯一了过后 用replace 转换成需要格式
--SELECT a.[发货时间],replace(REPLACE(REPLACE(发货时间, '-', ''),':',''),' ','') FROM temp3 a
--最后把demandID写入temp3里面
--UPDATE temp3 SET demandID=replace(REPLACE(REPLACE(发货时间, '-', ''),':',''),' ','')


--查找运输单位不在系统里面的
--SELECT 运输单位 FROM temp3 WHERE [运输单位]='自提'
--UPDATE temp3 SET [运输单位]='自提1' WHERE [运输单位]='自提'
--SELECT a.* FROM temp3 a where a.[运输单位] NOT IN(
--SELECT a.[运输单位] FROM temp3 a INNER JOIN T_C_Driver b ON a.[运输单位]=b.driverName
--)



--查找车牌号不在系统里面的
--SELECT a.* FROM temp3 a where a.[车牌号] NOT IN(
--SELECT a.[车牌号] FROM temp3 a INNER JOIN T_C_truck b ON a.[车牌号]=b.plateNum
--)
--更新车牌
--UPDATE temp3 SET 车牌号='桂AC7365'WHERE车牌号='AC7365'
--删除不在系统内的车牌号
--DELETE FROM temp3where 车牌号 NOT IN(
--SELECT a.[车牌号] FROM temp3 a INNER JOIN T_C_truck b ON a.[车牌号]=b.plateNum
--)

--查找运输单位和车牌 绑定 不符合系统规则的
SELECT a.* FROM temp3 a where a.[运输单位] NOT IN(
SELECT a.[运输单位] FROM temp3 a INNER JOIN DriverCarView b ON a.[运输单位]=b.driverName AND a.[车牌号]=b.PlateNum
) AND
a.[车牌号] NOT IN(
SELECT a.[车牌号] FROM temp3 a INNER JOIN DriverCarView b ON a.[运输单位]=b.driverName AND a.[车牌号]=b.PlateNum
)


--删除运输单位和车牌 绑定 不符合系统规则的
--DELETE FROM temp3 where 运输单位 NOT IN(
--SELECT a.[运输单位] FROM temp3 a INNER JOIN DriverCarView b ON a.[运输单位]=b.driverName AND a.[车牌号]=b.PlateNum
--)


SELECT a.* FROM temp3 a where a.[运输单位] NOT IN(
SELECT a.[运输单位] FROM temp3 a INNER JOIN DriverCarView b ON a.[运输单位]=b.driverName AND a.[车牌号]=b.PlateNum
)

SELECT a.* FROM temp3 a INNER JOIN DriverCarView b ON a.[运输单位]=b.driverName AND a.[车牌号]=b.PlateNum
--UPDATE a SET a.driverID=b.driverID,a.truckID=b.truckID FROM temp3 a INNER JOIN DriverCarView b ON a.[运输单位]=b.driverName AND a.[车牌

号]=b.PlateNum


--更新T_O_Demand
INSERT INTO T_O_Demand
SELECT a.demandID,a.demandID,'2',a.cementTypeID,销售单价,a.clientID,a.pickaccountID,a.PickaddressID,业务员,b.getstuffNum,3,null,CASE WHEN

水泥规格 LIKE'%散%' THEN 1 ELSE 出库数量 END,发货时间,发货时间,a.operatorID from temp3 a INNER JOIN T_M_ClientInformation b ON

a.clientID=b.clientID

--更新T_O_demandCount
--INSERT INTO T_O_demandCount(amount,demandID,releaseDate,operatorID,sransportType,state,alreadyTransport)
--SELECTCASE WHEN 水泥规格 LIKE'%散%' THEN 1 ELSE 出库数量 END,demandID,发货时间,operatorID,1,1,CASE WHEN 水泥规格 LIKE'%散%' THEN 1

ELSE 出库数量 END FROM temp3


--更新INTO T_O_Order 表
INSERT INTO T_O_Order
(orderNumID,demandcountID,getstuffAddress,clientName,getStuffNum,pickaddress,quantity,startTime,cementType,sellprice,transprice,salesmanN

ame,pickaccount,defaultprctime,startTranstime,endtranstime,verifiedTime,remark,factoryGetNum,verifiedNum,verifiedName,driverID,factoryPri

ce,factoryTime,truckID,orderType,acceptState,orderState,clientID,pickaccountID,produceNum,diaoduyuanID,tongjiyuanID)
SELECT a.demandID+'00',b.demandCountID,c.getstuffAddress,c.clientName,c.GetStuffNum,a.[生产厂家],CASE WHEN 水泥规格 LIKE'%散%' THEN 1

ELSE 出库数量 END,a.发货时间,水泥规格,销售单价,运费,业务员,提货账户,30,发货时间,发货时间,发货时间,备注,交货单号,验收数量,'3月导

入',driverID,出厂单价,发货时间,truckID,1,3,4,a.clientID,pickaccountID,出库数量,107,103 FROM temp3 a
INNER JOIN T_O_demandCount b ON a.demandID=b.demandID
INNER JOIN T_M_ClientInformation c ON a.clientID=c.clientID


--客户回款 改变回款时间为唯一值   
SELECT DATEADD(ss, id, a.[单据日期]) as paytime,a.* FROM temp2_get a
--客户回款找到客户回款里面 客户名与系统不相同的
SELECT a.* FROM temp2_get a WHERE a.[往来单位] not IN(
SELECT 往来单位 FROM temp2_get a INNER JOIN T_M_ClientInformation b ON a.[往来单位]=b.clientName
)
--更新客户回款 clientID
UPDATE a SET a.clientID=b.clientID FROM temp2_get a INNER JOIN T_M_ClientInformation b ON a.[往来单位]=b.clientName


--客户回款更新type和memo
UPDATE temp2_get SET type=[收款方式],memo=[票号]

--找到系统里面没有的公司账户
SELECT 收款账户 FROM temp2_get WHERE 收款账户 NOT IN(
SELECT 收款账户 FROM temp2_get a INNER JOIN T_M_companyAccount b ON a.[收款账户]=b.name
)
--把没有的账户更改名字
UPDATE temp2_get SET 收款账户='热度南宁市区农村信用合作联社石埠信用社' WHERE 收款账户='热度南宁市区农村信用合作联社石埠信用'
--看下公司账户在目标表里面的名字
SELECT 收款账户 FROM temp2_get a INNER JOIN T_M_companyAccount b ON a.[收款账户]=b.name

--扫描下公司账户和临时表里面的账户
SELECT 收款账户,a.companyaccID,b.name,b.companyAccountID FROM temp2_get a INNER JOIN T_M_companyAccount b ON a.[收款账户]=b.name
--客户回款更新companyaccountID到temp2_get
UPDATE a SET a.companyaccID=b.companyaccountID FROM temp2_get a INNER JOIN T_M_companyAccount b ON a.[收款账户]=b.name


--正式更新客户回款信息
INSERT INTO T_C_ClientPayment
(clientID,PaymentMoney,paymentdate,OperatorID,AddDate,companyAccountID,memo,type)
SELECT a.clientID,a.[总金额],CONVERT(varchar(100), DATEADD(ss, a.id, a.[单据日期]), 20),107,'2016-02-10 02:02:02',a.companyaccID,memo,type FROM temp2_get a

李维强-15级 发表于 2016-5-12 10:59:01

本帖最后由 李维强-15级 于 2016-5-12 11:02 编辑

----增加车辆
INSERT INTO (, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ) VALUES('桂AD5972', '临时车', '0', '0', '0', '0', '0', '0', '2016-04-30', '0', '2025-04-01', '2016-04-30', '2025-04-23', '2016-04-30', '2025-04-08', '2016-04-30', '2025-04-08', '2025-04-30', '2016-04-30', '2026-04-08', '2016-04-30', '2027-04-05', '1', '2016-04-30', '2025-04-17', '1', '1', '1', '2025-04-03', '1', '1', '2025-04-04', '无', '', '0', '2016-04-30 17:41:36')






--找生成厂家生成2个结果   结果1是不在系统内的结果2是和系统关联的总数
SELECT a.[生产厂家] FROM temp3 a where a.[生产厂家] not IN(
SELECT a.[生产厂家] FROM temp3 a INNER JOIN T_M_PickAddress b ON a.[生产厂家]=b.Address
)
SELECT a.[生产厂家] FROM temp3 a INNER JOIN T_M_PickAddress b ON a.[生产厂家]=b.Address

--把系统里面pickaddressID更新到temp表
UPDATE a SET a.pickaddressID=b.pickaddressID FROM temp3 a INNER JOIN T_M_PickAddress b ON a.[生产厂家]=b.Address


--查看客户哪些和系统对不起的
SELECT a.* FROM temp3 a where a.[客户] not IN(
SELECT a.[客户] FROM temp3 a INNER JOIN T_M_ClientInformation b ON a.[客户]=b.clientName
)
--改客户名字
--UPDATE temp3 SET [客户]='中铁六局－良庆大道（个人月结）' WHERE [客户]='中铁六局－良庆大道（个人现结）'
--最后扫描一下 看客户名字和系统的数据能否对得上
SELECT a.[客户] FROM temp3 a INNER JOIN T_M_ClientInformation b ON a.[客户]=b.clientName
--最后把clientID填到temp3里面去
UPDATE a SET a.clientID=b.clientID FROM temp3 a INNER JOIN T_M_ClientInformation b ON a.[客户]=b.clientName


--检查pickaccountID对不对
SELECT a.* FROM temp3 a where a.[提货账户] NOT IN(
SELECT a.[提货账户] FROM temp3 a INNER JOIN T_M_PickAccount b ON a.[提货账户]=b.PickAccounT )

--扫描一次 提货账户 看数量对不对
SELECT a.[提货账户],a.pickAccountID,b.PickAccountId FROM temp3 a INNER JOIN T_M_PickAccount b ON a.[提货账户]=b.PickAccounT
--把pickaccountID写到temp3里面去
UPDATE a SET a.pickaccountID=b.pickAccountID FROM temp3 a INNER JOIN T_M_PickAccount b ON a.[提货账户]=b.PickAccounT


--检查规格相关操作
SELECT a.[生产厂家],a.[水泥规格],a.cementTypeID,b.Address,b.GoodsType,b.GoodsTypeID
FROM temp3 a INNER JOIN TypeView b ON a.[水泥规格]=b.GoodsType AND a.pickaddressID=b.PickAddressId AND a.pickAccountID=b.PickAccountId
--把cementTypeID通过typeView写入temp里面
UPDATE a SET a.cementTypeID=b.goodsTypeID
FROM temp3 a INNER JOIN TypeView b ON a.[水泥规格]=b.GoodsType AND a.pickaddressID=b.PickAddressId AND a.pickAccountID=b.PickAccountId


--检查operatorID
SELECT a.* FROM temp3 a WHERE a.[业务员] NOT IN(SELECT a.[业务员] FROM temp3 a INNER JOIN T_P_Operator b ON a.[业务员]=b.name )
--把对不上的业务员纠错
UPDATE temp3 SET [业务员]='陆伟明业务' WHERE [业务员]='陆伟明'
--扫描最终结果看行数是否一致
SELECT a.[业务员],a.operatorID,b.name,b.operatorID FROM temp3 a INNER JOIN T_P_Operator b ON a.[业务员]=b.name
--把t_p_operator表里面的operatorID写到temp3里面去
UPDATE a SET a.operatorID=b.operatorID FROM temp3 a INNER JOIN T_P_Operator b ON a.[业务员]=b.name


--查看发货时间是否唯一
--SELECT COUNT(发货时间) as aaa,发货时间 FROM temp3 GROUP BY 发货时间 ORDER BY aaa DESC
--SELECT * FROM temp3 where 发货时间='2016-03-31 10:40:23'
--
--DELETE FROM temp3 WHERE 发货时间='2016-03-31 10:40:23'
--确定时间唯一了过后 用replace 转换成需要格式
--SELECT a.[发货时间],replace(REPLACE(REPLACE(发货时间, '-', ''),':',''),' ','') FROM temp3 a
--最后把demandID写入temp3里面
--UPDATE temp3 SET demandID=replace(REPLACE(REPLACE(发货时间, '-', ''),':',''),' ','')


--查找运输单位不在系统里面的1
SELECT 运输单位,车牌号 FROM temp3 WHERE [运输单位]='自提1'
--更新自提1
UPDATE temp3 SET [运输单位]='自提1' WHERE [运输单位]='自提'

SELECT a.* FROM temp3 a where a.[运输单位] NOT IN(
SELECT a.[运输单位] FROM temp3 a INNER JOIN T_C_Driver b ON a.[运输单位]=b.driverName
)



--查找车牌号不在系统里面的
SELECT a.* FROM temp3 a where a.[车牌号] NOT IN(
SELECT a.[车牌号] FROM temp3 a INNER JOIN T_C_truck b ON a.[车牌号]=b.plateNum
)

--
--查找运输单位和车牌 绑定 不符合系统规则的
SELECT a.* FROM temp3 a where a.[运输单位] NOT IN(
SELECT a.[运输单位] FROM temp3 a INNER JOIN DriverCarView b ON a.[运输单位]=b.driverName AND a.[车牌号]=b.PlateNum
) or
a.[车牌号] NOT IN(
SELECT a.[车牌号] FROM temp3 a INNER JOIN DriverCarView b ON a.[运输单位]=b.driverName AND a.[车牌号]=b.PlateNum
)
--一下两条select出两个结果对比
SELECT a.* FROM temp3 a INNER JOIN DriverCarView b ON a.[运输单位]=b.driverName AND a.[车牌号]=b.PlateNum
SELECT * FROM temp3


--更新driverID 和truckID
UPDATE a SET a.driverID=b.driverID,a.truckID=b.truckID
FROM temp3 a INNER JOIN DriverCarView b ON a.[运输单位]=b.driverName AND a.[车牌号]=b.PlateNum


--更新T_O_Demand表
INSERT INTO T_O_Demand
SELECT a.demandID,a.demandID,'2',a.cementTypeID,销售单价,a.clientID,a.pickaccountID,a.PickaddressID,业务员,b.getstuffNum,3,null,CASE WHEN 水泥规格 LIKE'%散%' THEN 1 ELSE 出库数量 END,发货时间,发货时间,a.operatorID
from temp3 a INNER JOIN T_M_ClientInformation b ON a.clientID=b.clientID


--更新T_O_demandCount
INSERT INTO T_O_demandCount(amount,demandID,releaseDate,operatorID,sransportType,state,alreadyTransport)
SELECTCASE WHEN 水泥规格 LIKE'%散%' THEN 1 ELSE 出库数量 END,demandID,发货时间,operatorID,1,1,CASE WHEN 水泥规格 LIKE'%散%' THEN 1 ELSE 出库数量 END FROM temp3



--更新INTO T_O_Order 表 注意 a.demandID+'00'这个后面的数字可以变更
INSERT INTO T_O_Order
(orderNumID,demandcountID,getstuffAddress,clientName,getStuffNum,pickaddress,quantity,startTime,cementType,sellprice,transprice,salesmanName,pickaccount,defaultprctime,startTranstime,endtranstime,verifiedTime,remark,factoryGetNum,verifiedNum,verifiedName,driverID,factoryPrice,factoryTime,truckID,orderType,acceptState,orderState,clientID,pickaccountID,produceNum,diaoduyuanID,tongjiyuanID)
SELECT a.demandID+'00',b.demandCountID,c.getstuffAddress,c.clientName,c.GetStuffNum,a.[生产厂家],CASE WHEN 水泥规格 LIKE'%散%' THEN 1 ELSE 出库数量 END,a.发货时间,水泥规格,销售单价,运费,业务员,提货账户,30,发货时间,发货时间,发货时间,备注,交货单号,验收数量,'3月导入（2次问题修改后）',driverID,出厂单价,发货时间,truckID,1,3,4,a.clientID,pickaccountID,出库数量,107,103 FROM temp3 a
INNER JOIN T_O_demandCount b ON a.demandID=b.demandID
INNER JOIN T_M_ClientInformation c ON a.clientID=c.clientID


--账户付款，看那些公司账户不在系统内
SELECT a.* FROM temp3_pay a WHERE a.[付款账户] NOT IN(
SELECT a.付款账户 FROM temp3_pay a INNER JOIN T_M_companyAccount b ON a.付款账户=b.name
)
--修改公司账户信息   
UPDATE temp3_pay SET 付款账户='热度南宁市区农村信用合作联社石埠信用社' WHERE 付款账户='热度南宁市区农村信用合作联社石埠信用'
--扫描一次公司账户
SELECT a.付款账户 FROM temp3_pay a INNER JOIN T_M_companyAccount b ON a.付款账户=b.name
--更新companyaccID
UPDATE a SET a.companyaccID=b.companyaccountID FROM temp3_pay a INNER JOIN T_M_companyAccount b ON a.付款账户=b.name


--账户付款，查找提货账户哪些不在系统里面
SELECT * FROM temp3_pay a WHERE a.[提货账户] NOT IN(
SELECT 提货账户 FROM temp3_pay a INNER JOIN T_M_PickAccount b ON a.[提货账户]=b.pickaccounT
)
--修改提货账户
UPDATE temp3_pay SET 提货账户='南宁热度（袋）' WHERE 提货账户='南宁厂热度（袋）'
--更新 pickaccountID
UPDATE a set a.pickaccountID=b.pickaccountID FROM temp3_pay a INNER JOIN T_M_PickAccount b ON a.[提货账户]=b.pickaccounT
--查找操作员 不在系统里面的
SELECT * FROM temp3_pay a WHERE a.[操作员] NOT IN(
SELECT 操作员 FROM temp3_pay a INNER JOIN T_P_Operator b ON a.[操作员]=b.name
)
--更新operatorID
UPDATE a SET a.operatorID=b.operatorID FROM temp3_pay a INNER JOIN T_P_Operator b ON a.[操作员]=b.name
--更新addDate
UPDATE temp3_pay SET addDate='2016-03-10 03:03:03'
--更新paymentDate
UPDATE a SETa.paymentDate=CONVERT(varchar(100), DATEADD(ss, a.id, a.[单据日期]),20) FROM temp3_pay a
--把结果插入到t_c_accountPayment 里面
INSERT INTO T_C_AccountPayment
(pickaccountID,paymentMoney,paymentDate,operatorID,addDate,companyAccountID,memo,type)
SELECT pickaccountID,总金额,paymentDate,operatorID,addDate,companyaccID,memo,type
FROM temp3_pay--temp3_pay要改哦



--客户回款更新type和memo
UPDATE temp3_get SET type=[收款方式],memo=[票号]
--客户回款 改变回款时间为唯一值   
UPDATE a SET a.paymentDate=CONVERT(varchar(100), DATEADD(ss, a.id, a.[单据日期]), 20) FROM temp3_get a
--客户回款找到客户回款里面 客户名与系统不相同的
SELECT a.* FROM temp3_get a WHERE a.[往来单位] not IN(
SELECT 往来单位 FROM temp3_get a INNER JOIN T_M_ClientInformation b ON a.[往来单位]=b.clientName
)
--修改客户名称
UPDATE temp3_get SET [往来单位]='周小华－陆屋工地（公司现结）' WHERE [往来单位]='周小华（公司现结）'
--更新客户回款 clientID
UPDATE a SET a.clientID=b.clientID FROM temp3_get a INNER JOIN T_M_ClientInformation b ON a.[往来单位]=b.clientName
--找到系统里面没有的公司账户
SELECT 收款账户 FROM temp3_get WHERE 收款账户 NOT IN(
SELECT 收款账户 FROM temp3_get a INNER JOIN T_M_companyAccount b ON a.[收款账户]=b.name
)
--客户回款更新companyaccountID到temp3_get
UPDATE a SET a.companyaccID=b.companyaccountID FROM temp3_get a INNER JOIN T_M_companyAccount b ON a.[收款账户]=b.name
--查看操作员是否都在系统里面
SELECT 操作员 FROM temp3_get a WHERE 操作员 not IN(
SELECT 操作员 FROM temp3_get a INNER JOIN T_P_Operator b ON a.[操作员]=b.name
)
--查看操作员是否都在系统里面
SELECT 操作员 FROM temp3_get a WHERE 操作员 not IN(
SELECT 操作员 FROM temp3_get a INNER JOIN T_P_Operator b ON a.[操作员]=b.name
)
--更新operatorID到temp3_get里面
UPDATE a set a.operatorID=b.operatorID FROM temp3_get a INNER JOIN T_P_Operator b ON a.[操作员]=b.name
--插入到t_c_clientPayment表里面
INSERT INTO T_C_ClientPayment
(clientID,PaymentMoney,paymentdate,OperatorID,AddDate,companyAccountID,memo,type)
SELECT a.clientID,a.[总金额],CONVERT(varchar(100), DATEADD(ss, a.id, a.[单据日期]), 20),a.operatorID,'2016-03-10 03:03:03',a.companyaccID,a.memo,a.type
FROM temp3_get a--temp3_get要改哦

李维强-15级 发表于 2016-5-15 13:07:05

inner join on 和where的区别 \\

SELECT a.clientname,a.money,b.clientName,b.money
FROM redu a INNER JOIN my b ON a.clientName=b.clientName WHERE a.money=b.money


SELECT a.clientname,a.money,b.clientName,b.money
FROM redu a INNER JOIN my b ON a.clientName=b.clientName AND a.money=b.money


我原意是查找 a表 和b表 里面 clientname 相等的 情况下 money 也相等的

然后我用了上面2条语句来查询查询结果都是一样的 但是我想搞清楚其中不同的意义在哪里

答案：

INNER JOIN 是一样的。
如果换成 left join 或right join ，是有区别的；

A left joinB on 后面的语句，是对 表 B 数据的过滤 ，而 where 是对结果集的过滤；

李维强-15级 发表于 2016-6-11 15:49:36

===销售明细表
SELECT a.ClientName,'售出' AS detail,0 AS payback,a.SellPrice*a.VerifiedNum AS sellnum,0 AS total,a.FactoryTime,d.name AS sellMan
FROM T_O_Order a INNER JOIN T_M_ClientInformation b ON a.clientID=b.clientID
INNER JOIN T_M_ClientAndSalesman c ON b.clientID=c.clientID AND c.isMaster=1
INNER JOIN T_P_Operator d ON c.operatorID=d.operatorID
WHERE a.OrderState=4 AND a.FactoryTime BETWEEN '2016-01-06 11:03:15' AND '2016-06-05 11:03:23'
--按照 袋装 散装       按照业务员         按照客户筛选       --按照区域         --按照客户编码   
--AND b.packaging=1 AND d.operatorID=48 AND b.clientID=43 AND b.number like '4%'AND b.number>='40317' AND b.number<='40317'

UNION
SELECT e.clientName,'回款' AS detail,e.PaymentMoney,0,0,e.PaymentDate,d.name as sellman
FROM ClientPaymentView e INNER JOIN T_M_ClientInformation b ON e.ClientID=b.clientID
INNER JOIN T_M_ClientAndSalesman c ON b.clientID=c.clientID AND c.isMaster=1
INNER JOIN T_P_Operator d ON c.operatorID=d.operatorID
WHERE e.PaymentDate BETWEEN '2016-01-06 11:03:15' AND '2016-06-05 11:03:23'

--AND d.operatorID=48 AND b.clientID=43 AND b.number like '40%'   AND b.number<='40317'

UNION
SELECT clientName,'期初数据' AS detail,SUM (sumpayback),SUM (sumsellnum),SUM (sumsellnum - sumpayback),'2016-01-06 11:03:15',sellMan
FROM (
                        SELECT sum(e.paymentmoney) AS sumpayback,0 AS sumsellnum,e.clientName,d.name AS sellMan
                        FROM ClientPaymentView e INNER JOIN T_M_ClientInformation b ON e.ClientID=b.clientID
                        INNER JOIN T_M_ClientAndSalesman c ON b.clientID=c.clientID AND c.isMaster=1
                        INNER JOIN T_P_Operator d ON c.operatorID=d.operatorID
                        WHERE e.PaymentDate < '2016-01-06 11:03:15'
                        -- con
                        GROUP BY e.clientName,d.name
                UNION ALL
                        SELECT 0 AS sumpayback,SUM(a.sellprice * a.verifiednum) AS sumsellnum,b.clientName,d.name AS sellMan
                        FROM T_O_Order a INNER JOIN T_M_ClientInformation b ON a.clientID=b.clientID
                        INNER JOIN T_M_ClientAndSalesman c ON b.clientID=c.clientID AND c.isMaster=1
                        INNER JOIN T_P_Operator d ON c.operatorID=d.operatorID
                        WHERE a.OrderState=4 AND a.FactoryTime < '2016-01-06 11:03:15'
                        -- con
                        GROUP BY b.clientName,d.name
) AS a
GROUP BY clientName,sellMan

ORDER BY ClientName,FactoryTime

李维强-15级 发表于 2016-8-15 23:28:38

汇总表单查询语句


select left(starttime,10) 日期,sum(case when orderstate=0 and acceptstate=2 then 1 else 0 end) as 拒绝,sum(case when orderstate=0 and acceptstate=1 then 1 else 0 end) as 待确认,sum(case when orderstate in(1,2) then 1 else 0 end) as 已接单未交货,sum(case when OrderState=3 and img is null then 1 else 0 end) as 已交货未传磅单,sum(case when OrderState=3 and not img is null then 1 else 0 end) as 已传磅单,sum(case when OrderState=4 then 1 else 0 end) as 已复核,sum(case when 1=1 then 1 else 0 end) as 合计 from t_o_order where orderid>6371group by left(starttime,10) having left(starttime,10)>'2016-08'
union select left(starttime,7)+'合计',sum(case when orderstate=0 and acceptstate=2 then 1 else 0 end) as 拒绝,sum(case when orderstate=0 and acceptstate=1 then 1 else 0 end) as 待确认,sum(case when orderstate in(1,2) then 1 else 0 end) as 已确认未交货,sum(case when OrderState=3 and img is null then 1 else 0 end) as 已交货未传磅单,sum(case when OrderState=3 and not img is null then 1 else 0 end) as 已传磅单,sum(case when OrderState=4 then 1 else 0 end) as 已统计,sum(case when 1=1 then 1 else 0 end) as 合计 from t_o_order where orderid>6371 group by left(starttime,7)

李维强-15级 发表于 2016-9-5 13:53:13

统计司机上传磅单模板

SELECT a.*,b.driverName,c.PlateNum FROM T_O_Order a INNER JOIN T_C_Driver b ON a.DriverID=b.driverID INNER JOIN T_C_Truck c ON a.truckID=c.truckIDWHERE NOT img is null AND OrderState>2 AND OrderNumID LIKE '201608%' AND orderID not in(
SELECT orderid FROM T_O_Order WHERE NOT img is null AND OrderState<4 AND OrderNumID LIKE '201608%'
) ORDER BY a.DriverID,StartTime

李维强-15级 发表于 2017-3-1 14:40:47

----------------------------------------
t_m_pickaddress

PickAddressADDTrigger afterinsert

insert into t_log (type,oplog,optime,opid)
select top 110,'trigger-新增厂家地址 Pickaddressid='+convert(varchar(100),PickAddressId)+',Address='+Address+',number='+convert(varchar(100),number),CONVERT(varchar(100), GETDATE(), 120),0
from inserted

PickAddressUpdateTrigger after update

declare @log varchar(2000);
set @log='修改生产厂家: ';
select @log+= case when i.address<>d.address then ' address='+convert(varchar(100),i.address) else '' end from inserted i left join deleted d on i.PickAddressId=d.PickAddressId;
select @log+= case when i.number<>d.number then' number='+i.number else '' end from inserted i left join deleted d on i.PickAddressId=d.PickAddressId;
insert into t_log (type,oplog,optime,opid) values (10,@log, CONVERT(varchar(100), GETDATE(), 120),0)

-------------------------------------------------------------------------

查看完整版本: SQL语句汇总