重工电子论坛

标题: SQL语句汇总 [打印本页]

作者: 李维强-15级 时间: 2016-4-1 04:02
标题: 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? ... =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
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
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
本帖最后由李维强-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
本帖最后由李维强-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
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
===销售明细表
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
汇总表单查询语句

[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
统计司机上传磅单模板

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

[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)



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

作者: 李维强-15级 时间: 2017-3-5 19:34
本帖最后由李维强-15级于 2017-3-5 19:38 编辑

----------------------------------------
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)

-------------------------------------------------------------------------
t_m_PickAccount

PickAccountAddTrigger after insert

insert into t_log (type,oplog,optime,opid)
select top 1  20,'trigger-新增提货账户 PickAccountId='+convert(varchar(100),PickAccountId)+
',PickAddressId='+convert(varchar(100),PickAddressId)+',PickAccounT='+convert(varchar(100),PickAccounT)+
',number='+convert(varchar(100),number)+',packaging='+convert(varchar(100),packaging),CONVERT(varchar(100), GETDATE(), 120),0
from inserted

PickAccountUpdateTrigger after update

declare @log varchar(2000);
select top 1 @log='Trigger-编辑提货账户: PickAccountId='+convert(varchar(100),PickAccountId) from inserted;
select top 1 @log+= case when i.PickAddressId<>d.PickAddressId then ' PickAddressId='+convert(varchar(100),i.PickAddressId) else '' end from inserted i left join deleted d on i.PickAccountId=d.PickAccountId;
select top 1 @log+= case when i.PickAccounT<>d.PickAccounT then ' PickAccounT='+convert(varchar(100),i.PickAccounT) else '' end from inserted i left join deleted d on i.PickAccountId=d.PickAccountId;
select top 1 @log+= case when i.number<>d.number then ' number='+convert(varchar(100),i.number) else '' end from inserted i left join deleted d on i.PickAccountId=d.PickAccountId;
select top 1 @log+= case when i.packaging<>d.packaging then ' packaging='+convert(varchar(100),i.packaging) else '' end from inserted i left join deleted d on i.PickAccountId=d.PickAccountId;

insert into t_log (type,oplog,optime,opid) values (20,@log, CONVERT(varchar(100), GETDATE(), 120),0);
-----------------------------------------------------------------------------
t_m_TypePrice

TypePriceAddTrigger after insert

insert into t_log (type,oplog,optime,opid)
select top 1  30,'trigger-新增规格 GoodsTypeID='+convert(varchar(100),GoodsTypeID)+
',PickAccountId='+convert(varchar(100),PickAccountId)+',GoodsType='+convert(varchar(100),GoodsType)+
',number='+convert(varchar(100),number)+',typeName='+convert(varchar(100),typeName),CONVERT(varchar(100), GETDATE(), 120),0
from inserted

TypePriceUpdateTrigger after update

declare @log varchar(2000);
select top 1 @log='Trigger-修改规格: GoodsTypeID='+convert(varchar(100),GoodsTypeID) from inserted;
select top 1 @log+= case when i.PickAccountId<>d.PickAccountId then ' PickAccountId='+convert(varchar(100),i.PickAccountId) else '' end from inserted i left join deleted d on i.GoodsTypeID=d.GoodsTypeID;
select top 1 @log+= case when i.GoodsType<>d.GoodsType then ' GoodsType='+convert(varchar(100),i.GoodsType) else '' end from inserted i left join deleted d on i.GoodsTypeID=d.GoodsTypeID;
select top 1 @log+= case when i.Unit<>d.Unit then ' Unit='+convert(varchar(100),i.Unit) else '' end from inserted i left join deleted d on i.GoodsTypeID=d.GoodsTypeID;
select top 1 @log+= case when i.number<>d.number then ' number='+convert(varchar(100),i.number) else '' end from inserted i left join deleted d on i.GoodsTypeID=d.GoodsTypeID;
select top 1 @log+= case when i.typeName<>d.typeName then ' typeName='+convert(varchar(100),i.typeName) else '' end from inserted i left join deleted d on i.GoodsTypeID=d.GoodsTypeID;
insert into t_log (type,oplog,optime,opid) values (30,@log, CONVERT(varchar(100), GETDATE(), 120),0);
---------------------------------------------------------------------------------
t_m_transRecomFee

TransRecomFeeAddTrigger after insert

insert into t_log (type,oplog,optime,opid)
select top 1  50,'trigger-运费单价新增 transRecomFeeID='+convert(varchar(100),transRecomFeeID)+
',ClientID='+convert(varchar(100),ClientID)+',PickAddressId='+convert(varchar(100),PickAddressId)+
',RecommendFee='+convert(varchar(100),RecommendFee)+',StartTime='+convert(varchar(100),StartTime),CONVERT(varchar(100), GETDATE(), 120),0
from inserted

TransRecomFeeDelTrigger after delete

declare @log varchar(2000);
select top 1 @log='Trigger-运费单价删除: transRecomFeeID='+convert(varchar(100),transRecomFeeID) from deleted;
select top 1 @log+=',ClientID='+convert(varchar(100),d.ClientID)+',PickAddressId='+convert(varchar(100),d.PickAddressId)+',RecommendFee='+convert(varchar(100),d.RecommendFee)+
',StartTime='+convert(varchar(100),d.StartTime) from deleted as d
insert into t_log (type,oplog,optime,opid) values (50,@log, CONVERT(varchar(100), GETDATE(), 120),0);

TransRecomFeeUpdateTrigger after update

declare @log varchar(2000);
select top 1 @log='Trigger-运费单价编辑: transRecomFeeID='+convert(varchar(100),transRecomFeeID) from inserted;
select top 1 @log+= case when i.ClientID<>d.ClientID then ' ClientID='+convert(varchar(100),i.ClientID) else '' end from inserted i left join deleted d on i.transRecomFeeID=d.transRecomFeeID;
select top 1 @log+= case when i.PickAddressId<>d.PickAddressId then ' PickAddressId='+convert(varchar(100),i.PickAddressId) else '' end from inserted i left join deleted d on i.transRecomFeeID=d.transRecomFeeID;
select top 1 @log+= case when i.RecommendFee<>d.RecommendFee then ' RecommendFee='+convert(varchar(100),i.RecommendFee) else '' end from inserted i left join deleted d on i.transRecomFeeID=d.transRecomFeeID;
select top 1 @log+= case when i.StartTime<>d.StartTime then ' StartTime='+convert(varchar(100),i.StartTime) else '' end from inserted i left join deleted d on i.transRecomFeeID=d.transRecomFeeID;
insert into t_log (type,oplog,optime,opid) values (50,@log, CONVERT(varchar(100), GETDATE(), 120),0);

----------------------------------------------------------------------------------------------------------------------------
t_m_companyAccount

CompanyAccountAddTrigger after insert

insert into t_log (type,oplog,optime,opid)
select top 1  60,'trigger-公司账户新增 companyAccountID='+convert(varchar(100),companyAccountID)+
',name='+convert(varchar(100),name)+',accountNum='+convert(varchar(100),accountNum),CONVERT(varchar(100), GETDATE(), 120),0
from inserted

