SQL语句汇总

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

本帖最后由李维强-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? ... =5118&fromuid=8

sql server 递归查询
http://www.cqutlab.cn/forum.php? ... =5138&fromuid=8

exists语句用法
http://www.cqutlab.cn/forum.php? ... =5153&fromuid=8

SQL Server 在数据库中查找字符串（不知道表名的情况下查找字符串）
http://www.cqutlab.cn/forum.php? ... =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 temp3  where 车牌号 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)
--SELECT  CASE 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

SQL语句.rar (2.7 KB, 下载次数: 1)

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

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

----增加车辆
INSERT INTO [T_C_Truck] ([PlateNum], [OwnerName], [IDCardNum], [Phone], [ModelType], [LoadCapacity], [FrameNum], [GeneratorNum], [EnviroVerifyDate], [EnviroNum], [EnviroActiveDate], [CIATAVGetDate], [CIATAVPassDate], [InsuranceGetDate], [InsurancePassDate], [TaxGetDate], [RTCGetDate], [RTCPassDate], [MaintainDate], [MaintainPassDate], [YearVerifyDate], [YearVerifyPassDate], [BorrowTimes], [PaybackBeginDate], [PaybackPassDate], [MonthDate], [PaybackMoney], [PaybackTotal], [ContractActDate], [CashDeposit], [ManageCost], [ManagePayDate], [ExtraOilCard], [GPSIMEI], [active], [addDate]) 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)
SELECT  CASE 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 SET  a.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要改哦

temp.rar (3.02 KB, 下载次数: 6)

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

inner join on 和where的区别 \\

[SQL] syntaxhighlighter_viewsource syntaxhighlighter_copycode

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

[SQL] syntaxhighlighter_viewsource syntaxhighlighter_copycode

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 join  B 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

汇总表单查询语句

[SQL] syntaxhighlighter_viewsource syntaxhighlighter_copycode

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>6371  group 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.truckID WHERE 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

[SQL] syntaxhighlighter_viewsource syntaxhighlighter_copycode

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

PickAddressADDTrigger after  insert

insert into t_log (type,oplog,optime,opid) 
select top 1  10,'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语句汇总