Iawen's Blog

MySQL中各数据类型的取值范围

TINYINT
-128 - 127
TINYINT UNSIGNED
0 - 255
SMALLINT
-32768 - 32767
SMALLINT UNSIGNED
0 - 65535
MEDIUMINT
-8388608 - 8388607
MEDIUMINT UNSIGNED
0 - 16777215
INT 或 INTEGER
-2147483648 - 2147483647
INT UNSIGNED 或 INTEGER UNSIGNED
0 - 4294967295
BIGINT
-9223372036854775808 - 9223372036854775807
BIGINT UNSIGNED
0 - 18446744073709551615

由于经常会用手机来测试网页的手机版，为了减少输入，所以非常喜欢地址栏的二维码插件，只需要轻轻一扫，就能够在手机端打开需要测试的网页了。
火狐已经有这样的插件了，不过，如果你安装的不是火狐中国版，默认就没有了，你可以到下面的地址去安装：
扩展地址：http://g-fox.cn/chinaedition/addons/cpmanager/cpmanager-1.2.13.xpi
而Chrome，由于访问不方便（~&~，需要跳墙），所以我就参照着网上的方法自己动手弄了一个：
qrcode.rar
具体的安装方法就是：
1、更多工具->扩展程序
2、拖放文件qrcode.crx到扩展程序界面进行安装
安装后效果如下图：

$str = "201510222141084291234562341234";
echo $str."\n";
echo bcmod(bcadd($str, '07345'), 6445)."\n";

函数功能如题！

mode con lines=40
mode con cols=120
color 0a
cls
@cmd.exe 

1、打开文件PHPExcel\Writer\Excel2007\Chart.php
2、找到函数_writePlotGroup
3、在
foreach($plotSeriesOrder as $plotSeriesIdx => $plotSeriesRef) { 之前添加代码

$colorNDX=array();
$colorNDX[0] = "ff33cc";
$colorNDX[1] = "ffff00";
$colorNDX[2] = "ffc000";
$colorNDX[3] = "00b0f0";
$colorNDX[4] = "ff0000";
$colorNDX[5] = "058beb";
$colorNDX[6] = "06eb6e";
$t =$plotGroup->getPlotValues();

//批量删除MS SQL中的表
DECLARE @tablename VARCHAR(100)
DECLARE @sql VARCHAR(500)
DECLARE cur_delete_table CURSOR READ_ONLY FORWARD_ONLY FOR

SELECT name FROM sysobjects WHERE name LIKE 'BSTemp%' AND type='U'
OPEN cur_delete_table
FETCH NEXT FROM cur_delete_table INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
  SELECT @sql='DROP TABLE '+@tablename
  EXEC (@sql)
  FETCH NEXT FROM cur_delete_table INTO @tablename
END
CLOSE cur_delete_table
DEALLOCATE cur_delete_table

如果是视图，则type='V'， 同时DROP TABLE改为DROP VIEW

SELECT 
  (CASE WHEN a.colorder=1 THEN d.name ELSE '' END) N'表名',
  a.colorder N'字段序号',
  a.name N'字段名',
  (CASE WHEN COLUMNPROPERTY( a.id, a.name, 'IsIdentity')=1 THEN '√'ELSE '' END) N'标识',
  (CASE 
    WHEN (
      SELECT COUNT(*) FROM sysobjects WHERE name IN (
        SELECT name FROM sysindexes 
        WHERE id = a.id AND 
            indid IN (SELECT indid FROM sysindexkeys WHERE id = a.id AND colid in (SELECT colid FROM syscolumns WHERE id = a.id AND name = a.name)
        )
      ) AND xtype = 'PK')>0 
    THEN '√' 
    ELSE '' END
  ) N'主键',
  b.name N'类型',
  a.length N'占用字节数',
  COLUMNPROPERTY(a.id,a.name,'PRECISION') AS N'长度',
  ISNULL(COLUMNPROPERTY(a.id,a.name,'Scale'),0) AS N'小数位数',
  (CASE WHEN a.isnullable=1 THEN '√' ELSE '' END) N'允许空',
  ISNULL(e.text, '') N'默认值'
FROM syscolumns a 
LEFT JOIN systypes b  ON a.xtype=b.xusertype
INNER JOIN sysobjects d ON a.id=d.id AND d.xtype='U' AND d.name<>'dtproperties'
LEFT JOIN syscomments e ON a.cdefault=e.id
WHERE d.name = 'DOC_ORG' --要查询的表
ORDER BY object_name(a.id), a.colorder

基本概念：全月一次加权平均法(Weighted-averageSystem)什么是全月一次加权平均法　　全月一次加权平均法是以本月的期初数量和本月全部进货数量作为权数，取出本月全部进货成本加上期初的进货成本，计算出本月存货平均单位成本，以此作为基础算出本月的发出存货的成本以及月末的库存实际成本。

全月一次加权平均法的计算公式:　

平均单位成本=  (期初结存材料成本+∑(本月入库成本)) / ( 期初结存数量+∑(本月入库数量) )
发出成本 = 发出数量×平均单位成本结存成本 = 结存数量×平均单位成本

1、清理以前还原过的痕迹,如果我们在数据库曾经还原过,我们先来清理一下,痕迹,

//删除用户
DROP USER xxxx CASCADE;
//删除表空间
DROP TABLESPACE xxxx;
//删除数据库文件
(数据库所在路径)xxxxxx.dbf

MS SQL多行转列示例：

SELECT 
  CASE 
    WHEN DeptName='广州滨江东' THEN 'S49'
    WHEN DeptName='广州太古汇' THEN 'CS01'
    WHEN DeptName='广州天河城' THEN 'S48'
    WHEN DeptName='广州五月花' THEN 'CS05' 
  END sh_code,
  rtrim(convert(varchar(10),CARDID)) +'-' +rtrim(EmployeeName) AS staff_id,
  rtrim(convert(varchar(10),CARDID)) +'-' +rtrim(EmployeeName) AS staff_name,
  AddTime sign_date,
  [1] as in_time1, [3] as in_time2, [5] as in_time3, [7] as in_time4, 
  [2] as out_time1,[4] as out_time2,[6] as out_time3,[8] as out_time4,
  '' AS remark 
FROM 
(
  SELECT 
    row_number() over (partition by CardID, CONVERT(VARCHAR(10), AddTime, 120) order by id) AS ID, 
    CardID, EmployeeName, AttDateTime, DeptName,
    CONVERT(VARCHAR(10), AddTime, 120) AddTime
  FROM View_Staff_sgin
) AS SourceTable
 PIVOT ( MAX(AttDateTime) FOR ID IN ([1],[2],[3],[4],[5],[6],[7],[8]) ) AS PivotTable
 WHERE 
  AddTime ='2015-03-21'

最主要的地方是用到了PIVOT语法，另外就是结合row_number()函数。

不过，确实为这个打卡表的设计感到郁闷！