CompanyAccountDelTrigger after delete

declare @log varchar(2000);
select top 1 @log='Trigger-运费单价删除: companyAccountID='+convert(varchar(100),companyAccountID) from deleted;
insert into t_log (type,oplog,optime,opid) values (60,@log, CONVERT(varchar(100), GETDATE(), 120),0);

CompanyAccountUpdateTrigger after update

declare @log varchar(2000);
select top 1 @log='Trigger-公司账户编辑: companyAccountID='+convert(varchar(100),companyAccountID) from inserted;
select top 1 @log+= case when i.name<>d.name then ' name='+convert(varchar(100),i.name) else '' end from inserted i left join deleted d on i.companyAccountID=d.companyAccountID;
select top 1 @log+= case when i.accountNum<>d.accountNum then ' accountNum='+convert(varchar(100),i.accountNum) else '' end from inserted i left join deleted d on i.companyAccountID=d.companyAccountID;
insert into t_log (type,oplog,optime,opid) values (60,@log, CONVERT(varchar(100), GETDATE(), 120),0);

-----------------------------------------------------------------------------------------------------------------------------
T_M_recommSellPrice

RecommSellPriceAddTrigger after insert

insert into t_log (type,oplog,optime,opid)
select top 1  70,'trigger-推荐销售单价新增 id='+convert(varchar(100),id)+
',PickAccountId='+convert(varchar(100),PickAccountId)+',clientID='+convert(varchar(100),clientID)+',price='+convert(varchar(100),price)+',optime='+convert(varchar(100),optime)+',GoodsTypeID='+convert(varchar(100),GoodsTypeID),CONVERT(varchar(100), GETDATE(), 120),0
from inserted

RecommSellPriceDelTrigger after delete

declare @log varchar(2000);
select top 1 @log='Trigger-推荐销售单价删除: id='+convert(varchar(100),id) from deleted;
insert into t_log (type,oplog,optime,opid) values (70,@log, CONVERT(varchar(100), GETDATE(), 120),0);

RecommSellPriceUpdateTrigger after Update

declare @log varchar(2000);
select top 1 @log='Trigger-公司账户编辑: id='+convert(varchar(100),id) from inserted;
select top 1 @log+= case when i.PickAccountId<>d.PickAccountId then ' PickAccountId='+convert(varchar(100),i.PickAccountId) else '' end from inserted i left join deleted d on i.id=d.id;
select top 1 @log+= case when i.clientID<>d.clientID then ' clientID='+convert(varchar(100),i.clientID) else '' end from inserted i left join deleted d on i.id=d.id;
select top 1 @log+= case when i.price<>d.price then ' price='+convert(varchar(100),i.price) else '' end from inserted i left join deleted d on i.id=d.id;
select top 1 @log+= case when i.optime<>d.optime then ' optime='+convert(varchar(100),i.optime) else '' end from inserted i left join deleted d on i.id=d.id;
select top 1 @log+= case when i.GoodsTypeID<>d.GoodsTypeID then ' GoodsTypeID='+convert(varchar(100),i.GoodsTypeID) else '' end from inserted i left join deleted d on i.id=d.id;
insert into t_log (type,oplog,optime,opid) values (70,@log, CONVERT(varchar(100), GETDATE(), 120),0);

-----------------------------------------------------------------------------
t_m_Clientinformation

ClientInformationAddTrigger after insert

insert into t_log (type,oplog,optime,opid)
select top 1  80,'trigger-客户管理新增 clientID='+convert(varchar(100),clientID)+',clientName='+convert(varchar(100),clientName)+',number='+convert(varchar(100),number)+',getStuffAddress='+convert(varchar(100),getStuffAddress),CONVERT(varchar(100), GETDATE(), 120),0 from inserted

t_m_clientinformation_update after update
declare @log varchar(2000);
declare @cc int;
declare @UpFlag int;
select top 1 @log='Trigger-客户管理编辑: clientID='+convert(varchar(100),clientID) from inserted;
select top 1 @log+= case when i.contactNum<>d.contactNum then ' contactNum='+convert(varchar(100),i.contactNum) else '' end from inserted i left join deleted d on i.clientID=d.clientID;
select top 1 @log+= case when i.getStuffNum<>d.getStuffNum then ' getStuffNum='+convert(varchar(100),i.getStuffNum) else '' end from inserted i left join deleted d on i.clientID=d.clientID;
select top 1 @UpFlag= case when i.getStuffNum<>d.getStuffNum then 1 else 0 end from inserted i left join deleted d on i.clientID=d.clientID;
if (@UpFlag>0)
begin
update t_o_order set getstuffnum=a.getstuffnum from inserted a where a.clientid=t_o_order.clientID and OrderState<3;
                        set @UpFlag=0;
end
select top 1 @log+= case when i.clientName<>d.clientName then ' clientName='+convert(varchar(100),i.clientName) else '' end from inserted i left join deleted d on i.clientID=d.clientID;
select top 1 @log+= case when i.getStuffAddress<>d.getStuffAddress then ' getStuffAddress='+convert(varchar(100),i.getStuffAddress) else '' end from inserted i left join deleted d on i.clientID=d.clientID;
select top 1 @log+= case when i.packaging<>d.packaging then ' packaging='+convert(varchar(100),i.packaging) else '' end from inserted i left join deleted d on i.clientID=d.clientID;

select top 1 @UpFlag= case when i.isDelete<>d.isDelete then 1 else 0 end from inserted i left join deleted d on i.clientID=d.clientID;

if (@UpFlag>0)
begin
select top 1 @log='Trigger-客户管理删除: clientID='+convert(varchar(100),clientID) from deleted;
                        insert into t_log (type,oplog,optime,opid) values (80,@log, CONVERT(varchar(100), GETDATE(), 120),0);
                        set @UpFlag=0;

end
else
            begin
                        insert into t_log (type,oplog,optime,opid) values (80,@log, CONVERT(varchar(100), GETDATE(), 120),0);
            end

-------------------------------------------------------------------------------------------------
t_c_truck

TruckAddTrigger after insert

insert into t_log (type,oplog,optime,opid)
select top 1  130,'trigger-车辆管理新增 truckID='+convert(varchar(100),truckID)+
',ownerName='+convert(varchar(100),ownerName)+',phone='+convert(varchar(100),phone)+',PlateNum='+convert(varchar(100),PlateNum)+',carType='+convert(varchar(100),carType),CONVERT(varchar(100), GETDATE(), 120),0
from inserted

TruckUpdateTrigger after update

