git

1. GitHub 是什么?

GitHub是一个非常流行的全球代码托管平台，基于Git版本控制技术实现，同时GitHub也是一个活跃的开发者交流社区。许多的开源项目都在GitHub上发布。例如著名的Linux操作系统内核的源代码。

2. Git是什么?

Git是目前世界上最先进的分布式版本控制系统(没有之一)。

它由Linux系统之父Linus Torvalds在2005年用C语言开发。

Git是一个分布式版本控制系统，没有中央服务器，不同于svn等需要中央服务器的集中式版本控制系统。

Git的功能：版本控制(版本管理，远程仓库，分支协作)

人工版本管理：

41cf9e78267e2cd50f447ed4487d81af.png


Git版本管理：

e08a3004cf34864acb3bf342f1e7d633.png


3. Git功能原理

  • git版本控制系统由工作区，缓存区，版本库组成。
  • git跟踪的是文件的修改而不是全部文件。
  • git擅长管理代码等文本文件，不擅长管理图片等二进制文件。

7e641b07168d736d5a57f2156b5b2f2a.png


4. 快速上手

1，安装Git

下载位置: Git下载链接—— https://git-scm.com/downloads

可以在bash中执行git命令，也可以在Jupyter Notebook中执行.

2，配置用户信息

git config --global user.name "XX"git config --global user.email "XX@XX"

3，新建文件夹并切入

mkdir git-learncd git-learn

4，创建仓库

git init

5，新建readme.txt

echo "hello world" >>readme.txt

6，查看当前状态：

git status

7，添加全部修改到暂存区并提交

git add -Agit commit -m"comment" 

8，添加github远程库并推送仓库

git remote add origin https://github.com/XX/XXgit push -u origin master

9，在github上的项目clone到本地文件

git clone https://github.com/XX/XX  ../XX

5. 安装配置

命令列表：

  • config(配置信息)
  • init(创建仓库)
  • help(帮助信息)
  • status(当前状态)

特殊文件：

  • .gitignore过滤文件(配置git无需管理的目录和文件)

示范1：基本配置

#设置用户名：git config --global user.name "XXX"#设置用户邮箱：git config --global user.email "XXX@XX"#初始化仓库git init 

示范2：获取帮助

#获取常用git命令列表：git help#查看当前状态，获取提示：git status

示范3：建立.gitignore过滤配置文件

#过滤掉.DS_store文件echo ".DS_store" >.gitignore#过滤掉.ipynb_checkpoints目录echo ".ipynb_checkpoints/" >> .gitignore#过滤掉所有zip文件echo "*.zip" >>.gitignore 

6. 版本管理

命令列表：

  • add(修改暂存)
  • commit(提交修改)
  • reset(版本回退)
  • checkout(撤销修改)
  • rm(删除文件)
  • diff(比较文件)
  • log(版本列表)
  • reflog(版本历史)

git reset的说明：

git reset HEAD^ #可以回退到上一个版本。git reset HEAD^^ #可以回退到上上个版本。git reset a234b3 #可以回退到版本号为 a234b3的版本。git reset --hard  head^   #修改版本库，保留暂存区，保留工作区git reset --mixed head^  #修改版本库，修改暂存区，保留工作区git reset --soft  head^  #修改版本库，保留暂存区，保留工作区

示范1：版本提交

echo "hello world" >>readme.txtgit add readme.txtgit add -Agit commit -m"add readme.txt"#修改覆盖上一次commit:git commit --amend#先暂存再提交：git commit -a -m"modify readme.txt"

示范2：版本回退

git reset --hard head^git reset head a23b5#撤销修改：git checkout -- readme.txt#图形显示仓库版本及分支状态：git log --oneline --graph --all#查看head指向过的版本历史：git reflog

示范3：日志压缩

git reset --soft a23b5 #版本库回退到某个版本，工作区保留修改git commit -m 'add feature' #将工作区的修改写到版本库中，a23b5到当前的日志被删除

示范4：撤销修改

echo "hello Haidian">>readme.txtgit checkout -- readme.txt #使用暂存区内容覆盖文件

示范5：版本对比

#查看工作区文件与暂存区文件区别：git diff  readme.txt#查看工作区文件和head文件区别：git diff --head readme.txt#查看暂存区文件与head文件区别：git diff --cached readme.txt#查看两个版本某一文件的区别:git diff a458b d23e5 -- readme.txt

4288aa4636ee9b74d5f924eb81ad6501.png

7. 远程仓库

命令列表：

  • remote(设置远程)
  • push(推送远程)
  • clone(克隆远程)
  • pull(合并远程)
  • fetch(拉取远程)

远程仓库说明：

#通常可以用SSH协议和远程库通信或使用http协议和远程库通信。#http协议较为方便，但SSH协议方式速度较快。#运行下面命令，并一路回车，在用户主目录里找到.ssh目录。ssh-keygen -t rsa -C "your_emial@xxx.com"#目录下的id_rsa有私钥，不能泄露出去。里面的id_rsa.pub是公钥，可分享给别人。#在GitHub——> settings ——> SSH Keys 页面添加SSH公钥。#将公钥绑定github后尝试建立SSH连接:ssh -T git@github.com

示范1：添加远程

#使用ssh地址添加github远程库连接并命名为github，ssh方式更稳定快速，但稍麻烦：git remote add github git@github.com:lyhue1991/ai.git:#使用url地址添加远程库，url方式更简单：git remote add origin https://github.com/lyhue1991/GitHub.git

示范2：推送到远程

#推送本地至远程库origin的master分支:git push -u origin master#查看远程库信息：git remote -v#移除和远程库的连接:git remote remove orgin

示范3：拉取远程

#建立当前master分支与远程库develop分支的追踪关系：git branch --set-upstream master origin/develop#将远程仓库克隆到本地父目录的ML文件夹：git clone https://github.com/lyhue1991/machine-learning.git ../ML#取回origin的develop分支与当前master分支合并(或会冲突)：git pull orgin/develop:master#获取origin的develop分支到本地并用merge合并(pull≈fetch+merge)：git fetch origin developgit merge origin/develop  

8. 分支协作

命令列表：

  • branch(设置分支)
  • checkout(切换分支)
  • merge(合并分支)
  • cherry-pick(采集提交)
  • rebase(重演分支)
  • stash(储藏管理)
  • tag(标签管理)

示范1：切换分支

#查看分支信息：git branch#当前head位置新建develop分支：git branch develop#创建并切换到名称为feature的新分支：git checkout -b feature#切换至master分支：git checkout master

示范2：分支整合

#head处于develop分支,合并feature分支(或会冲突)：git merge  --no-ff -m"merge feature"  feature#采集其它分支中版本号为a458b的commit提交至当前分支(或会冲突)：git cherry-pick a458b#使用当前所在分支作为base重演develop分支(或会冲突)：git rebase develop

示范3：储藏和标签

git stash;git stash pop;git stash list;git stash save "message"git stash apply @2git tag;git tag v1.0;git tag v0.9 a2543d;#推送标签到远程(标签不会自动推送)git push origin v1.0#删除远程标签(先删本地，再push)：git tag -d v0.9git push origin :refs/tags/v0.9

git分支管理最佳实践：

  • master: 主分支，主要用来版本发布。
  • develop：日常开发分支，该分支正常保存了开发的最新代码。
  • feature：具体的功能开发分支，只与 develop 分支交互。
  • release：release 分支可以认为是 master 分支的未测试版。比如说某一期的功能全部开发完成，那么就将 develop 分支合并到 release 分支，测试没有问题并且到了发布日期就合并到 master 分支，进行发布。
  • hotfix：线上 bug 修复分支。

