sql语句积累，优化增删改查,提高效率.

    技术QQ交流群:294088839

 1.        例：  

               //拼接sql语句 减少数据库压力

                $sql="insert into aa_cc(name,password,endtime,time,user_name,attach,goods_name) values";
                foreach($data as $v){
                    $sql.="('".$v['name']."','".$v['password']."',".$v['endtime'].",".$v['time'].",'".$v['user_name']."','".$v['attach']."','".$v['goods_name']."'),";
                }
                $sql=substr($sql,0,strlen($sql)-1);
                $sql.=';';

                $cc=M();

 

2. in 关键字

$where['username']=array('in',$arr);
$count = $Online->where($where)->where('acctstoptime is null')->count();

某个字段中是否值是否存在于某个数组中

 

3.搜索优化 用join 增加索引 索引类型分为Unique 和Nomalh和Full text 索引方法 btree和hash 尽量不适用like 一般用LOCATE（）代替  %和两个% 的查询速度不一样 

4.ROW_NUMBER 分组排序功能 

ROW_NUMBER() OVER (PARTITION BY SItemID ORDER BY sid DESC )  AS rn

SELECT Wh_Bill.SNO AS bSON,* FROM(SELECT ROW_NUMBER() OVER (PARTITION BY SItemID ORDER BY sid DESC ) AS rn, SPID  FROM .Wh_BillSub ) AS www LEFT JOIN Wh_Bill ON Wh_Bill.SID=www.SPID WHERE rn=1 

5.sqlserver 对时间的处理  这样的时间 2019-12-15 00:00:00.000 格式化时间

convert(varchar(10),字段名，121)

 

 

5. sql server 

考勤时间查询 查询每天最大打卡时间和最小打卡时间  如果一天只打一次 另一次就是空

SELECT MIN(SCollectionDate) as am,CASE WHEN MAX(SCollectionDate)=MIN(SCollectionDate) 
THEN '' ELSE  MAX(SCollectionDate) end as pm,En_Project.SProName,Kq_CardRecordOuter000000.SCardDay FROM dbo.Kq_CardRecordOuter000000 JOIN En_Project on En_Project.SSaOrderID=Kq_CardRecordOuter000000.SSaOrderID
GROUP BY Kq_CardRecordOuter000000.SUserID,Kq_CardRecordOuter000000.SCardDay,En_Project.SProName 

 

6.查询下属的考勤打卡 

SELECT  DISTINCT Sa_Order.SProName ,
        En_Employee.SEmplName ,
         MIN(dbo.Kq_CardRecordOuter000000.SCollectionDate) as am,CASE WHEN MAX(dbo.Kq_CardRecordOuter000000.SCollectionDate)=MIN(dbo.Kq_CardRecordOuter000000.SCollectionDate) 
THEN '' ELSE  MAX(dbo.Kq_CardRecordOuter000000.SCollectionDate) end as pm
FROM    dbo.Kq_CardRecordOuter000000
        LEFT JOIN dbo.En_Employee ON dbo.Kq_CardRecordOuter000000.SUserID = dbo.En_Employee.SUserID
        LEFT JOIN dbo.Sa_Order ON Sa_Order.SID = Kq_CardRecordOuter000000.SSaOrderID
WHERE   ( ISNULL(En_Employee.SUserID , 0) = 10 
          OR 10 IN(SELECT SUserID  FROM En_Employee where ISNULL(En_Employee.SIsMan, 0) = 1)
          OR 10 IN ( SELECT   SID
                           FROM     dbo.YS_UserOuter
                           WHERE    ISNULL(SFlagAdmin, 0) = 1 ) 
        ) GROUP BY Kq_CardRecordOuter000000.SUserID,Kq_CardRecordOuter000000.SCardDay,Sa_Order.SProName,En_Employee.SEmplName

 

7.利用游标 动态jion表名 查询相关联的数据

DECLARE @strSQL VARCHAR(1000)
DECLARE @TableName VARCHAR(1000)
SET @strSQL = ''
DECLARE auth_cur CURSOR
FOR
    SELECT DISTINCT  SConfigTableName
FROM    YW_AdTask 
OPEN auth_cur
FETCH NEXT FROM auth_cur INTO @TableName
WHILE ( @@fetch_status = 0 ) 
    BEGIN
    SET @strSQL = 'SELECT  A.SNO ,
        YW_AdTask.SConfigTableName ,
        *
FROM    YW_AdTask
        INNER   JOIN ' + @TableName
    + ' A ON A.SID=YW_AdTask.SConfigValue'
EXEC(@strSQL)
PRINT @strSQL
    
      FETCH NEXT FROM auth_cur INTO @TableName
    END
CLOSE auth_cur
DEALLOCATE auth_cur

 

8.动态列查询 sql

DECLARE @msg NVARCHAR(max)=''
SELECT @msg=@msg+','+[key]+' as '+value  FROM KeyValue 

SET @msg='Name as 名称'+@msg;

DECLARE @sql NVARCHAR(max);
SET @sql='select '+@msg +' from TableNumber'

EXEC( @sql)

 

9.统计打卡报表

set datefirst 1
SELECT b.SproName,En_Employee.SJob,En_Employee.SNO,En_Employee.SEmplName,
B.SUserID,SCardDay as 日期,SCardDay,
B.SUserID,SCardDay as 日期,SCardDay,
MONTH(SCardDay) AS 月,DAY(SCardDay) AS day,datepart(weekday, SCardDay) as week,
b.SCardTime FROM  En_Employee 
inner JOIN ( SELECT    SCardDay,
                                SUserID ,Sa_Order.SproName,
                                ( SELECT    LEFT(SCardTime,5) + ' '
                                  FROM      Kq_CardRecordOuter000000
                                  WHERE     SCardDay = A.SCardDay
                                            AND SUserID = A.SUserID
 --and datediff(day, Kq_CardRecordOuter000000.SCardDay,:SSDate)<=0
 --                 and datediff(day, Kq_CardRecordOuter000000.SCardDay,:SEDate)>=0                       
                                          ORDER BY SCardTime
                                FOR
                                  XML PATH('')
                                ) AS SCardTime
                      FROM      Kq_CardRecordOuter000000 A
                                       left JOIN Sa_Order ON Sa_Order.SID=A.SSaOrderID
   --WHERE   datediff(day, A.SCardDay,:SSDate)<=0
   --              and datediff(day, A.SCardDay,:SEDate)>=0
                      GROUP BY  SCardDay ,
                                A.SUserID,Sa_Order.SproName
                     ) B ON En_Employee.SUserID = B.SUserID 
   --where (En_Employee.SID=:SEmplID or :SEmplID=0)
Order by B.SProName