declare @log varchar(2000);
declare @UpFlag int;
select top 1 @log='Trigger-车辆管理编辑: truckID='+convert(varchar(100),truckID) from inserted;
select top 1 @log+= case when i.ownerName<>d.ownerName then ' ownerName='+convert(varchar(100),i.ownerName) else '' end from inserted i left join deleted d on i.truckID=d.truckID;
select top 1 @log+= case when i.phone<>d.phone then ' phone='+convert(varchar(100),i.phone) else '' end from inserted i left join deleted d on i.truckID=d.truckID;
select top 1 @log+= case when i.PlateNum<>d.PlateNum then ' PlateNum='+convert(varchar(100),i.PlateNum) else '' end from inserted i left join deleted d on i.truckID=d.truckID;
select top 1 @log+= case when i.carType<>d.carType then ' carType='+convert(varchar(100),i.carType) else '' end from inserted i left join deleted d on i.truckID=d.truckID;
select top 1 @log+= case when i.GPSIMEI<>d.GPSIMEI then ' GPSIMEI='+convert(varchar(100),i.GPSIMEI) else '' end from inserted i left join deleted d on i.truckID=d.truckID;
select top 1 @UpFlag= case when i.isDelete<>d.isDelete then 1 else 0 end from inserted i left join deleted d on i.truckID=d.truckID;
if (@UpFlag>0)
begin
select top 1 @log='Trigger-车辆管理删除: truckID='+convert(varchar(100),truckID) from deleted;
                        insert into t_log (type,oplog,optime,opid) values (130,@log, CONVERT(varchar(100), GETDATE(), 120),0);
                        set @UpFlag=0;
end
else
            begin
                        insert into t_log (type,oplog,optime,opid) values (130,@log, CONVERT(varchar(100), GETDATE(), 120),0);
            end

--------------------------------------------------------------------------------------------------
t_c_driver

DriverAddTrigger after insert

insert into t_log (type,oplog,optime,opid)
select top 1  120,'trigger-运输单位新增 driverID='+convert(varchar(100),driverID)+
',driverName='+convert(varchar(100),driverName)+',email='+convert(varchar(100),email)+',licensePassTime='+convert(varchar(100),licensePassTime),CONVERT(varchar(100), GETDATE(), 120),0
from inserted

DriverUpdateTrigger after update

declare @log varchar(2000);
select top 1 @log='Trigger-运输单位管理编辑: driverID='+convert(varchar(100),driverID) from inserted;
select top 1 @log+= case when i.driverName<>d.driverName then ' driverName='+convert(varchar(100),i.driverName) else '' end from inserted i left join deleted d on i.driverID=d.driverID;
select top 1 @log+= case when i.licensePassTime<>d.licensePassTime then ' licensePassTime='+convert(varchar(100),i.licensePassTime) else '' end from inserted i left join deleted d on i.driverID=d.driverID;
select top 1 @log+= case when i.email<>d.email then ' email='+convert(varchar(100),i.email) else '' end from inserted i left join deleted d on i.driverID=d.driverID;

insert into t_log (type,oplog,optime,opid) values (120,@log, CONVERT(varchar(100), GETDATE(), 120),0);

-----------------------------------------------------------------------------------------------------
t_c_driverOwnerBinding

DriverOwnerBindingAddTrigger after insert

insert into t_log (type,oplog,optime,opid)
select top 1  120,'trigger-司机车辆绑定新增 BindingID='+convert(varchar(100),BindingID)+
',DriverID='+convert(varchar(100),DriverID)+',TruckID='+convert(varchar(100),TruckID),CONVERT(varchar(100), GETDATE(), 120),0
from inserted

-----------------------------------------------------------------------------------------------------  3-4 21:00
t_o_demand

DemandUpdateTrigger after update

declare @log varchar(2000);
select top 1 @log='Trigger-需求修改 : demandID='+convert(varchar(100),demandID) from inserted;
select top 1 @log+= case when i.clientID<>d.clientID then ' clientID='+convert(varchar(100),i.clientID) else '' end from inserted i left join deleted d on i.demandID=d.demandID;
select top 1 @log+= case when i.pickAccount<>d.pickAccount then ' pickAccount='+convert(varchar(100),i.pickAccount) else '' end from inserted i left join deleted d on i.demandID=d.demandID;
select top 1 @log+= case when i.pickAddress<>d.pickAddress then ' pickAddress='+convert(varchar(100),i.pickAddress) else '' end from inserted i left join deleted d on i.demandID=d.demandID;
select top 1 @log+= case when i.state<>d.state then ' state='+convert(varchar(100),i.state) else '' end from inserted i left join deleted d on i.demandID=d.demandID;
select top 1 @log+= case when i.cementType<>d.cementType then ' cementType='+convert(varchar(100),i.cementType) else '' end from inserted i left join deleted d on i.demandID=d.demandID;
select top 1 @log+= case when i.price<>d.price then ' price='+convert(varchar(100),i.price) else '' end from inserted i left join deleted d on i.demandID=d.demandID;

insert into t_log (type,oplog,optime,opid) values (160,@log, CONVERT(varchar(100), GETDATE(), 120),0);

--------------------------------------------------------------------------------------------------------
t_o_demandCount

DemandCountUpdateTrigger after update

declare @log varchar(2000);
select top 1 @log='Trigger-DemandCount 更新: demandCountID='+convert(varchar(100),demandCountID) from inserted;
select top 1 @log+= case when i.Amount<>d.Amount then ' Amount='+convert(varchar(100),i.Amount) else '' end from inserted i left join deleted d on i.demandCountID=d.demandCountID;
select top 1 @log+= case when i.state<>d.state then ' state='+convert(varchar(100),i.state) else '' end from inserted i left join deleted d on i.demandCountID=d.demandCountID;

insert into t_log (type,oplog,optime,opid) values (160,@log, CONVERT(varchar(100), GETDATE(), 120),0);

--------------------------------------------------------------------------------------------------------------
t_c_ClientPayment

ClientPaymentAddTrigger after insert

insert into t_log (type,oplog,optime,opid)
select top 1  260,'trigger-客户回款新增 ClientPaymentID='+convert(varchar(100),ClientPaymentID)+
',ClientID='+convert(varchar(100),ClientID)+',companyAccountID='+convert(varchar(100),companyAccountID)+',PaymentMoney='+convert(varchar(100),PaymentMoney)
+',PaymentDate='+convert(varchar(100),PaymentDate)+',memo='+convert(varchar(100),memo)+',type='+convert(varchar(100),type),CONVERT(varchar(100), GETDATE(), 120),0
from inserted

ClientPaymentDelTrigger after delete

declare @log varchar(2000);
select top 1 @log='Trigger-客户回款删除: ClientPaymentID='+convert(varchar(100),ClientPaymentID) from deleted;
insert into t_log (type,oplog,optime,opid) values (260,@log, CONVERT(varchar(100), GETDATE(), 120),0);

ClientPaymentUpdateTrigger after Update