4c4c858b8d3b27653efbdfa4153fc9e9.png

近期干货

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

https://gitee.com/er_eggs/tor-plcprogram.git

 

 

 

 

 

 

 

 

 

 

 

public void OpenRemoteFile(string FileName)
{
object missing = System.Reflection.Missing.Value;
app = new Application();
wbs = app.Workbooks;

var token = IntPtr.Zero;
if (LogonUser("svcgzsdfh_par", "Jabil", "Jabidfl@201sdf!!", 2, 0, out token))
{
using (WindowsIdentity newId = new WindowsIdentity(token))
{
using (WindowsImpersonationContext impersonatedUser = newId.Impersonate())
{
var copyFile = AppDomain.CurrentDomain.BaseDirectory + "PackageListReport.xls";
File.Copy(FileName, copyFile, true);
wb = wbs.Open(copyFile, missing, false, missing, missing, missing, missing, missing, missing, true, missing, missing, missing, missing, missing);
mFilename = copyFile;
}
}
}
else
{
throw new Exception("模拟登录失败，请检查用户名密码");
}
}
using (entry = new DirectoryEntry(ADHelper.ADPath, "jabil\\svcwsdfeh_par", "Jasdfbil@sdfk51!!", AuthenticationTypes.Secure))
{
entry.RefreshCache();
//DirectorySearcher search = new DirectorySearcher(entry);
//if (strNTID.IndexOf('@') != -1)
//{
// search.Filter = string.Format("Mail={0}", strNTID);
//}
//else
//{
// search.Filter = string.Format("SAMAccountName={0}", strNTID);
//}
//result = search.FindOne();
File.Copy(IdocFolder_BackUp + "\\" + Path.GetFileName(sFileName), IdocFolder + "\\" + Path.GetFileName(sFileName));
LogUtil.Info("Filename:" + Path.GetFileName(sFileName));
}
Process.Start("explorer.exe", "E:\\");

 

 


class Program
{
static void Main(string[] args)
{

//获取当前运行程序的目录
string fileDir = Environment.CurrentDirectory;
Console.WriteLine("当前程序目录："+fileDir);

//一个文件目录
string filePath = "C:\\JiYF\\BenXH\\BenXHCMS.xml";
Console.WriteLine("该文件的目录："+filePath);

string str = "获取文件的全路径：" + Path.GetFullPath(filePath); //-->C:\JiYF\BenXH\BenXHCMS.xml
Console.WriteLine(str);
str = "获取文件所在的目录：" + Path.GetDirectoryName(filePath); //-->C:\JiYF\BenXH
Console.WriteLine(str);
str = "获取文件的名称含有后缀：" + Path.GetFileName(filePath); //-->BenXHCMS.xml
Console.WriteLine(str);
str = "获取文件的名称没有后缀：" + Path.GetFileNameWithoutExtension(filePath); //-->BenXHCMS
Console.WriteLine(str);
str = "获取路径的后缀扩展名称：" + Path.GetExtension(filePath); //-->.xml
Console.WriteLine(str);
str = "获取路径的根目录：" + Path.GetPathRoot(filePath); //-->C:\
Console.WriteLine(str);
Console.ReadKey();

}
}


string str1 =Process.GetCurrentProcess().MainModule.FileName;//可获得当前执行的exe的文件名。
string str2=Environment.CurrentDirectory;//获取和设置当前目录（即该进程从中启动的目录）的完全限定路径。
//备注按照定义，如果该进程在本地或网络驱动器的根目录中启动，则此属性的值为驱动器名称后跟一个尾部反斜杠（如“C:\”）。如果该进程在子目录中启动，则此属性的值为不带尾部反斜杠的驱动器和子目录路径（如“C:\mySubDirectory”）。
string str3=Directory.GetCurrentDirectory();//获取应用程序的当前工作目录。
string str4=AppDomain.CurrentDomain.BaseDirectory;//获取基目录，它由程序集冲突解决程序用来探测程序集。
string str5=Application.StartupPath;//获取启动了应用程序的可执行文件的路径，不包括可执行文件的名称。
string str6=Application.ExecutablePath;//获取启动了应用程序的可执行文件的路径，包括可执行文件的名称。
string str7=AppDomain.CurrentDomain.SetupInformation.ApplicationBase;//获取或设置包含该应用程序的目录的名称。
1. System.Diagnostics.Process.GetCurrentProcess().MainModule.FileName
获取模块的完整路径。
2. System.Environment.CurrentDirectory
获取和设置当前目录(该进程从中启动的目录)的完全限定目录。
3. System.IO.Directory.GetCurrentDirectory()
获取应用程序的当前工作目录。这个不一定是程序从中启动的目录啊，有可能程序放在C:\www里,这个函数有可能返回C:\ Documents and Settings\ZYB\,或者C:\Program Files\Adobe\,有时不一定返回什么东东，我也搞不懂了。
4. System.AppDomain.CurrentDomain.BaseDirectory
获取程序的基目录。
5. System.AppDomain.CurrentDomain.SetupInformation.ApplicationBase
获取和设置包括该应用程序的目录的名称。
6. System.Windows.Forms.Application.StartupPath
获取启动了应用程序的可执行文件的路径。效果和、一样。只是返回的字符串后面多了一个"\"而已
7. System.Windows.Forms.Application.ExecutablePath
获取启动了应用程序的可执行文件的路径及文件名，效果和一样。

From <http://blog.sina.com.cn/s/blog_591842240100tlhn.html>

 

 

方式一：通过委托发起线程（BeginInvoke）：
Func<int, int> a = Test;
IAsyncResult ar = a.BeginInvoke(20, OnCallBack, a);//倒数第二个参数是一个委托类型的参数，表示回调函数，当线程结束时会调用这个委托指向的方法；倒数第一个参数用来给回调函数传递数据；通过ar获取数据a
方式二：通过Thread发起线程，thread创建的线程都是前台线程，线程池创建的线程都是后台线程
1. thread参数为静态方法

static void Downloadfile()
{
Console.WriteLine("开始下载" + Thread.CurrentThread.ManagedThreadId);
Thread.Sleep(2000);
Console.WriteLine("下载完成");
}
static void Main(string[] args)
{
Thread t = new Thread(Downloadfile);
t.Start();
Console.WriteLine("main");
Console.ReadKey();
}

2. lamda表达式

1 Thread t = new Thread(() =>
2 {
3 Console.WriteLine("开始下载" + Thread.CurrentThread.ManagedThreadId);
4 Thread.Sleep(2000);
5 Console.WriteLine("下载完成");
6 });
7 t.Start();

3. Thread参数为普通方法


1 class Program
2 {
3 static void Downloadfile(object filename)
4 {
5 Console.WriteLine("开始下载" + filename+ Thread.CurrentThread.ManagedThreadId);
6 Thread.Sleep(2000);
7 Console.WriteLine("下载完成"+filename);
8 }
9
10 static void Main(string[] args)
11 {
12 //Thread t = new Thread(() =>
13 //{
14
15 // Console.WriteLine("开始下载" + Thread.CurrentThread.ManagedThreadId);
16 // Thread.Sleep(2000);
17 // Console.WriteLine("下载完成");
18 //});
19 //Thread t = new Thread(Downloadfile);
20 MyThread my = new MyThread("xxx.bt", "http://www.xxx.bbs");
21 Thread t = new Thread(my.DownFile);
22 t.Start();
23 Console.WriteLine("main");
24 Console.ReadKey();
25 }
26 }

 


