SQL server数据库运算符

运算符"/>

SQL server数据库运算符

获取数据库当前时间：SELECT GETDATE( )

获取当前年份（GETDATE( )可以更改成时间字段）：SELECT DATEPART( YEAR, GETDATE( ) )

获取当前月份（GETDATE( )可以更改成时间字段）：SELECT DATEPART( MONTH, GETDATE( ) )

获取当前日（GETDATE( )可以更改成时间字段）：SELECT DATEPART(DAY, GETDATE()) 

获取当前时（GETDATE( )可以更改成时间字段）：SELECT DATEPART( HOUR, GETDATE( ) )

列重命名：AS

求和：SUM（字段名）

两部分结果合并为一个视图结果集：UNION ALL

   CASE WHEN SUM ( ShiftPowerTime ) <> 0 THEN SUM ( ShiftWorkTime ) / SUM ( ShiftPowerTime ) * 100 ELSE NULL END AS ManHourUtilization

判断除数是否为0（ShiftPowerTime 的和是否为0），ShiftWorkTime的和除以ShiftPowerTime的和乘以100 在重命名为ManHourUtilization的列显示

复制表示例

INSERT INTO 需要插入的表名 ([Material_Num],[ProductCode],[JobNo],[ShiftType],[ShiftWorkTime],[ShiftPowerTime],[ShiftUnWCount],[ShiftShouW],[ManHourUtilization],[UnitOutput],[ElectricityConsumption],[ShiftNo],[MachineName],[GongXuName],[ShopName],[FactoryName],[UpdateTime] ) 
SELECT [Material_Num],[ProductCode],[JobNo],[ShiftType],[ShiftWorkTime],[ShiftPowerTime],[ShiftUnWCount],[ShiftShouW],[ManHourUtilization],[UnitOutput],[ElectricityConsumption],[ShiftNo],[MachineName],[GongXuName],[ShopName],[FactoryName],[UpdateTime] 
FROM 查询的表名

查询当前年月当前机台的工时利用率

SELECT'机台' AS MachineName,'白班' AS ShiftType,DATEPART( MONTH, GETDATE( ) ) AS MonthNum, -- 获取当前月份CASE WHEN SUM ( ShiftPowerTime ) <> 0 THEN SUM ( ShiftWorkTime ) / SUM ( ShiftPowerTime ) * 100 ELSE NULL END AS ManHourUtilization, --判断除数是否为0 SUM ( ShiftWorkTime )是求和CASE WHEN SUM ( ShiftShouW ) <> 0 THEN SUM ( ShiftShouW ) / SUM ( ShiftWorkTime ) * 60 ELSE NULL END AS UnitOutput,CASE WHEN SUM ( ShiftShouW ) <> 0 THEN SUM ( ElectricityConsumption ) / SUM ( ShiftShouW ) * 10000 ELSE NULL END AS ElectricityConsumption,GETDATE( ) AS 更新时间 
FROMdbo.MachineAllWorkInfo 
WHEREMachineName = '机台'AND ShiftType = '夜班'AND DATEPART( YEAR, InsertTime ) = DATEPART( YEAR, GETDATE( ) ) -- 查询年份是否等于当前年份AND DATEPART( MONTH, InsertTime ) = DATEPART( MONTH, GETDATE( ) ) -- 查询月份是否等于当前月份