declare @log varchar(2000);
select top 1 @log='Trigger-客户回款编辑: ClientPaymentID='+convert(varchar(100),ClientPaymentID) from inserted;
select top 1 @log+= case when i.ClientID<>d.ClientID then ' ClientID='+convert(varchar(100),i.ClientID) else '' end from inserted i left join deleted d on i.ClientPaymentID=d.ClientPaymentID;
select top 1 @log+= case when i.PaymentMoney<>d.PaymentMoney then ' PaymentMoney='+convert(varchar(100),i.PaymentMoney) else '' end from inserted i left join deleted d on i.ClientPaymentID=d.ClientPaymentID;
select top 1 @log+= case when i.PaymentDate<>d.PaymentDate then ' PaymentDate='+convert(varchar(100),i.PaymentDate) else '' end from inserted i left join deleted d on i.ClientPaymentID=d.ClientPaymentID;
select top 1 @log+= case when i.companyAccountID<>d.companyAccountID then ' companyAccountID='+convert(varchar(100),i.companyAccountID) else '' end from inserted i left join deleted d on i.ClientPaymentID=d.ClientPaymentID;
select top 1 @log+= case when i.memo<>d.memo then ' memo='+convert(varchar(100),i.memo) else '' end from inserted i left join deleted d on i.ClientPaymentID=d.ClientPaymentID;
select top 1 @log+= case when i.type<>d.type then ' type='+convert(varchar(100),i.type) else '' end from inserted i left join deleted d on i.ClientPaymentID=d.ClientPaymentID;
insert into t_log (type,oplog,optime,opid) values (260,@log, CONVERT(varchar(100), GETDATE(), 120),0);

---------------------------------------------------------------------------------------------------------------------------
t_c_AccountPayment

AccountPaymentAddTrigger after insert

insert into t_log (type,oplog,optime,opid)
select top 1  270,'trigger-账户付款新增 AccountPaymentID='+convert(varchar(100),AccountPaymentID)+
',PickAccountID='+convert(varchar(100),PickAccountID)+',PaymentMoney='+convert(varchar(100),PaymentMoney)+',PaymentDate='+convert(varchar(100),PaymentDate)
+',companyAccountID='+convert(varchar(100),companyAccountID)+',memo='+convert(varchar(100),memo)+',type='+convert(varchar(100),type),CONVERT(varchar(100), GETDATE(), 120),0
from inserted

AccountPaymentDelTrigger after Delete

declare @log varchar(2000);
select top 1 @log='Trigger-账户付款删除: AccountPaymentID='+convert(varchar(100),AccountPaymentID) from deleted;
insert into t_log (type,oplog,optime,opid) values (270,@log, CONVERT(varchar(100), GETDATE(), 120),0);

AccountPaymentUpdateTrigger after Update

declare @log varchar(2000);
select top 1 @log='Trigger-账户付款编辑: AccountPaymentID='+convert(varchar(100),AccountPaymentID) from inserted;
select top 1 @log+= case when i.PickAccountID<>d.PickAccountID then ' PickAccountID='+convert(varchar(100),i.PickAccountID) else '' end from inserted i left join deleted d on i.AccountPaymentID=d.AccountPaymentID;
select top 1 @log+= case when i.PaymentMoney<>d.PaymentMoney then ' PaymentMoney='+convert(varchar(100),i.PaymentMoney) else '' end from inserted i left join deleted d on i.AccountPaymentID=d.AccountPaymentID;
select top 1 @log+= case when i.PaymentDate<>d.PaymentDate then ' PaymentDate='+convert(varchar(100),i.PaymentDate) else '' end from inserted i left join deleted d on i.AccountPaymentID=d.AccountPaymentID;
select top 1 @log+= case when i.companyAccountID<>d.companyAccountID then ' companyAccountID='+convert(varchar(100),i.companyAccountID) else '' end from inserted i left join deleted d on i.AccountPaymentID=d.AccountPaymentID;
select top 1 @log+= case when i.memo<>d.memo then ' memo='+convert(varchar(100),i.memo) else '' end from inserted i left join deleted d on i.AccountPaymentID=d.AccountPaymentID;
select top 1 @log+= case when i.type<>d.type then ' type='+convert(varchar(100),i.type) else '' end from inserted i left join deleted d on i.AccountPaymentID=d.AccountPaymentID;
insert into t_log (type,oplog,optime,opid) values (270,@log, CONVERT(varchar(100), GETDATE(), 120),0);

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

SQLTrigger.rar (2.61 KB, 下载次数: 0)

拟定修改目录日志.rar (7.9 KB, 下载次数: 0)

作者: 李维强-15级 时间: 2017-3-7 10:50
本帖最后由李维强-15级于 2017-3-7 12:59 编辑

[SQL] syntaxhighlighter_viewsource syntaxhighlighter_copycode





select case when d.DecuctNum is NOT null 

then 

      case when CONVERT(DECIMAL(18,2),a.produceNum-a.VerifiedNum)>d.DecuctNum then CONVERT(DECIMAL(18,2),(a.produceNum-a.VerifiedNum)*a.FactoryPrice) else 0 end

else 

      case when a.CementType LIKE '%散%' AND  CONVERT(DECIMAL(18,2),a.produceNum-a.VerifiedNum)>=0.2      

           THEN

                CONVERT(DECIMAL(18,2),(a.produceNum-a.VerifiedNum)*a.FactoryPrice)

           WHEN a.CementType LIKE '%散%' AND  CONVERT(DECIMAL(18,2),a.produceNum-a.VerifiedNum)<0.2

           THEN 

                0

           ELSE

                CONVERT(DECIMAL(18,2),(a.produceNum-a.VerifiedNum)*a.FactoryPrice)

      END 

END as decountFee,

CONVERT(DECIMAL(18,2),-1*(a.produceNum-a.VerifiedNum)) as costNum,

d.DecuctNum,a.VerifiedNum*a.TransPrice as HJJE,

a.FactoryTime,b.driverName,c.PlateNum,a.PickAddress,a.ClientName,a.CementType,a.produceNum,a.VerifiedNum,a.TransPrice

,a.FactoryGetNum,NULL AS YFDetails,NULL AS AccountName,a.Remark,d.state,'吨' as unit





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

LEFT JOIN T_M_transFeeAudit d ON a.transFeeAuditNum=d.auditNum



where  a.OrderState=4 AND a.FactoryTime BETWEEN '2017-02-01 21:53:03' AND '2017-03-15 21:53:03'





UNION 

SELECT NULL AS decountFee,NULL as costNum,NULL AS DecuctNum,NULL as HJJE,payDate as FactoryTime,a.driverName,NULL as PlateNum,NULL as PickAddress,NULL AS ClientName,NULL AS CementType,NULL AS produceNum,NULL AS VerifiedNum,NULL AS TransPrice,c.transFeeNum AS FactoryGetNum,Paymoney AS YFDetails,b.name AS AccountName,Remark,NULL AS state,NULL as unit

from T_M_transFeeDetails c INNER JOIN T_M_companyAccount b ON c.companyAccountID=b.companyAccountID

LEFT JOIN (SELECT driverID,transFeeNum,driverName FROM transFeeView GROUP BY transFeeNum,driverName,driverID) AS a ON a.transFeeNum=c.transFeeNum



where c.payDate BETWEEN '2017-02-01 21:53:03' AND '2017-03-15 21:53:03'



ORDER BY FactoryTime ASC

作者: 李维强-15级 时间: 2017-3-7 21:39