1 class MyThread
2 {
3 private string filename;
4 private string filepath;
5 public MyThread(string fileName, string filePath)
6 {
7 this.filename = fileName;
8 this.filepath = filePath;
9 }
10
11 public void DownFile()
12 {
13 Console.WriteLine("开始下载" + filepath + filename);
14 Thread.Sleep(2000);
15 Console.WriteLine("下载完成");
16 }
17
18 }
19 }

方式三：线程池：适合用于小任务线程,
WaitCallback:将方法排入队列以便执行，WaitCallback，表示要执行的方法。如果将方法成功排入队列，则为 true；否则为 false。


1 class Program
2 {
3 static void ThreadMethod(object state)
4 {
5 Console.WriteLine("线程开始"+Thread.CurrentThread.ManagedThreadId);
6 Thread.Sleep(2000);
7 Console.WriteLine("线程结束");
8 }
9 static void Main(string[] args)
10 {
11 ThreadPool.QueueUserWorkItem(ThreadMethod);
12 ThreadPool.QueueUserWorkItem(ThreadMethod);
13 ThreadPool.QueueUserWorkItem(ThreadMethod);
14 ThreadPool.QueueUserWorkItem(ThreadMethod);
15 ThreadPool.QueueUserWorkItem(ThreadMethod);
16 Console.ReadKey();
17 }
18 }

方式四：任务线程
1. 通过Task创建


1 class Program
2 {
3 static void ThreadMethod()
4 {
5 Console.WriteLine("任务开始" + Thread.CurrentThread.ManagedThreadId);
6 Thread.Sleep(2000);
7 Console.WriteLine("任务结束");
8 }
9
10 static void Main(string[] args)
11 {
12 Task t = new Task(ThreadMethod);
13 t.Start();
14 Console.WriteLine("main");
15 Console.ReadKey();
16 }
17 }

2. 通过TaskFactory创建
TaskFactory tf = new TaskFactory();
tf.StartNew(ThreadMethod);
tf.StartNew(ThreadMethod);
tf.StartNew(ThreadMethod)；

From <https://www.cnblogs.com/wxhao/p/13604924.html>

 


C# 委托（Delegate）
C# 中的委托（Delegate）类似于 C 或 C++ 中函数的指针。委托（Delegate） 是存有对某个方法的引用的一种引用类型变量。引用可在运行时被改变。
委托（Delegate）特别用于实现事件和回调方法。所有的委托（Delegate）都派生自 System.Delegate 类。
声明委托（Delegate）
委托声明决定了可由该委托引用的方法。委托可指向一个与其具有相同标签的方法。
例如，假设有一个委托：
public delegate int MyDelegate (string s);
上面的委托可被用于引用任何一个带有一个单一的 string 参数的方法，并返回一个 int 类型变量。
声明委托的语法如下：
delegate <return type> <delegate-name> <parameter list>
实例化委托（Delegate）
一旦声明了委托类型，委托对象必须使用 new 关键字来创建，且与一个特定的方法有关。当创建委托时，传递到 new 语句的参数就像方法调用一样书写，但是不带有参数。例如：
public delegate void printString(string s);
...
printString ps1 = new printString(WriteToScreen);
printString ps2 = new printString(WriteToFile);
下面的实例演示了委托的声明、实例化和使用，该委托可用于引用带有一个整型参数的方法，并返回一个整型值。
实例
using System;

delegate int NumberChanger(int n);
namespace DelegateAppl
{
class TestDelegate
{
static int num = 10;
public static int AddNum(int p)
{
num += p;
return num;
}

public static int MultNum(int q)
{
num *= q;
return num;
}
public static int getNum()
{
return num;
}

static void Main(string[] args)
{
// 创建委托实例
NumberChanger nc1 = new NumberChanger(AddNum);
NumberChanger nc2 = new NumberChanger(MultNum);
// 使用委托对象调用方法
nc1(25);
Console.WriteLine("Value of Num: {0}", getNum());
nc2(5);
Console.WriteLine("Value of Num: {0}", getNum());
Console.ReadKey();
}
}
}
当上面的代码被编译和执行时，它会产生下列结果：
Value of Num: 35
Value of Num: 175
委托的多播（Multicasting of a Delegate）
委托对象可使用 "+" 运算符进行合并。一个合并委托调用它所合并的两个委托。只有相同类型的委托可被合并。"-" 运算符可用于从合并的委托中移除组件委托。
使用委托的这个有用的特点，您可以创建一个委托被调用时要调用的方法的调用列表。这被称为委托的 多播（multicasting），也叫组播。下面的程序演示了委托的多播：
实例
using System;

delegate int NumberChanger(int n);
namespace DelegateAppl
{
class TestDelegate
{
static int num = 10;
public static int AddNum(int p)
{
num += p;
return num;
}

public static int MultNum(int q)
{
num *= q;
return num;
}
public static int getNum()
{
return num;
}

static void Main(string[] args)
{
// 创建委托实例
NumberChanger nc;
NumberChanger nc1 = new NumberChanger(AddNum);
NumberChanger nc2 = new NumberChanger(MultNum);
nc = nc1;
nc += nc2;
// 调用多播
nc(5);
Console.WriteLine("Value of Num: {0}", getNum());
Console.ReadKey();
}
}
}
当上面的代码被编译和执行时，它会产生下列结果：
Value of Num: 75
委托（Delegate）的用途
下面的实例演示了委托的用法。委托 printString 可用于引用带有一个字符串作为输入的方法，并不返回任何东西。
我们使用这个委托来调用两个方法，第一个把字符串打印到控制台，第二个把字符串打印到文件：
实例
using System;
using System.IO;

namespace DelegateAppl
{
class PrintString
{
static FileStream fs;
static StreamWriter sw;
// 委托声明
public delegate void printString(string s);

// 该方法打印到控制台
public static void WriteToScreen(string str)
{
Console.WriteLine("The String is: {0}", str);
}
// 该方法打印到文件
public static void WriteToFile(string s)
{
fs = new FileStream("c:\\message.txt", FileMode.Append, FileAccess.Write);
sw = new StreamWriter(fs);
sw.WriteLine(s);
sw.Flush();
sw.Close();
fs.Close();
}
// 该方法把委托作为参数，并使用它调用方法
public static void sendString(printString ps)
{
ps("Hello World");
}
static void Main(string[] args)
{
printString ps1 = new printString(WriteToScreen);
printString ps2 = new printString(WriteToFile);
sendString(ps1);
sendString(ps2);
Console.ReadKey();
}
}
}
当上面的代码被编译和执行时，它会产生下列结果：
The String is: Hello Wor

From <https://www.runoob.com/csharp/csharp-delegate.html>

 

---14位日期
Select replace( replace(replace(CONVERT(varchar(100), GETDATE(), 20),'-',''),':',''),' ','') --20210428133051
Select replace( replace( replace(replace(CONVERT(varchar(100), GETDATE(), 121),'-',''),':',''),' ',''),'.','') --20210428133321540
----时间对比
select DATEDIFF ([second], '2004-09-19 00:00:18', '2004-09-18 00:00:19')
select DATEDIFF ([second], '2004-09-19 00:00:18', GetDate())
---字符串转日期
select convert(datetime,substring(left('20210428133051',8)+' ' + substring('20210428133051',9,2)+':' + substring('20210428133051',11,2)+':' + substring('20210428133051',13,2),1,17))


DateTime dttttt = DateTime.ParseExact("20210625132750", "yyyyMMddHHmmss", System.Globalization.CultureInfo.CurrentCulture);
timeFrom = dttttt.ToString("yyyy-MM-dd HH:mm:ss");//sunjie -2021年6月28日15:37:49


DECLARE @start DATE,@end DATE ,@DateSpan int
set @DateSpan = -30
SET @start=dateadd(dd,@DateSpan, getdate())
SET @end=getdate()

