HardSoft-Viewer SQL
程序员文章站
2024-01-20 22:14:34
...
/* HardInfo视图 */
select Computers.Factory as 廠別,
Computers.Depart as 部門,
Computers.id as 財產編號,
Computers.Owner as 使用者,
Computers.Account as 帳戶,
Computers.Locate as 存放位置,
Hardware.OS as 操作系統,
Hardware.CPU,
Hardware.Computer_Type as 主機型號,
Hardware.Ram as 內存,
concat(ceil(Hardware.Ram / 256) * 256, 'MB') as 標準內存,
Hardware.Display_Card as 顯卡,
Hardware.MotherBoard_Vendor as 主板廠商,
Hardware.MotherBoard_Type as 主板型號,
Hardware.HardDisk_Type as 硬盤型號,
Hardware.HardDisk_Size as 硬盤大小,
/*Hardware.size / (1024 * 1024 * 1024) as STNAD_HARD,*/
concat(case when Hardware.HardDisk_Size/(1024*1024*1024) <= 40 then 40
when Hardware.HardDisk_Size/(1024*1024*1024) > 40 and Hardware.HardDisk_Size/(1024*1024*1024) <= 80 then 80
when Hardware.HardDisk_Size/(1024*1024*1024) > 80 and Hardware.HardDisk_Size/(1024*1024*1024) <= 120 then 120
when Hardware.HardDisk_Size/(1024*1024*1024) > 120 and Hardware.HardDisk_Size/(1024*1024*1024) <= 160 then 160
when Hardware.HardDisk_Size/(1024*1024*1024) > 160 and Hardware.HardDisk_Size/(1024*1024*1024) <= 250 then 250
when Hardware.HardDisk_Size/(1024*1024*1024) > 250 and Hardware.HardDisk_Size/(1024*1024*1024) <= 320 then 320
when Hardware.HardDisk_Size/(1024*1024*1024) > 320 and Hardware.HardDisk_Size/(1024*1024*1024) <= 500 then 500
when Hardware.HardDisk_Size/(1024*1024*1024) > 500 and Hardware.HardDisk_Size/(1024*1024*1024) <= 750 then 750
when Hardware.HardDisk_Size/(1024*1024*1024) > 750 and Hardware.HardDisk_Size/(1024*1024*1024) <= 1024 then 1024
when Hardware.HardDisk_Size/(1024*1024*1024) > 1024 and Hardware.HardDisk_Size/(1024*1024*1024) <= 1500 then 1500
when Hardware.HardDisk_Size/(1024*1024*1024) > 1500 and Hardware.HardDisk_Size/(1024*1024*1024) <= 2048 then 2048
end,'GB') as 標準硬盤大小,
Hardware.NetCard_Type as 網卡型號,
Hardware.MAC_Address AS MAC,
Hardware.IP_Address AS IP,
Control.Admin,
Control.Poweruser,
CASE
WHEN Control.Usb = '1' THEN
'開'
else
'關'
end as USB,
Computers.Mark,
Computers.Note AS 備註
from Computers, Hardware, Control
where Computers.id = Hardware.id
and Computers.id = Control.id
select Computers.Factory,
Computers.Depart,
Computers.id,
Computers.Owner,
Computers.Account,
Computers.Locate,
Hardware.OS,
Hardware.CPU,
Hardware.Computer_Type,
Hardware.Ram,
concat(ceil(Hardware.Ram / 256) * 256, 'MB') as STAND_Ram,
Hardware.Display_Card,
Hardware.MotherBoard_Vendor,
Hardware.MotherBoard_Type,
Hardware.HardDisk_Type,
Hardware.HardDisk_Size,
/*Hardware.size / (1024 * 1024 * 1024) as STNAD_HARD,*/
concat(case when Hardware.HardDisk_Size/(1024*1024*1024) <= 40 then 40
when Hardware.HardDisk_Size/(1024*1024*1024) > 40 and Hardware.HardDisk_Size/(1024*1024*1024) <= 80 then 80
when Hardware.HardDisk_Size/(1024*1024*1024) > 80 and Hardware.HardDisk_Size/(1024*1024*1024) <= 120 then 120
when Hardware.HardDisk_Size/(1024*1024*1024) > 120 and Hardware.HardDisk_Size/(1024*1024*1024) <= 160 then 160
when Hardware.HardDisk_Size/(1024*1024*1024) > 160 and Hardware.HardDisk_Size/(1024*1024*1024) <= 250 then 250
when Hardware.HardDisk_Size/(1024*1024*1024) > 250 and Hardware.HardDisk_Size/(1024*1024*1024) <= 320 then 320
when Hardware.HardDisk_Size/(1024*1024*1024) > 320 and Hardware.HardDisk_Size/(1024*1024*1024) <= 500 then 500
when Hardware.HardDisk_Size/(1024*1024*1024) > 500 and Hardware.HardDisk_Size/(1024*1024*1024) <= 750 then 750
when Hardware.HardDisk_Size/(1024*1024*1024) > 750 and Hardware.HardDisk_Size/(1024*1024*1024) <= 1024 then 1024
when Hardware.HardDisk_Size/(1024*1024*1024) > 1024 and Hardware.HardDisk_Size/(1024*1024*1024) <= 1500 then 1500
when Hardware.HardDisk_Size/(1024*1024*1024) > 1500 and Hardware.HardDisk_Size/(1024*1024*1024) <= 2048 then 2048
end,'GB') as STAND_HD_Size,
Hardware.NetCard_Type,
Hardware.MAC_Address,
Hardware.IP_Address,
Control.Admin,
Control.Poweruser,
CASE
WHEN Control.Usb = '1' THEN
'開'
else
'關'
end as USB,
Computers.Mark,
Computers.Note
from Computers, Hardware, Control
where Computers.id = Hardware.id
and Computers.id = Control.id
Get_MS_Office SQL
SELECT `Software`.`Soft_ID`, `Software`.`id`, `Software`.`SoftName`, max(`Software`.`ScanTime`) as ScanTime, max(`Software`.`Note`) as Note FROM `Software` WHERE ((lcase(`Software`.`SoftName`) like '%microsoft%office%') and (not((lcase(`Software`.`SoftName`) like '%security%'))) and (not((lcase(`Software`.`SoftName`) like '%viewer%'))) and (not((lcase(`Software`.`SoftName`) like '%update%'))) and (not((lcase(`Software`.`SoftName`) like '%add-in%'))) and (not((lcase(`Software`.`SoftName`) like '%mui%'))) and (not((lcase(`Software`.`SoftName`) like '%help%'))) and (not((lcase(`Software`.`SoftName`) like '%proof%'))) and (not((lcase(`Software`.`SoftName`) like '%ime%'))) and (not((lcase(`Software`.`SoftName`) like '%sp%')))) group by `Software`.`SoftName`, `Software`.`id` order by `Software`.`id`, `Software`.`SoftName`
Get_Adobe_Soft
SELECT `Software`.`Soft_ID`, `Software`.`id`, `Software`.`SoftName`, max(`Software`.`ScanTime`) as ScanTime, max(`Software`.`Note`) as Note FROM `Software` WHERE (( lower( SoftName ) LIKE '%illustrator%' OR lower( SoftName ) LIKE '%photoshop%' OR lower( SoftName ) LIKE '%acrobat%' OR lower( SoftName ) LIKE '%audition%' OR lower( SoftName ) LIKE '%premiere%' OR lower( SoftName ) LIKE '%dreamweaver%' OR lower( SoftName ) LIKE '%fireworks%' OR lower( SoftName ) LIKE '%lightroom%' OR lower( SoftName ) LIKE '%flash%professional%' OR lower( SoftName ) LIKE '%captivate%' OR lower( SoftName ) LIKE '%coldfusion%' OR lower( SoftName ) LIKE '%after%effects%' ) AND lower( SoftName ) NOT LIKE '%help%media%' AND lower( SoftName ) NOT LIKE '%language%pack%' ) group by `Software`.`SoftName`, `Software`.`id` order by `Software`.`id`, `Software`.`SoftName`
Get_2D_3D_Soft
SELECT `Software`.`Soft_ID`, `Software`.`id`, `Software`.`SoftName`, max(`Software`.`ScanTime`) as ScanTime, max(`Software`.`Note`) as Note FROM `Software` WHERE (( lower( SoftName ) LIKE '%engineer%' OR lower( SoftName ) LIKE '%magics%' OR lower( SoftName ) LIKE '%t-splines%' OR lower( SoftName ) LIKE '%keyshot%' OR lower( SoftName ) LIKE '%shoecost%' OR lower( SoftName ) LIKE '%cad%' OR lower( SoftName ) LIKE '%uvlayout%' OR lower( SoftName ) LIKE '%coreldraw%' OR lower( SoftName ) LIKE '%3dmax%' OR lower( SoftName ) LIKE '%maya%' OR lower( SoftName ) LIKE '%solidworks%' ) AND lower( SoftName ) NOT LIKE '%help%media%' AND lower( SoftName ) NOT LIKE '%language%pack%' ) group by `Software`.`SoftName`, `Software`.`id` order by `Software`.`id`, `Software`.`SoftName`
Get_Other_Soft
SELECT `Software`.`Soft_ID`, `Software`.`id`, `Software`.`SoftName`, max(`Software`.`ScanTime`) as ScanTime, max(`Software`.`Note`) as Note FROM `Software` WHERE (( lower( SoftName ) LIKE '%dos%' OR lower( SoftName ) LIKE '%wps%office%' OR lower( SoftName ) LIKE '%winrar%' OR lower( SoftName ) LIKE '%2345%' OR lower( SoftName ) LIKE '%ghost%' OR lower( SoftName ) LIKE '%oe-mail%' OR lower( SoftName ) LIKE '%realvnc%' OR lower( SoftName ) LIKE '%radmin%' OR lower( SoftName ) LIKE '%sms%' OR lower( SoftName ) LIKE 'qq%' OR lower( SoftName ) LIKE '%sogou%' OR lower( SoftName ) LIKE '%remote%administrator%' OR lower( SoftName ) LIKE '%搜狗%' ) AND lower( SoftName ) NOT LIKE '%viewer%' ) group by `Software`.`SoftName`, `Software`.`id` order by `Software`.`id`, `Software`.`SoftName`
Get_Null_Info
SELECT Factory,Depart,Owner,Computers.id,Locate,OS,CPU,Ram,Display_Card,Computer_Type,MotherBoard_Type,HardDisk_Type,HardDisk_Size,NetCard_Type,MAC_Address,IP_Address FROM Hardware,Computers where ( length(`OS`) < 2 OR length(`CPU`) < 4 OR length(`Ram`) < 2 OR length(`Display_Card`) < 2 OR length(`MotherBoard_Type`) < 2 OR length(`HardDisk_Type`) < 2 OR length(`HardDisk_Size`) < 2 OR length(`NetCard_Type`) < 2 OR length(`MAC_Address`) < 2 OR length(`IP_Address`) < 2 ) AND Hardware.id=Computers.id
Get_Rhino_Soft
/* 法1 */ select max(b.Soft_ID) as Soft_ID, b.id, b.SoftName,b.ScanTime, b.Note from Software b where (b.id,b.SoftName) in (select a.id,MIN(a.SoftName) from Software a where a.SoftName in ( select distinct c.SoftName from Software c where lower( c.SoftName ) LIKE '%rhinoceros%' AND LOWER(c.SoftName) NOT LIKE "%help%media%" group by c.SoftName ) group by a.id ) group by b.id, b.SoftName /* 法2 */ SELECT max(`Soft_ID`) as Soft_ID,id,`ScanTime`,case WHEN instr(SoftName,'rhinoceros 5') > 0 then 'Rhinoceros 5.0' WHEN instr(SoftName,'Rhinoceros 4') > 0 then 'Rhinoceros 4.0' ELSE SoftName end SoftName,Note FROM `Software` WHERE `SoftName` LIKE '%rhinoceros%' GROUP BY id
Get_Soft
SELECT * FROM `Get_2D_3D_Soft` UNION ALL SELECT * FROM `Get_Adobe_Soft` UNION ALL SELECT * FROM `Get_MS_Office` UNION ALL SELECT * FROM `Get_Other_Soft` UNION ALL SELECT * FROM `Get_Rhino_Soft`
转载于:https://blog.51cto.com/fengwan/1618237
上一篇: java面向对象基础
下一篇: Java面向对象基础