[SQL] syntaxhighlighter_viewsource syntaxhighlighter_copycode

select SUM(a.VerifiedNum*a.TransPrice)-SUM(case when d.DecuctNum is NOT null

then

      case when CONVERT(DECIMAL(18,2),a.produceNum-a.VerifiedNum)>d.DecuctNum then CONVERT(DECIMAL(18,2),(a.produceNum-a.VerifiedNum)*a.FactoryPrice) else 0 end

else

      case when a.CementType LIKE '%散%' AND  CONVERT(DECIMAL(18,2),a.produceNum-a.VerifiedNum)>=0.2      

           THEN

                CONVERT(DECIMAL(18,2),(a.produceNum-a.VerifiedNum)*a.FactoryPrice)

           WHEN a.CementType LIKE '%散%' AND  CONVERT(DECIMAL(18,2),a.produceNum-a.VerifiedNum)<0.2

           THEN

                0

           ELSE

                CONVERT(DECIMAL(18,2),(a.produceNum-a.VerifiedNum)*a.FactoryPrice)

      END

END) as ToPay,

b.driverName,left(a.FactoryTime,10) as DayTime,NULL AS HasPay





FROM T_O_Order a INNER JOIN T_C_Driver b ON a.DriverID=b.driverID LEFT JOIN T_M_transFeeAudit d ON a.transFeeAuditNum=d.auditNum

where a.TransPrice<>0 AND a.OrderState=4 AND a.FactoryTime BETWEEN '2017-02-01 21:53:03' AND '2017-03-15 21:53:03'

GROUP BY b.driverName,left(a.FactoryTime,10)



UNION



SELECT NULL AS ToPay,a.driverName,left(c.payDate,10) as DayTime,SUM(c.Paymoney) as HasPay

from T_M_transFeeDetails c INNER JOIN T_M_companyAccount b ON c.companyAccountID=b.companyAccountID

LEFT JOIN (SELECT driverID,transFeeNum,driverName FROM transFeeView GROUP BY transFeeNum,driverName,driverID) AS a ON a.transFeeNum=c.transFeeNum



where c.payDate BETWEEN '2017-02-01 21:53:03' AND '2017-03-15 21:53:03'

GROUP BY a.driverName,left(c.payDate,10)



ORDER BY DayTime

作者: 李维强-15级 时间: 2018-3-27 10:34
分页查询