select
DATEADD(DAY,number,@start) ymd
from master..spt_values
where type='p' AND
number<=DATEDIFF(day,@start,@end)

 

---14位日期
Select replace( replace(replace(CONVERT(varchar(100), GETDATE(), 20),'-',''),':',''),' ','') --20210428133051
Select replace( replace( replace(replace(CONVERT(varchar(100), GETDATE(), 121),'-',''),':',''),' ',''),'.','') --20210428133321540
----时间对比
select DATEDIFF ([second], '2004-09-19 00:00:18', '2004-09-18 00:00:19')
select DATEDIFF ([second], '2004-09-19 00:00:18', GetDate())
---字符串转日期
select convert(datetime,substring(left('20210428133051',8)+' ' + substring('20210428133051',9,2)+':' + substring('20210428133051',11,2)+':' + substring('20210428133051',13,2),1,17))


DateTime dttttt = DateTime.ParseExact("20210625132750", "yyyyMMddHHmmss", System.Globalization.CultureInfo.CurrentCulture);
timeFrom = dttttt.ToString("yyyy-MM-dd HH:mm:ss");//sunjie -2021年6月28日15:37:49


DECLARE @start DATE,@end DATE ,@DateSpan int
set @DateSpan = -30
SET @start=dateadd(dd,@DateSpan, getdate())
SET @end=getdate()

select
DATEADD(DAY,number,@start) ymd
from master..spt_values
where type='p' AND
number<=DATEDIFF(day,@start,@end)

第一大类:整数数据
bit:bit数据类型代表0,1或NULL,就是表示true,false.占用1byte.
int:以4个字节来存储正负数.可存储范围为:-2^31至2^31-1.
smallint:以2个字节来存储正负数.存储范围为:-2^15至2^15-1
tinyint: 是最小的整数类型,仅用1字节,范围:0至此^8-1
第二大类:近似浮点数值数据
float:用8个字节来存储数据.最多可为53位.范围为:-1.79E+308至1.79E+308.
real:位数为24,用4个字节,数字范围:-3.04E+38至3.04E+38
第三大类:日期时间数据
datatime:表示时间范围可以表示从1753/1/1至9999/12/31,时间可以表示到3.33/1000秒.使用8个字节.
smalldatetime:表示时间范围可以表示从1900/1/1至2079/12/31.使用4个字节.
第四大类:字符串数据
char:非Unincode编码，长度是固定的,最短为1字节,最长为8000个字节.不足的长度会用空白补上.
varchar: 非Unincode编码，长度可变,最短为1字节,最长为8000个字节,尾部的空白会去掉.
text: 非Unincode编码，长宽也是设定的,最长可以存放2G的数据.，存储长文本信息
nchar: Unincode编码，长度是固定的,最短为1字节,最长为4000个字节.不足的长度会用空白补上.储存一个字符需2个字节.
nvarchar: Unincode编码，长度是可变的,最短为1字节,最长为4000个字节.尾部的空白会去掉.储存一个字符需要2个字.
ntext: Unincode编码，长度是可变的,最短为1字节,最长为2G.尾部的空白会去掉,储存一个字符需要2个字节.
第五大类:货币数据类型
money:记录金额范围为:-92233720368577.5808至92233720368577.5807.需要8 个字节.
smallmoney:记录金额范围为:-214748.3648至214748.36487.需要4个字节.
第六大类:二进制码字符串数据
binary:固定长度的二进制码字符串字段,最短为1,最长为8000.
varbinary:与binary差异为数据尾部是00时,varbinary会将其去掉
image:为可变长度的二进制码字符串,最长2G.

 

drop table TBM_MC_UI
GO
CREATE TABLE TBM_MC_UI(
J_ID INT NOT NULL,
FCT_CODE VARCHAR (20) NOT NULL,
PLANT_CODE VARCHAR (20) NOT NULL,
UI_SCREEN_ID VARCHAR (20) NOT NULL,
UI_CTGR_CODE VARCHAR (20) ,
RESOL_CTGR_CODE VARCHAR (20) ,
SCREEN_NM VARCHAR (100) ,
SCREEN_DESC VARCHAR (100) ,
FILE_ID VARCHAR (100) ,
UI_URL VARCHAR (200) ,
FORM_ID VARCHAR (50) ,
MLANG_ID VARCHAR (50) ,
K_DESC VARCHAR (200) ,
REMARKS VARCHAR (200) ,
N1_EXTD_CULM_CONT VARCHAR (100) ,
N2_EXTD_CULM_CONT VARCHAR (100) ,
N3_EXTD_CULM_CONT VARCHAR (100) ,
N4_EXTD_CULM_CONT VARCHAR (100) ,
N5_EXTD_CULM_CONT VARCHAR (100) ,
DEL_YN VARCHAR (1) ,
USE_YN VARCHAR (1) ,
FST_REGER_ID VARCHAR (50) ,
FST_REG_DT date,
FST_REGER_IP VARCHAR (50) ,
FNL_UPDER_ID VARCHAR (50) ,
FNL_UPD_DT date,
FNL_UPDER_IP VARCHAR (50) -- ,
--PRIMARY KEY( J_ID,FCT_CODE,PLANT_CODE,UI_SCREEN_ID)
)
GO
ALTER TABLE TBM_MC_UI WITH NOCHECK ADD
CONSTRAINT [PK_TBM_MC_UI] PRIMARY KEY NONCLUSTERED
(
J_ID,FCT_CODE,PLANT_CODE,UI_SCREEN_ID
)
GO
SET ANSI_PADDING OFF
GO
SET ANSI_nulls on
go
set QUOTED_IDENTIFIER ON
GO

 

 

换一种：case when userName is null THEN '空' else '有值' end，这种写法好使；

From <https://www.cnblogs.com/yanshaoxiong/p/11469006.html>


if exists(select * from table1 where ...)
select section2
else
select section3

From <https://zhidao.baidu.com/question/316975625.html>

select section1
if(@@rowcount > 0) //@@rowcount 返回上一次查询结果所影响的行数。
select section2
else
select section3

From <https://zhidao.baidu.com/question/316975625.html>

 

 

sqlserver 

 

 

 

 

Oracle   

 

 

 

 

 

 

 


EXEC sp_GetProductionHourlyReport_Test
@Sector = 'WEH' ,
@CustomerGroup = 'HP-AIO',
@QMID = 0,
@CustomerID = 0,
@FactoryMARouteID = 0,
@Station = 'MI',
@Shift = 3,
@StartTime = '2021-07-01 07:00:00',
@EndTime = '2021-07-01 19:00:00' ,
@ShowEff = 0,
@Tester = NULL,
@CellLine = NULL
EXEC dbo.ProTest @OrderNO = N'单号001', @OrderName = N'名称001', @RMDSC = N'备注'
（或不写列名"EXEC dbo.ProTest N'单号001', N'名称001', N'备注';"，但不能混合使用，下同）

From <https://www.cnblogs.com/atlj/p/11184952.html>

存储过程学习
一、定义变量
--简单赋值
declare @a int
set @a=5
print @a

--使用select语句赋值
declare @user1 nvarchar(50)
select @user1='张三'
print @user1
declare @user2 nvarchar(50)
select @user2 = Name from ST_User where ID=1
print @user2

--使用update语句赋值
declare @user3 nvarchar(50)
update ST_User set @user3 = Name where ID=1
print @user3


二、表、临时表、表变量

--创建临时表1
create table #DU_User1
(
[ID] [int] NOT NULL,
[Oid] [int] NOT NULL,
[Login] [nvarchar](50) NOT NULL,
[Rtx] [nvarchar](4) NOT NULL,
[Name] [nvarchar](5) NOT NULL,
[Password] [nvarchar](max) NULL,
[State] [nvarchar](8) NOT NULL
);
--向临时表1插入一条记录
insert into #DU_User1 (ID,Oid,[Login],Rtx,Name,[Password],State) values (100,2,'LS','0000','临时','321','特殊');

--从ST_User查询数据，填充至新生成的临时表
select * into #DU_User2 from ST_User where ID<8

--查询并联合两临时表
select * from #DU_User2 where ID<3 union select * from #DU_User1

--删除两临时表
drop table #DU_User1
drop table #DU_User2

--创建临时表
CREATE TABLE #t
(
[ID] [int] NOT NULL,
[Oid] [int] NOT NULL,
[Login] [nvarchar](50) NOT NULL,
[Rtx] [nvarchar](4) NOT NULL,
[Name] [nvarchar](5) NOT NULL,
[Password] [nvarchar](max) NULL,
[State] [nvarchar](8) NOT NULL,
)

--将查询结果集（多条数据）插入临时表
insert into #t select * from ST_User
--不能这样插入
--select * into #t from dbo.ST_User

--添加一列，为int型自增长子段
alter table #t add [myid] int NOT NULL IDENTITY(1,1)
--添加一列，默认填充全球唯一标识
alter table #t add [myid1] uniqueidentifier NOT NULL default(newid())

select * from #t
drop table #t
--给查询结果集增加自增长列

--无主键时：
select IDENTITY(int,1,1)as ID, Name,[Login],[Password] into #t from ST_User
select * from #t

--有主键时：
select (select SUM(1) from ST_User where ID<= a.ID) as myID,* from ST_User a order by myID
--定义表变量
declare @t table
(
id int not null,
msg nvarchar(50) null
)
insert into @t values(1,'1')
insert into @t values(2,'2')
select * from @t
三、循环

--while循环计算1到100的和
declare @a int
declare @sum int
set @a=1
set @sum=0
while @a<=100
begin
set @sum+=@a
set @a+=1
end
print @sum
四、条件语句

--if,else条件分支
if(1+1=2)
begin
print '对'
end
else
begin
print '错'
end

--when then条件分支
declare @today int
declare @week nvarchar(3)
set @today=3
set @week=case
when @today=1 then '星期一'
when @today=2 then '星期二'
when @today=3 then '星期三'
when @today=4 then '星期四'
when @today=5 then '星期五'
when @today=6 then '星期六'
when @today=7 then '星期日'
else '值错误'
end
print @week


五、游标

declare @ID int
declare @Oid int
declare @Login varchar(50)

--定义一个游标
declare user_cur cursor for select ID,Oid,[Login] from ST_User
--打开游标
open user_cur
while @@fetch_status=0
begin
--读取游标
fetch next from user_cur into @ID,@Oid,@Login
print @ID
--print @Login
end
close user_cur
--摧毁游标
deallocate user_cur
六、触发器

触发器中的临时表：

Inserted
存放进行insert和update 操作后的数据
Deleted
存放进行delete 和update操作前的数据

--创建触发器
Create trigger User_OnUpdate
On ST_User
for Update
As
declare @msg nvarchar(50)
--@msg记录修改情况
select @msg = N'姓名从“' + Deleted.Name + N'”修改为“' + Inserted.Name + '”' from Inserted,Deleted
--插入日志表
insert into [LOG](MSG)values(@msg)

--删除触发器
drop trigger User_OnUpdate
七、存储过程

--创建带output参数的存储过程
CREATE PROCEDURE PR_Sum
@a int,
@b int,
@sum int output
AS
BEGIN
set @sum=@a+@b
END

--创建Return返回值存储过程
CREATE PROCEDURE PR_Sum2
@a int,
@b int
AS
BEGIN
Return @a+@b
END

--执行存储过程获取output型返回值
declare @mysum int
execute PR_Sum 1,2,@mysum output
print @mysum

--执行存储过程获取Return型返回值
declare @mysum2 int
execute @mysum2= PR_Sum2 1,2
print @mysum2



八、自定义函数

函数的分类：

1）标量值函数

2）表值函数

a:内联表值函数

b:多语句表值函数

3）系统函数


--新建标量值函数
create function FUNC_Sum1
(
@a int,
@b int
)
returns int
as
begin
return @a+@b
end

--新建内联表值函数
create function FUNC_UserTab_1
(
@myId int
)
returns table
as
return (select * from ST_User where ID<@myId)

--新建多语句表值函数
create function FUNC_UserTab_2
(
@myId int
)
returns @t table
(
[ID] [int] NOT NULL,
[Oid] [int] NOT NULL,
[Login] [nvarchar](50) NOT NULL,
[Rtx] [nvarchar](4) NOT NULL,
[Name] [nvarchar](5) NOT NULL,
[Password] [nvarchar](max) NULL,
[State] [nvarchar](8) NOT NULL
)
as
begin
insert into @t select * from ST_User where ID<@myId
return
end

--调用表值函数
select * from dbo.FUNC_UserTab_1(15)
--调用标量值函数
declare @s int
set @s=dbo.FUNC_Sum1(100,50)
print @s

--删除标量值函数
drop function FUNC_Sum1
谈谈自定义函数与存储过程的区别：

一、自定义函数：

1. 可以返回表变量

2. 限制颇多，包括

不能使用output参数；

不能用临时表；

函数内部的操作不能影响到外部环境；

不能通过select返回结果集；

不能update，delete，数据库表；

3. 必须return 一个标量值或表变量

自定义函数一般用在复用度高，功能简单单一，争对性强的地方。

二、存储过程

1. 不能返回表变量

2. 限制少，可以执行对数据库表的操作，可以返回数据集

3. 可以return一个标量值，也可以省略return

存储过程一般用在实现复杂的功能，数据操纵方面。

From <https://www.cnblogs.com/lihuiqi/p/10471740.html>

 

 

 


SELECT substring(replace( replace(replace(CONVERT(varchar(100), GETDATE(), 120),' ',''),'-',''),':',''),1,8)


select cast(substring(replace( replace(replace(CONVERT(varchar(100), GETDATE(), 120),' ',''),'-',''),':',''),1,8)) as varchar


SELECT cast(datepart(week,cast(substring(replace( replace(replace(CONVERT(varchar(100), GETDATE(), 120),' ',''),'-',''),':',''),1,8) as varchar))as varchar) + 'W'

 

所有 周数据

declare @sDate datetime = '2022-01-01'
,@eDate datetime= '2022-12-31'

select d2.weekRange,d2.FirstDay,d2.EndDay from
(
select datepart(WEEK,d.dates) weekRange
,dateadd(Day,1-(DATEPART(Weekday,d.dates)+@@DATEFIRST-1)%7,d.dates) FirstDay
,dateadd(wk, datediff(wk,0,d.dates), 6) EndDay
from
(
select dateadd(dd,number,@sDate) AS dates from master..spt_values
where type='p' and dateadd(dd,number,@sDate)<=@eDate
) d
) d2 group by d2.weekRange,d2.FirstDay,d2.EndDay

 

周六周天 本月
select * from(
select convert(varchar(8),GETDATE(),120)+ltrim(number) as [date]
from master..spt_values
where type='P' and number between 1 and DAY(GETDATE())
)t
where DATEPART(W,[date])=7 or DATEPART(W,[date])=1

 


select * from AVL_AVLResult where datepart(weekday, sgd006) NOT IN(1,7)