[C#] syntaxhighlighter_viewsource syntaxhighlighter_copycode

                sql1 += " select top " + rows.ToString() + " * from ";

                sql2 += " ( SELECT ROW_NUMBER() OVER (order by " + sort + " " + order + ") as rowNumber,ISNULL(SUM(b.quantity),0) AS dispatchNum, a.demandCountID,a.dstate,a.salesmanName,a.clientName,a.GoodsType,a.Amount,a.alreadyTransport,a.sransportType,a.state,a.releaseDate";

                sql2 += " FROM DemandCountDetails a LEFT JOIN T_O_Order b ON a.demandcountid=b.demandcountid";

                sql2 += " where " + condition + " GROUP BY a.demandCountID,a.dstate,a.salesmanName,a.clientName,a.GoodsType,a.Amount,a.alreadyTransport,a.sransportType,a.state,a.releaseDate)as s";

                sql3 += " where rownumber>" + (rows * (page - 1)).ToString();

[SQL] syntaxhighlighter_viewsource syntaxhighlighter_copycode

select top 20 * from  ( SELECT ROW_NUMBER() OVER (order by a.demandCountID desc) as rowNumber,ISNULL(SUM(b.quantity),0) AS dispatchNum, 

a.demandCountID,a.dstate,a.salesmanName,a.clientName,a.GoodsType,a.Amount,a.alreadyTransport,a.sransportType,a.state,a.releaseDate FROM DemandCountDetails a LEFT JOIN T_O_Order b ON 

a.demandcountid=b.demandcountid where  1=1 and state=0 and companyManageID='2' and dstate=3 GROUP BY 

a.demandCountID,a.dstate,a.salesmanName,a.clientName,a.GoodsType,a.Amount,a.alreadyTransport,a.sransportType,a.state,a.releaseDate)as s where rownumber>0

作者: 李维强-15级 时间: 2018-6-7 04:53

环球

SELECT top 20 * FROM (
SELECT ArticleId,Title1,b.AuthorName,PostTime,'article' as srcType from t_d_article a INNER JOIN t_d_author b ON a.AuthorId=b.AuthorId where a.IsDelete<>1 AND Title1 LIKE '%法%'
UNION
SELECT a.pictureid,a.name,b.AuthorName,PostTime,'picture' FROM t_d_picture a INNER JOIN t_d_author b ON a.AuthorId=b.AuthorId where a.IsDelete<>1 AND Name LIKE '%法%'
UNION
SELECT splistid,title,b.authorname,posttime,'SP' FROM t_d_SPList a INNER JOIN t_d_author b ON a.AuthorId=b.AuthorId where a.Title LIKE '%法%'
UNION
--作者
SELECT ArticleId,Title1,b.AuthorName,PostTime,'article' as srcType from t_d_article a INNER JOIN t_d_author b ON a.AuthorId=b.AuthorId where a.IsDelete<>1 AND b.AuthorName LIKE '%法%'
UNION
SELECT a.pictureid,a.name,b.AuthorName,PostTime,'picture' FROM t_d_picture a INNER JOIN t_d_author b ON a.AuthorId=b.AuthorId where a.IsDelete<>1 AND b.AuthorName LIKE '%法%'
UNION
SELECT splistid,title,b.authorname,posttime,'SP' FROM t_d_SPList a INNER JOIN t_d_author b ON a.AuthorId=b.AuthorId where b.AuthorName LIKE '%法%'
) AS s
GROUP BY Articleid,title1,authorname,posttime,srctype
ORDER BY posttime DESC

差关键词

作者: 李维强-15级 时间: 2018-6-7 23:15
本帖最后由李维强-15级于 2018-6-8 00:35 编辑

SELECT top 20 * FROM (
SELECT ArticleId,Title1,b.AuthorName,PostTime,'article' as srcType,'' as test
from t_d_article a INNER JOIN t_d_author b ON a.AuthorId=b.AuthorId
where a.IsDelete<>1 AND Title1 LIKE '%法%'
UNION
SELECT a.pictureid,a.name,b.AuthorName,PostTime,'picture',''
FROM t_d_picture a INNER JOIN t_d_author b ON a.AuthorId=b.AuthorId
where a.IsDelete<>1 AND Name LIKE '%法%'
UNION
SELECT splistid,title,b.authorname,posttime,'SP' as srcType,''
FROM t_d_SPList a INNER JOIN t_d_author b ON a.AuthorId=b.AuthorId
where a.Title LIKE '%法%'
UNION
--作者
SELECT ArticleId,Title1,b.AuthorName,PostTime,'article',''
from t_d_article a INNER JOIN t_d_author b ON a.AuthorId=b.AuthorId
where a.IsDelete<>1 AND b.AuthorName LIKE '%法%'
UNION
SELECT a.pictureid,a.name,b.AuthorName,PostTime,'picture',''
FROM t_d_picture a INNER JOIN t_d_author b ON a.AuthorId=b.AuthorId
where a.IsDelete<>1 AND b.AuthorName LIKE '%法%'
UNION
SELECT splistid,title,b.authorname,posttime,'SP',''
FROM t_d_SPList a INNER JOIN t_d_author b ON a.AuthorId=b.AuthorId
where b.AuthorName LIKE '%法%'
--关键词
UNION
SELECT b.ArticleId,c.Title1,d.AuthorName,c.PostTime,'article' as srcType,''
FROM t_d_keyword a INNER JOIN t_d_KWRelation b ON a.KeyWordId=b.KeyWordId
INNER JOIN t_d_article c ON c.ArticleId=b.ArticleId
INNER JOIN t_d_author d ON c.AuthorId=d.AuthorId
where a.Name like '%法%' AND a.type=1

) AS s
GROUP BY Articleid,title1,authorname,posttime,srctype,test
ORDER BY posttime DESC

SELECT REPLACE((SELECT STUFF((SELECT name FROM t_d_keyword where Name like '%法%' for xml path('')),1,0,'')),'<name>','')

以下为最终版，关键是 LIKE '%法%'-----------------------------------------------------------------
SELECT top 20 * FROM (
SELECT a.ArticleId,Title1,b.AuthorName,PostTime,'article' as srcType,
REPLACE((SELECT STUFF((SELECT name FROM t_d_keyword x INNER JOIN t_d_KWRelation y ON x.KeyWordId=y.KeyWordId  where y.ArticleId=a.ArticleId for xml path('')),1,0,'')),'<name>','') as kw
from t_d_article a INNER JOIN t_d_author b ON a.AuthorId=b.AuthorId
where a.IsDelete<>1 AND Title1 LIKE '%法%'
UNION
SELECT a.pictureid,a.name,b.AuthorName,PostTime,'picture' as srcType,
REPLACE((SELECT STUFF((SELECT name FROM t_d_keyword x INNER JOIN t_d_KWRelation y ON x.KeyWordId=y.KeyWordId  where y.PictureId=a.PictureId for xml path('')),1,0,'')),'<name>','') as kw
FROM t_d_picture a INNER JOIN t_d_author b ON a.AuthorId=b.AuthorId
where a.IsDelete<>1 AND Name LIKE '%法%'
UNION
SELECT splistid,title,b.authorname,posttime,'SP' as srcType,''
FROM t_d_SPList a INNER JOIN t_d_author b ON a.AuthorId=b.AuthorId
where a.Title LIKE '%法%'
UNION
--作者
SELECT a.ArticleId,Title1,b.AuthorName,PostTime,'article' as srcType,
REPLACE((SELECT STUFF((SELECT name FROM t_d_keyword x INNER JOIN t_d_KWRelation y ON x.KeyWordId=y.KeyWordId  where y.ArticleId=a.ArticleId for xml path('')),1,0,'')),'<name>','') as kw
from t_d_article a INNER JOIN t_d_author b ON a.AuthorId=b.AuthorId
where a.IsDelete<>1 AND b.AuthorName LIKE '%法%'
UNION
SELECT a.pictureid,a.name,b.AuthorName,PostTime,'picture' as srcType,
REPLACE((SELECT STUFF((SELECT name FROM t_d_keyword x INNER JOIN t_d_KWRelation y ON x.KeyWordId=y.KeyWordId  where y.PictureId=a.PictureId for xml path('')),1,0,'')),'<name>','') as kw
FROM t_d_picture a INNER JOIN t_d_author b ON a.AuthorId=b.AuthorId
where a.IsDelete<>1 AND b.AuthorName LIKE '%法%'
UNION
SELECT splistid,title,b.authorname,posttime,'SP' as srcType,''
FROM t_d_SPList a INNER JOIN t_d_author b ON a.AuthorId=b.AuthorId
where b.AuthorName LIKE '%法%'
--关键词
UNION
SELECT b.ArticleId,c.Title1,d.AuthorName,c.PostTime,'article' as srcType,
REPLACE((SELECT STUFF((SELECT name FROM t_d_keyword x INNER JOIN t_d_KWRelation y ON x.KeyWordId=y.KeyWordId  where y.ArticleId=b.ArticleId for xml path('')),1,0,'')),'<name>','') as kw
FROM t_d_keyword a INNER JOIN t_d_KWRelation b ON a.KeyWordId=b.KeyWordId
INNER JOIN t_d_article c ON c.ArticleId=b.ArticleId
INNER JOIN t_d_author d ON c.AuthorId=d.AuthorId
where a.Name like '%法%' AND a.type=1
UNION
SELECT b.pictureid,c.name,d.AuthorName,c.PostTime,'picture' as srcType,
REPLACE((SELECT STUFF((SELECT name FROM t_d_keyword x INNER JOIN t_d_KWRelation y ON x.KeyWordId=y.KeyWordId  where y.PictureId=b.PictureId for xml path('')),1,0,'')),'<name>','') as kw
FROM t_d_keyword a INNER JOIN t_d_KWRelation b ON a.KeyWordId=b.KeyWordId
INNER JOIN t_d_picture c ON c.pictureid=b.pictureid
INNER JOIN t_d_author d ON c.AuthorId=d.AuthorId
where a.Name like '%法%' AND a.type=2

) AS s
GROUP BY Articleid,title1,authorname,posttime,srctype,kw
ORDER BY posttime DESC

作者: 李维强-15级 时间: 2019-5-4 17:05
这个例子其实是非常的简单了就是要实现sql插入数据时，如果已经存在，则执行update更新了，这样对于sql与程序来讲是非常的简洁的，下面就和小编一起来深入的分析一下吧．
在很多项目中，我们需要对数据进行不断的调用和更新，如果有新的数据过来，还要把它加入到数据库中。其中非常重要的一种情况就是，我们不知道传过来的数据是不是原本数据库中就已经有了的记录，所以我们常常需要先通过抓取数据，判断是否存在，如果存在执行update，如果不存在执行insert，这样就要进行两次数据库操作，第一次是查询，第二次是更新或插入，有没有一种方法可以只需要执行一次操作即可呢？答案是有的。

INSERT ... ON DUPLICATE KEY UPDATE 方法可以帮助我们非常好的解决这一问题。让我们来看一个例子：

INSERT INTO table (a,b,c) VALUES (1,2,3)  ON DUPLICATE KEY UPDATE b=b-1,c=c+1;

这一个语句就可以实现，在插入(1,2,3)这条记录的时候，检查是否存在a=1，如果有a=1的记录，那么更新a=1这条记录，相当于执行了一次下面这个语句：

UPDATE table SET b=b-1,c=c+1 WHERE a=1;

如果数据库中不存在a=1这条记录，那么就插入新的记录。

使用INSERT ... ON DUPLICATE KEY UPDATE的前提是，a字段被设置为“唯一键”索引，否则该方法是无效的。

再看一些例子

该语句是基于唯一索引或主键使用，比如一个字段a被加上了unique index，并且表中已经存在了一条记录值为1，下面两个语句会有相同的效果：

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

UPDATE table SET c=c+1 WHERE a=1;

ON DUPLICATE KEY UPDATE后面可以放多个字段，用英文逗号分割。使用ON DUPLICATE KEY UPDATE，最终如果插入了一个新行，则受影响的行数是1，如果修改了已存在的一行数据，则受影响的行数是2。

如果字段b也被加上了unique index，则该语句和下面的update语句是等效的：

UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;

如果a=1 OR b=2匹配了多行，则只有一行会被修改。通常的，在ON DUPLICATE KEY UPDATE语句中，我们应该避免多个唯一索引的情况。如果需要插入或更新多条数据，并且更新的字段需要根据其它字段来运算时，可以使用如下语句：

INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)
  ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);
   在ON DUPLICATE KEY UPDATE后面使用VALUES()方法，这个语句等同于下面的两个语句：

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=3;--1+2
INSERT INTO table (a,b,c) VALUES (4,5,6)
  ON DUPLICATE KEY UPDATE c=9;--4+5