select COUNT(1) as [不是星期六的天数] from(
select convert(varchar(8),GETDATE(),120)+ltrim(number) as [date]
from master..spt_values where type='P' and number between 1 and DAY(GETDATE()))t
where DATEPART(W,[date])<>7 and DATEPART(W,[date])<>6

/*不是星期六的天数11*/

 

select COUNT(1) as [不是星期六的天数] from(
select convert(varchar(8),GETDATE(),120)+ltrim(number) as [date]
from master..spt_values
where type='P' and number between 1 and DAY(GETDATE())
)t
where DATEPART(W,[date])<>7 and DATEPART(W,[date])<>6


select * from(
select convert(varchar(8),GETDATE(),120)+ltrim(number) as [date]
from master..spt_values
where type='P' and number between 1 and DAY(GETDATE())
)t
where DATEPART(W,[date])<>7 and DATEPART(W,[date])<>6

------- 周日期范围
declare @sDate datetime = '2022-01-01'
,@eDate datetime= '2022-12-31'
select * from(
select convert(varchar(8),GETDATE(),120)+ltrim(number) as [date]
from master..spt_values
where type='P' and number between 1 and DAY(GETDATE())
)t
where DATEPART(W,[date])=7 or DATEPART(W,[date])=1

 

--- 一个月 日期
select convert(varchar(8),GETDATE(),120)+ltrim(number) as [date]
from master..spt_values
where type='P' and number between 1 and DAY(GETDATE())

------------- 周六 周天
SELECT convert(varchar(8),GETDATE(),120)+ltrim(number) as [date]
FROM MASTER..SPT_VALUES
WHERE TYPE = 'P'
AND DATEADD(DAY,NUMBER,'2022-01-01')<='2022-12-31'
AND DATEPART(WEEKDAY,DATEADD(DAY,NUMBER,'2022-12-31')) IN (1,2)

 

 

 

 


------------------------------------------------------------------查数据库大小-----

SELECT database_id AS DataBaseId,DB_NAME(database_id) AS DataBaseName,
CAST(SUM(SIZE)*8.0/1024/1024 AS DECIMAL(9, 4)) AS [SizeGB]
FROM sys.master_files
GROUP BY database_id order by [SizeGB] desc

select * from sys.master_files where database_id = '8'


------------------------------------------------------------------查各表记录数-----

select a.name as tab_name,max(b.rows) as row_num
from sysobjects a,sysindexes b
where a.id=b.id and a.xtype='u'
group by a.name
order by max(b.rows) desc



SELECT ('Wk'+cast(datepart(week,ACRS_YMD) as varchar)) WEEK_PASS ,sum(1) WEEK_PASS_SUM,cast('98.00' as decimal(10,1) ) Goal into #weekPass FROM [TBM_PM_PROD_PRGS]
where 1= 1 and OPERATE_PASS = 'Y' and FST_REG_DT > dateadd(MM,-1,getdate()) and QC_DECI_PASS_YN = 'Y' and PACK_QC_DECI_PASS_YN = 'Y' ---- 周别的不良个数 百分比目标
group by ('Wk'+cast(datepart(week,ACRS_YMD) as varchar)) ;
SELECT ('Wk'+cast(datepart(week,ACRS_YMD) as varchar)) WEEK_OK ,sum(1) WEEK_OK_SUM,cast('98.00' as decimal(10,1) ) Goal into #weekok FROM [TBM_PM_PROD_PRGS]
where 1= 1 and FST_REG_DT > dateadd(MM,-1,getdate()) and QC_DECI_PASS_YN = 'Y' and PACK_QC_DECI_PASS_YN = 'Y' ---- 周别的不良个数 百分比目标
group by ('Wk'+cast(datepart(week,ACRS_YMD) as varchar)) ;

select A.*, case when D.WEEK_PASS_SUM is null then 0 else D.WEEK_PASS_SUM end as weekdefect,
round(
(
(case when D.WEEK_PASS_SUM is NULL then '0' else D.WEEK_PASS_SUM end ) /
cast(A.WEEK_OK_SUM as decimal(10,1) )
) * 100
,2) as weekpersent
from #weekok A
left join #weekPass D
on A.WEEK_OK = D.WEEK_PASS order by A.WEEK_OK ;


drop table #weekPass;
drop table #weekok;

SELECT (cast(substring(ACRS_YMD,5,2)as varchar)+'Month') MONTH_PROD,sum(1) MONTH_PROD_SUM FROM TBM_PM_PROD_PRGS
where 1= 1 and FST_REG_DT > dateadd(MM,-1,getdate()) and QC_DECI_PASS_YN = 'Y' and PACK_QC_DECI_PASS_YN = 'Y' ---- 月别总数据
group by (cast(substring(ACRS_YMD,5,2)as varchar)+'Month') ;
SELECT (cast(substring(ACRS_YMD,5,2)as varchar)+'Month') MONTH_PROD_Y ,sum(1) MONTH_PROD_YSUM FROM TBM_PM_PROD_PRGS
where 1= 1 and FST_REG_DT > dateadd(MM,-1,getdate()) and QC_DECI_PASS_YN = 'Y' and PACK_QC_DECI_PASS_YN = 'Y' ---- 月别 Y 数据
and OPERATE_PASS = 'Y' group by (cast(substring(ACRS_YMD,5,2)as varchar)+'Month');
SELECT (cast(substring(ACRS_YMD,5,2)as varchar)+'Month') MONTH_PROD_N ,sum(1) MONTH_PROD_NSUM FROM TBM_PM_PROD_PRGS
where 1= 1 and FST_REG_DT > dateadd(MM,-1,getdate()) and QC_DECI_PASS_YN = 'Y' and PACK_QC_DECI_PASS_YN = 'Y' ---- 月别 N 数据
and OPERATE_PASS = 'N' group by (cast(substring(ACRS_YMD,5,2)as varchar)+'Month') ;

SELECT top 6 substring( PRODC_CHK_TYPE,1,16) as PRODC_CHK_TYPE ,sum(1) DEFT_TYPE_SUM FROM [TBD_PM_PROD_PRGS]
where 1 =1 and FST_REG_DT > dateadd(MM,-1,getdate()) and N1_EXTD_CULM_CONT = 'Y' and DEL_YN = 'N' and USE_YN = 'Y' ----不良区分 饼状图 month
group by substring( PRODC_CHK_TYPE,1,16) order by DEFT_TYPE_SUM desc;

 

SELECT BAY_CODE , ('Wk'+cast(datepart(week,ACRS_YMD) as varchar)) as weekdate,sum(1) BAY_PROD_SUM into #baysum FROM [TBM_PM_PROD_PRGS]
where 1 =1 and FST_REG_DT > dateadd(MM,-1,getdate()) and QC_DECI_PASS_YN = 'Y' and PACK_QC_DECI_PASS_YN = 'Y' ----产线别生产产品数量 month
group by BAY_CODE , ('Wk'+cast(datepart(week,ACRS_YMD) as varchar));
SELECT BAY_CODE , ('Wk'+cast(datepart(week,ACRS_YMD) as varchar)) as weekdate,sum(1) BAY_PROD_YSUM into #bayysum FROM [TBM_PM_PROD_PRGS]
where 1 =1 and FST_REG_DT > dateadd(MM,-1,getdate()) and OPERATE_PASS = 'Y'　 and QC_DECI_PASS_YN = 'Y' and PACK_QC_DECI_PASS_YN = 'Y' ----产线别生产产品数量 month
group by BAY_CODE , ('Wk'+cast(datepart(week,ACRS_YMD) as varchar));
SELECT BAY_CODE , ('Wk'+cast(datepart(week,ACRS_YMD) as varchar)) as weekdate,sum(1) BAY_PROD_NSUM into #baynsum FROM [TBM_PM_PROD_PRGS]
where 1 =1 and FST_REG_DT > dateadd(MM,-1,getdate()) and OPERATE_PASS = 'N'　 and QC_DECI_PASS_YN = 'Y' and PACK_QC_DECI_PASS_YN = 'Y' ----产线别生产产品数量 month
group by BAY_CODE , ('Wk'+cast(datepart(week,ACRS_YMD) as varchar));

 

select A.*,case when N.BAY_PROD_NSUM is NULL then '0' else N.BAY_PROD_NSUM end as BAY_PROD_NSUM , round(
(
(case when N.BAY_PROD_NSUM is NULL then '0' else N.BAY_PROD_NSUM end ) /
cast(A.BAY_PROD_SUM as decimal(10,1) )
) * 100
,2) as persent ,round((
(
(case when N.BAY_PROD_NSUM is NULL then '0' else N.BAY_PROD_NSUM end ) /
cast(A.BAY_PROD_SUM as decimal(10,1) )
) * 1000000
) ,2) as DPPM from #baysum A
left join #baynsum N
on A.BAY_CODE = N.BAY_CODE and A.weekdate = N.weekdate
order by BAY_CODE,weekdate;

 


SELECT ('Wk'+cast(datepart(week,ACRS_YMD) as varchar)) as weekdate,sum(1) BAY_PROD_NNN into #baytotalsumN FROM [TBM_PM_PROD_PRGS]
where 1 =1 and FST_REG_DT > dateadd(MM,-1,getdate()) and OPERATE_PASS = 'N'　 and QC_DECI_PASS_YN = 'Y' and PACK_QC_DECI_PASS_YN = 'Y' ----产线别生产产品数量 month
group by ('Wk'+cast(datepart(week,ACRS_YMD) as varchar));

SELECT ('Wk'+cast(datepart(week,ACRS_YMD) as varchar)) as weekdate,sum(1) BAY_PROD_YYY into #baytotalsumY FROM [TBM_PM_PROD_PRGS]
where 1 =1 and FST_REG_DT > dateadd(MM,-1,getdate()) and OPERATE_PASS = 'Y'　　 and QC_DECI_PASS_YN = 'Y' and PACK_QC_DECI_PASS_YN = 'Y' ----产线别生产产品数量 month
group by ('Wk'+cast(datepart(week,ACRS_YMD) as varchar));


SELECT ('Wk'+cast(datepart(week,ACRS_YMD) as varchar)) as weekdate,sum(1) BAY_PROD_SSS into #baytotalsum FROM [TBM_PM_PROD_PRGS]
where 1 =1 and FST_REG_DT > dateadd(MM,-1,getdate()) 　 and QC_DECI_PASS_YN = 'Y' and PACK_QC_DECI_PASS_YN = 'Y' ----产线别生产产品数量 month
group by ('Wk'+cast(datepart(week,ACRS_YMD) as varchar));

 

SELECT top 6 Model_code,sum(1) BAY_PROD_WeekN into #ModelWeeksumN FROM [TBM_PM_PROD_PRGS]
where 1 =1 and FST_REG_DT > dateadd(dd,-7,getdate()) and OPERATE_PASS = 'N'　　 and QC_DECI_PASS_YN = 'Y' and PACK_QC_DECI_PASS_YN = 'Y' ----产线别生产产品数量 month
group by Model_code order by BAY_PROD_WeekN desc;

select AA.*,case when NN.BAY_PROD_NNN is NULL then '0' else NN.BAY_PROD_NNN end as BAY_PROD_NNN ,round((
(
(case when NN.BAY_PROD_NNN is NULL then '0' else NN.BAY_PROD_NNN end ) /
cast(AA.BAY_PROD_SSS as decimal(10,1) )
) * 1000000
) ,2) as DPPMSum ,1800 as DPPM_Goal from #baytotalsum AA
left join #baytotalsumN NN
on AA.weekdate = NN.weekdate
order by weekdate;

-- drop table #baytotalsumN ;
-- drop table #baytotalsumY ;
-- drop table #baytotalsum ;


select *from #bayysum;
select *from #baynsum;
select *from #baysum;
select *from #baytotalsumN;
select *from #baytotalsumY;
select * from #ModelWeeksumN ;

drop table #ModelWeeksumN;
drop table #baysum;
drop table #bayysum;
drop table #baynsum;
drop table #baytotalsumN;
drop table #baytotalsumY;
drop table #baytotalsum ;

 


DECLARE @start DATE,@end DATE ,@DateSpan int ,@DateSpancount int ,@allcount int ,@NGcount int ,@OKcount int ,@MAxcount int ,@MINcount int ,@Linecount int ,@averagep float,@sump float
set @DateSpan = -29
set @DateSpancount = (-@DateSpan) + 1
SET @start=dateadd(dd,@DateSpan, getdate())
SET @end=getdate()
select
replace(cast( DATEADD(DAY,number,@start) as varchar),'-','') ymd into #monthdays
from master..spt_values
where type='p' AND
number<=DATEDIFF(day,@start,@end);

--;with NGlist as
--(
select a.ymd,b.PRODC_MAGT_NO,b.operate_pass into #NGlist from #monthdays a inner join TBM_PM_PROD_PRGS b on a.ymd=b.acrs_ymd where operate_pass='N' and QC_DECI_PASS_YN = 'Y' and PACK_QC_DECI_PASS_YN = 'Y' ;
--)
--, OKlist as
--(
select a.ymd,b.PRODC_MAGT_NO,b.operate_pass into #OKlist from #monthdays a inner join TBM_PM_PROD_PRGS b on a.ymd=b.acrs_ymd where operate_pass='Y' and QC_DECI_PASS_YN = 'Y' and PACK_QC_DECI_PASS_YN = 'Y' ;
--select * from #NGlist
--1027
--)

--select a.ymd,b.PRODC_MAGT_NO,b.operate_pass from #monthdays a left join TBM_PM_PROD_PRGS b on a.ymd=b.acrs_ymd ;

--update TBM_PM_PROD_PRGS set PACK_QC_DECI_PASS_YN = 'Y',QC_DECI_PASS_YN = 'Y'


select ymd,sum(1) as Ngnumber into #NGlist2 from #NGlist group by ymd ;
select ymd,sum(1) as OKnumber into #OKlist2 from #OKlist group by ymd ;
select ymd,sum(1) as Allnumber into #monthdays2 from #monthdays group by ymd ;