如果一个表中包含了一个auto_increment的字段，每次insert数据后，可以通过last_insert_id()方法返回最后自动生成的值，如果通过INSERT ... ON DUPLICATE KEY UPDATE语句修改了一条数据，那么再通过last_insert_id()方法获取的值将不正确，实际测试中是多了一个数，比如向表中增加了3条数据，那么通过last_insert_id()方法得到的值是3，但是通过该语句修改了一条数据后，通过last_insert_id()方法得到的值是4。如果想解决该问题，可以通过如下语句：

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), c=3;
   重点是这句id=LAST_INSERT_ID(id)。

还有一种方法是使用ignore，我们来看一个例子：

INSERT ignore INTO a(id, type)  VALUES  ( 11, 22)

这个语句中使用了ignore，意思是：如果数据库中存在一条记录id=11，那么就不执行insert操作（忽略），只有上述条件不满足时才执行插入操作。ignore方法也要求这里的id为唯一键（主键默认就是是唯一键，因此id可以是主键）

另外，还有一种方法是replace into，它的使用方法和insert into一样，但是和上面的ignore效果不同，如果数据库中已经存在id=11，那么强制替换id=11这条记录的type为22。

看个例子

下面通过代码说明之间的区别，如下：

create table testtb(
id int not null primary key,
name varchar(50),
age int
);
insert into testtb(id,name,age)values(1,"bb",13);
select * from testtb;
insert ignore into testtb(id,name,age)values(1,"aa",13);
select * from testtb;//仍是1，“bb”,13，因为id是主键，出现主键重复但使用了ignore则错误被忽略
replace into testtb(id,name,age)values(1,"aa",12);
select * from testtb; //数据变为1,"aa",12

总结一下：

如果要实现插入数据时检查是否已经存在某个唯一键的数据，如果存在，则替换该记录的其他字段，我们可以使用三种方法来实现插入数据时判断是否存在对应键的记录，分别是INSERT ... ON DUPLICATE KEY UPDATE、insert gnore into和replace into。其中INSERT ... ON DUPLICATE KEY UPDATE和replace into可以实现如果已经存在对应键的记录时，替换该记录的其他字段。

作者: 李维强-15级 时间: 2019-8-24 00:46
本帖最后由李维强-15级于 2019-8-24 00:58 编辑

在sqlserver2005之前，要实现递归功能比较麻烦，比如可能会要用到临时表与while语句来循环。自sqlserver2005之后，新增了with as功能语法，即公用表达式(CTE),让递归实现起来变的简单了。

本章我们主要演示如何利用with as功能实现一个简单的递归功能。
在这之前先看一下cte的语法：

[ WITH <common_table_expression > [ ,...n ] ]
<common_table_expression >::=
expression_name [ ( column_name [ ,...n ] ) ]
AS
( CTE_query_definition )
参数说明：
expression_name:
公用表表达式的有效标识符。 expression_name 必须与在同一 WITH <common_table_expression > 子句中定义的任何其他公用表表达式的名称不同，但 expression_name 可以与基表或基视图的名称相同。在查询中对 expression_name 的任何引用都会使用公用表表达式，而不使用基对象。

column_name:
在公用表表达式中指定列名。在一个 CTE 定义中不允许出现重复的名称。指定的列名数必须与CTE_query_definition结果集中列数匹配。只有在查询定义中为所有结果列都提供了不同的名称时，列名称列表才是可选的。

CTE_query_definition:
指定一个其结果集填充公用表表达式的 SELECT 语句。除了 CTE 不能定义另一个 CTE 以外，CTE_query_definition的 SELECT 语句必须满足与创建视图时相同的要求。
如果定义了多个 CTE_query_definition，则这些查询定义必须用下列一个集合运算符联接起来：UNION ALL、UNION、EXCEPT 或 INTERSECT。

--开始实例演示--

先创建一个仓库表，表名为Storage_Depository，该表有三个字段

ID(仓库编号),DName(仓库名称),PID(父仓库编号).
通过这样一个简单表，就可以将所有仓库信息，通过DID与PID字段来创建一个树型结构。
创建表的sql语句：

Create table Storage_Depository
(
DID varchar(50) not null primary key,
DName varchar(50) not null,
PID varchar(50) null
)
然后往该表插入演示数据：

insert into Storage_Depository(DID,DName,PID)
select 'A','A仓库',null
union all
select 'A-1','A-1仓库','A'
union all
select 'A-2','A-2仓库','A'
union all
select 'A-1-1','A-1-1仓库','A-1'
union all
select 'B','B仓库',null
从上面的数据可以看的出来，A的子仓为A-1与A-2仓，而A-1-1为A-1的子仓，B仓是一个独立的仓库，与A仓平级。
下面，我们通过with as功能，查出A仓下面的所有子仓：

with w_Storage_Depository as
(
select DID,DName,PID from Storage_Depository where DID='A'
union all
select A.DID,A.DName,A.PID from Storage_Depository A,w_Storage_Depository B where A.PID=B.DID
)
select * from w_Storage_Depository
代码很简短，也非常容易让人理解.

反过来，比如我们要查出A-1-1仓的所有上级仓，稍稍改一下上面的sql语句就可以了：

with w_Storage_Depository as
(
select DID,DName,PID from Storage_Depository where DID='A-1-1'
union all
select A.DID,A.DName,A.PID from Storage_Depository A,
w_Storage_Depository B where A.DID=B.PID
)
select * from w_Storage_Depository
很简单吧，熟练使用CTE后，会发现它会给我们的工作带来很大的便利.
--------------------------------------------------

[SQL] syntaxhighlighter_viewsource syntaxhighlighter_copycode

BEGIN 

with cte as(

select UserName,PhoneNum,UpUserId,UserId 

FROM t_d_user where UserId=5

union ALL

SELECT a.UserName,a.PhoneNum,a.UpUserId,a.UserId 

FROM t_d_User a INNER JOIN cte b ON a.UserId=b.UpUserId

)

SELECT * FROM cte

END

作者: 李维强-15级 时间: 2019-11-23 12:19
exists : 强调的是是否返回结果集，不要求知道返回什么, 比如：
  select name from student where sex = 'm' and mark exists(select 1 from grade where ...) ,只要
exists引导的子句有结果集返回，那么exists这个条件就算成立了,大家注意返回的字段始终为1，如果改成“select 2 from grade where ...”，那么返回的字段就是2，这个数字没有意义。所以exists子句不在乎返回什么，而是在乎是不是有结果集返回。

而 exists 与 in 最大的区别在于 in引导的子句只能返回一个字段，比如：
  select name from student where sex = 'm' and mark in (select 1,2,3 from grade where ...)
，in子句返回了三个字段，这是不正确的，exists子句是允许的，但in只允许有一个字段返回，在1，2，3中随便去了两个字段即可。

而not exists 和not in 分别是exists 和 in 的对立面。

exists （sql 返回结果集为真）
not exists (sql 不返回结果集为真）

下面详细描述not exists的过程：

如下：
表A
ID NAME
1 A1
2 A2
3 A3

表B
ID AID NAME
1 1    B1
2 2    B2
3 2    B3

表A和表B是１对多的关系 A.ID => B.AID

SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE A.ID=B.AID)
执行结果为
1 A1
2 A2
原因可以按照如下分析
SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=１)
--->SELECT * FROM B WHERE B.AID=１有值返回真所以有数据

SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=2)
--->SELECT * FROM B WHERE B.AID=２有值返回真所以有数据

SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=3)
--->SELECT * FROM B WHERE B.AID=３无值返回真所以没有数据

NOT EXISTS 就是反过来
SELECT ID,NAME FROM A WHERE　NOT EXIST (SELECT * FROM B WHERE A.ID=B.AID)
执行结果为
3 A3
===========================================================================
EXISTS = IN,意思相同不过语法上有点点区别，好像使用IN效率要差点，应该是不会执行索引的原因
SELECT ID,NAME FROM A　 WHERE　ID IN (SELECT AID FROM B)

NOT EXISTS = NOT IN ,意思相同不过语法上有点点区别
SELECT ID,NAME FROM A WHERE　ID　NOT IN (SELECT AID FROM B)

有时候我们会遇到要选出某一列不重复,某一列作为选择条件,其他列正常输出的情况.

如下面的表table:

Id  Name  Class Count  Date

1 苹果水果 10    2011-7-1

1 桔子水果 20    2011-7-2

1 香蕉水果 15    2011-7-3

2 白菜蔬菜 12    2011-7-1

2 青菜蔬菜 19    2011-7-2

如果想要得到下面的结果

Id唯一,Date选最近的一次)

1 香蕉水果 15    2011-7-3

2 青菜蔬菜 19    2011-7-2

正确的SQL语句是:

SELECT Id, Name, Class, Count, Date
FROM table t
WHERE (NOT EXISTS
      (SELECT Id, Name, Class, Count, Date FROM table
      WHERE Id = t.Id AND Date > t.Date))

如果用distinct,得不到这个结果, 因为distinct是作用与所有列的

SELECT DISTINCT Id, Name, Class, Count, Date FROM table

结果是表table的所有不同列都显示出来,如下所示:

1 苹果    水果 10    2011-7-1

1 桔子水果 20    2011-7-2

1 香蕉水果 15    2011-7-3

2 白菜蔬菜 12    2011-7-1

2 青菜蔬菜 19    2011-7-2

如果用Group by也得不到需要的结果,因为Group by 要和聚合函数共同使用,所以对于Name,Class和Count列要么使用Group by,要么使用聚合函数. 如果写成

SELECT Id, Name, Class, Count, MAX(Date)
FROM table
GROUP BY Id, Name, Class, Count

得到的结果是

1 苹果    水果 10 2011-7-1

1 桔子水果 20    2011-7-2

1 香蕉水果 15    2011-7-3

2 白菜蔬菜 12    2011-7-1

2 青菜蔬菜 19    2011-7-2

如果写成

SELECT Id, MAX(Name), MAX(Class), MAX(Count), MAX(Date)
FROM table
GROUP BY Id

得到的结果是:

1 香蕉水果 20    2011-7-3

2 青菜蔬菜 19    2011-7-2

如果用in有时候也得不到结果,（有的时候可以得到，如果Date都不相同（没有重复数据），或者是下面得到的Max（Date）只有一个值）

SELECT DISTINCT Id, Name, Class, Count, Date FROM table

WHERE (Date IN
      (SELECT MAX(Date)
      FROM table
      GROUP BY Id))

得到的结果是：（因为MAX(Date)有两个值2011-7-2，2011-7-3）

1 桔子水果 20    2011-7-2

1 香蕉水果 15    2011-7-3

2 青菜蔬菜 19    2011-7-2

注意in只允许有一个字段返回

有一种方法可以实现：

SELECT Id, Name, Class, COUNT, Date
FROM table1 t
WHERE (Date =
      (SELECT MAX(Date)
      FROM table1
      WHERE Id = t .Id))

作者: 李维强-15级 时间: 2022-8-2 15:33
在不知道表名、字段名的情况下，只知道某个值，查找出关于这个值所在的表与字段的位置

以下在ZZWine这个数据库测试的，应用时需要更换数据库名称

[SQL] syntaxhighlighter_viewsource syntaxhighlighter_copycode



declare @key varchar(30)

set @key = '李维强' --替换为要查找的字符串

DECLARE @tabName VARCHAR(40),@colName VARCHAR(40)

DECLARE @sql VARCHAR(2000)

declare @tsql varchar(8000)

DECLARE tabCursor CURSOR FOR

SELECT name from ZZWine.dbo.sysobjects WHERE xtype = 'u' AND name <> 'dtproperties'

OPEN tabCursor

FETCH NEXT from tabCursor INTO @tabName

WHILE @@fetch_status = 0

BEGIN

set @tsql = ''

DECLARE colCursor CURSOR FOR Select Name from SysColumns Where id=Object_Id(@tabName) --and xtype=167

OPEN colCursor

FETCH NEXT from colCursor INTO @colName

WHILE @@fetch_status = 0

BEGIN

SET @sql = 'if(exists(select * from ' + @tabName + ' where '

SET @sql = @sql + @colName + ' like ''%' + @key + '%'')) begin select * from '

set @sql = @sql + @tabName + ' where ' + @colName + ' like ''%' + @key + '%'';select '''

+ @tabName + ''' as TableName end'

set @tsql = @tsql + @sql + ';'

print @tsql

FETCH NEXT from colCursor INTO @colName

END

exec(@tsql)

CLOSE colCursor

DEALLOCATE colCursor

FETCH NEXT from tabCursor INTO @tabName

END

CLOSE tabCursor

DEALLOCATE tabCursor

欢迎光临重工电子论坛 (http://cqutlab.cn/)