select
aa.ymd ,
(case when ab.Ngnumber is null then 0 else ab.Ngnumber end) as Ngnumber,
(case when ac.OKnumber is null then 0 else ac.OKnumber end) as OKnumber,
(case when ad.Allnumber is null then 0 else ad.Allnumber end) as Allnumber
into #resultdata from #monthdays aa left join #NGlist2 ab on aa.ymd =ab.ymd
left join #OKlist2 ac on aa.ymd =ac.ymd
left join #monthdays2 ad on aa.ymd =ad.ymd ;
select @MAxcount = max(Ngnumber +OKnumber ) from #resultdata where (Ngnumber +OKnumber) <> 0 ;
select @MINcount = min(Ngnumber +OKnumber ) from #resultdata where (Ngnumber +OKnumber) <> 0 ;
select ymd,Ngnumber,OKnumber,(Ngnumber +OKnumber ) as nowdata ,
round
(((cast((case when Ngnumber is null then 0 else Ngnumber end) as decimal(10,5)))/
(cast((case when (Ngnumber +OKnumber ) = 0 then 1 else (Ngnumber +OKnumber ) end )as decimal(10,5)))) --* 100
, 2)as ppp ,
round
(((cast((case when Ngnumber is null then 0 else Ngnumber end) as decimal(10,5)))/
(cast((case when (Ngnumber +OKnumber ) = 0 then 1 else (Ngnumber +OKnumber ) end )as decimal(10,5)))) * 100
, 2)as ppp100 into #pchartdata
from #resultdata;
select @NGcount = count(1) from #NGlist;
select @OKcount = count(1) from #OKlist;
select @Linecount = count(1) from #pchartdata where nowdata <> 0 ;
select @averagep = round((cast( @NGcount as decimal(10,5))/cast( (@OKcount+ @NGcount)as decimal(10,5))) , 10) from #pchartdata ;
select *,@averagep as aver,@averagep * 100 as aver100,@DateSpancount childgroupcount,(@NGcount + @OKcount) as allcount ,@NGcount as NGcount,@OKcount as OKcount,
@MAxcount as MAXcount ,@MINcount as MINcount,@Linecount as Linecount ,((cast( @MINcount as decimal(10,5))/ cast( @MAxcount as decimal(10,5))) ) as MinMaxPersent ,
case
--select cast( 38 as decimal(10,5))/1036
/*
=IF(当前组个数=",",
IF(最小组个数/最大组个数>=0.75,
组数+3*SQRT(ABS(组数*(1-组数)/平均个数)),
组数+3*SQRT(ABS(组数*(1-组数)/当前组个数))
)
)
*/
when ((cast( @MINcount as decimal(10,5))/ cast( @MAxcount as decimal(10,5))) >= 0.75)
then
(@averagep + 3*SQRT(ABS(@averagep*(1-(@averagep))/((@NGcount +@OKcount)/ @Linecount))))
else
(@averagep + 3*SQRT(ABS(@averagep*(1-(@averagep))/( nowdata))))
end as UCL_P ,
case
when ((cast( @MINcount as decimal(10,5))/ cast( @MAxcount as decimal(10,5))) >= 0.75)
then
case when ((@averagep - 3*SQRT(ABS(@averagep*(1-(@averagep))/((@NGcount +@OKcount)/ @Linecount)))) < 0)
then
0
else
(@averagep - 3*SQRT(ABS(@averagep*(1-(@averagep))/((@NGcount +@OKcount)/ @Linecount))))
end
else
case when (@averagep - 3*SQRT(ABS(@averagep*(1-(@averagep))/(nowdata))) < 0)
then
0
else
(@averagep - 3*SQRT(ABS(@Linecount*(1-(@averagep/100))/(nowdata))))
end
end as LCL_P
/*
=IF(当前组个数=",",
IF(最小组个数/最大组个数>=0.75,
IF((组数-3*SQRT(ABS(组数*(1-组数)/平均个数)))<0,
0,
(组数-3*SQRT(ABS(组数*(1-组数)/平均个数)))),
IF((K31-3*SQRT(ABS(组数*(1-组数)/当前组个数)))<0,
0,
(组数-3*SQRT(ABS(组数*(1-组数)/当前组个数))))
)
)
*/
into #result from #pchartdata where nowdata <> 0 order by ymd ;
select * from #result;
select ROW_NUMBER() Over(ORDER BY ymd) as rowId ,convert(varchar(100), convert(date,ymd),23) as DateYmd --,*
, round(ppp100,3) as ppp100
, round(aver100,3) as aver100
, round(UCL_P*100,3) as UCL_P100
, round(LCL_P*100,3) as LCL_P100
,CONVERT(varchar(100),@start, 120) as startdate
,CONVERT(varchar(100),@end, 120) as enddate
from #result;

drop table #result;
drop table #monthdays;
drop table #resultdata;
drop table #NGlist;
drop table #OKlist;
drop table #monthdays2;
drop table #NGlist2;
drop table #OKlist2;
drop table #pchartdata;

 

 

 

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

;with
maxseq
as
(
SELECT SerialNumber, RunNumber FROM [InfoRun] B where datetimestarted > dateadd(dd,-1,getdate()) and RunNumber = 1 and ( B.SerialNumber like '%0620' or B.SerialNumber like '%0622' )
),
datalist
as
(
select B.SerialNumber , B.RunNumber ,[ProjectVersion] ,
( 'Wk' +cast(datepart(week,cast(substring(replace( replace(replace(CONVERT(varchar(100),DateTimeStarted, 120),' ',''),'-',''),':',''),1,8) as varchar))as varchar) ) as weekly ,
[DateTimeStarted] ,[DateTimeCompleted] ,--[ElapsedTime] ,
[StationNumber] ,[StationName] ,[Status] ,[FailedTests] ,--[GroupRunNumber] ,
[FailedTestsDetails]
FROM [InfoRun] B inner join maxseq on B.SerialNumber = maxseq.SerialNumber and B.RunNumber = maxseq.RunNumber where B.datetimestarted > dateadd(dd,-1,getdate()) and ( B.SerialNumber like '%0620' or B.SerialNumber like '%0622' )
)
select * into #tempdatalist from datalist ;
with
nglist
as
(
select B.SerialNumber , B.RunNumber ,[ProjectVersion] ,
( 'Wk' +cast(datepart(week,cast(substring(replace( replace(replace(CONVERT(varchar(100),DateTimeStarted, 120),' ',''),'-',''),':',''),1,8) as varchar))as varchar) ) as week ,
[DateTimeStarted] ,[DateTimeCompleted] ,--[ElapsedTime] ,
[StationNumber] ,[StationName] ,[Status] ,[FailedTests] ,
substring([FailedTests],1,15) as failtype,
--[GroupRunNumber] ,
[FailedTestsDetails]
FROM [InfoRun] B where 1 =1 and ( B.SerialNumber like '%0620' or B.SerialNumber like '%0622' )
and B.RunNumber = 1
and B.Status='FAIL'
and B.datetimestarted > dateadd(dd,-1,getdate())
)
select top(5) failtype,count(1) as numberlist into #temptop5 from nglist group by failtype order by numberlist desc;
--select weekly,count(1) as summerynumber from datalist where 1 = 1 group by weekly order by weekly
select weekly,count(1) as Passnumber into #tempPassdatalist from #tempdatalist where Status='PASS' group by weekly order by weekly;
select weekly,count(1) as summerynumber into #tempAlldatalist from #tempdatalist where 1 = 1 group by weekly order by weekly;
select
(case when A.weekly is null then B.weekly else A.weekly end) as weekA,A.*,
(case when A.summerynumber is null then 0 else A.summerynumber end) as summery,
(case when B.weekly is null then A.weekly else B.weekly end) as weekB,B.*,
(case when B.Passnumber is null then 0 else B.Passnumber end) as pass,
round
(((case when B.Passnumber is null then 0 else B.Passnumber end)/(case when A.summerynumber is null then 0 else A.summerynumber end)) * 100, 2)as ppp,
round
(((cast((case when B.Passnumber is null then 0 else B.Passnumber end)
as decimal(10,5)))/(cast((case when A.summerynumber is null then 0 else A.summerynumber end )as decimal(10,5)))) * 100, 2) as FTT, 0 as FTT0 , 75 as FTTGoal
--B.weekly as weekB,B.*
from #tempAlldatalist A
full outer join #tempPassdatalist B
on A.weekly = B.weekly;
select * from #temptop5;
drop table #tempdatalist;
drop table #tempAlldatalist;
drop table #tempPassdatalist;
drop table #temptop5;
---------------------------------------------------------------------------------------------------------------------------------------------------------

 

 

posted @ 2022-03-09 07:36  2eggs  Views(83)  Comments(9)  Edit  收藏  举报