首页 / MSSQL / buglist系统中涉及的sql SqlServer版本

buglist系统中涉及的sql SqlServer版本

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了buglist系统中涉及的sql SqlServer版本，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含8825字，纯文字阅读大概需要13分钟。

内容图文

use test;


select bug_id,project_name,cr_name,bug_cr_num,bug_task_num,object_name,bug_description,bug_rca,bug_solution,b.employ_name developer,a.employ_name tester,qa_creationdt,qa_updatedt,bug_deleted_flag
from qa_buglist
join qa_project on bug_project_id = project_id  
join qa_crtype on cr_id = bug_cr_type_id  
join qa_rtype on object_id = qa_type_id  
join qa_employ a on a.employ_id = qa_tester_id and a.employ_group = 1   
join qa_employ b on b.employ_id= qa_assignee_id and b.employ_group = 2  
where bug_deleted_flag =0 order by 1 desc


select count(1)
from qa_buglist
join qa_project on bug_project_id = project_id  
join qa_crtype on cr_id = bug_cr_type_id  
join qa_rtype on object_id = qa_type_id  
join qa_employ a on a.employ_id = qa_tester_id and a.employ_group = 1   
join qa_employ b on b.employ_id= qa_assignee_id and b.employ_group = 2  
where bug_deleted_flag =0 

SELECT * FROM QA_BUGLIST;

CREATE TABLE `QA_BUGLIST`(
`BUG_ID` int(4) NOT NULL AUTO_INCREMENT,
`BUG_PROJECT_ID` int,
`BUG_CR_TYPE_ID` int,
`BUG_CR_NUM` VARCHAR(100),
`BUG_TASK_NUM` VARCHAR(100),
`QA_TYPE_ID` int,
`BUG_DESCRIPTION` VARCHAR(2000),
`BUG_RCA` VARCHAR(2000),
`BUG_SOLUTION` VARCHAR(2000),
`QA_ASSIGNEE_ID` int,
`QA_TESTER_ID` int,
`QA_CREATIONDT` datetime,
`QA_UPDATEDT` datetime,
`BUG_DELETED_FLAG` BIT default 0,
`BUG_DELETED_COMMENT` VARCHAR(100),
PRIMARY KEY (`BUG_ID`)
)ENGINE=InnoDB AUTO_INCREMENT=300;

alter table qa_buglist add column bug_status_id int default 1 not null 

ALTER TABLE `article`
ADD CONSTRAINT `fk_1` FOREIGN KEY (`category_id`) REFERENCES `category` (`id`);

ALTER TABLE `QA_BUGLIST`  ADD  CONSTRAINT `FK_BUG_PROJECT` FOREIGN KEY(`BUG_PROJECT_ID`) REFERENCES `QA_PROJECT` (`PROJECT_ID`);
ALTER TABLE QA_BUGLIST  ADD  CONSTRAINT `FK_BUG_CR_TYPE` FOREIGN KEY(BUG_CR_TYPE_ID) REFERENCES `QA_CRTYPE` (`CR_ID`);
ALTER TABLE QA_BUGLIST  ADD  CONSTRAINT `FK_BUG_QA_TYPE_ID` FOREIGN KEY(QA_TYPE_ID) REFERENCES `QA_RTYPE` (OBJECT_ID);
ALTER TABLE QA_BUGLIST   ADD  CONSTRAINT `FK_BUG_ASSIGN_ID` FOREIGN KEY(`QA_ASSIGNEE_ID`) REFERENCES QA_EMPLOY (EMPLOY_ID);
ALTER TABLE QA_BUGLIST ADD  CONSTRAINT `FK_BUG_qa_ID` FOREIGN KEY(`QA_TESTER_ID`) REFERENCES QA_EMPLOY (EMPLOY_ID);


SELECT * FROM QA_BUGLIST;

INSERT QA_BUGLIST(BUG_PROJECT_ID,BUG_CR_TYPE_ID,BUG_CR_NUM,BUG_TASK_NUM,QA_TYPE_ID,BUG_DESCRIPTION,BUG_RCA,BUG_SOLUTION,QA_ASSIGNEE_ID,QA_TESTER_ID,QA_CREATIONDT,QA_UPDATEDT)
VALUES(3,2,NULL,'',3,'注册接口存在幂等性问题','唯一键','jiejue',1,27,curdate(),curdate());




INSERT QA_PROJECT(PROJECT_NAME,PROJECT_CREATION_DT,PROJECT_UPDATE_DT,PROJECT_DELETED_FLAG)
VALUES('alimama',now(),now(),0)


SELECT * FROM QA_PROJECT;
SELECT * FROM QA_CRTYPE;
SELECT * FROM QA_RTYPE;
SELECT * FROM QA_EMPLOY;


CREATE TABLE `QA_BUGSTATUS` (
`STATUS_ID` INT NOT NULL AUTO_INCREMENT,
`STATUS_DES` VARCHAR(100),
`STATUS_CREATION_DT` DATE,
`STATUS_UPDATE_DT` DATE,
`STATUS_DELETED_FLAG` BIT DEFAULT 0,
PRIMARY KEY (`STATUS_ID`)
)ENGINE=INNODB AUTO_INCREMENT=1;


CREATE TABLE `QA_PROJECT` (
`PROJECT_ID` INT NOT NULL AUTO_INCREMENT,
`PROJECT_NAME` VARCHAR(100),
`PROJECT_CREATION_DT` DATE,
`PROJECT_UPDATE_DT` DATE,
`PROJECT_DELETED_FLAG` BIT default 0,
PRIMARY KEY (`PROJECT_ID`)
)ENGINE=InnoDB AUTO_INCREMENT=1;


INSERT QA_CRTYPE(CR_NAME,CR_CREATION_DT,CR_UPDATE_DT) VALUES('内部不收费CR',curdate(),curdate());
INSERT QA_CRTYPE(CR_NAME,CR_CREATION_DT,CR_UPDATE_DT) VALUES('标准收费CR',curdate(),curdate());
INSERT QA_CRTYPE(CR_NAME,CR_CREATION_DT,CR_UPDATE_DT) VALUES('bugFix CR',curdate(),curdate());


CREATE TABLE `QA_CRTYPE` (
`CR_ID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`CR_NAME` VARCHAR(100),
`CR_CREATION_DT` DATE,
`CR_UPDATE_DT` DATE,
`CR_DELETED_FLAG` BIT default 0)
ENGINE=InnoDB AUTO_INCREMENT=1;





SELECT * FROM QA_EMPLOY;
SELECT * FROM QA_RTYPE;

SET SQL_SAFE_UPDATES = 0;
update QA_EMPLOY set employ_group =2 where EMPLOY_NAME =  '张三';

INSERT into  `QA_EMPLOY`(`EMPLOY_NAME`,`EMPLOY_CREATION_DT`,`EMPLOY_UPDATE_DT`) VALUES('张三',curdate(),curdate());
INSERT into  `QA_EMPLOY`(`EMPLOY_NAME`,`EMPLOY_CREATION_DT`,`EMPLOY_UPDATE_DT`) VALUES('李四',curdate(),curdate());

INSERT QA_RTYPE(OBJECT_NAME,OBJECT_CREATION_DT,OBJECT_UPDATE_DT) VALUES('UAT测试','20181204','20181204') ;
INSERT QA_RTYPE(OBJECT_NAME,OBJECT_CREATION_DT,OBJECT_UPDATE_DT) VALUES('proc测试','20181204','20181204') ;
INSERT QA_RTYPE(OBJECT_NAME,OBJECT_CREATION_DT,OBJECT_UPDATE_DT) VALUES('api测试','20181204','20181204') ;
INSERT QA_RTYPE(OBJECT_NAME,OBJECT_CREATION_DT,OBJECT_UPDATE_DT) VALUES('性能测试','20181204','20181204') ;
INSERT QA_RTYPE(OBJECT_NAME,OBJECT_CREATION_DT,OBJECT_UPDATE_DT) VALUES('ui测试','20181204','20181204') ;
INSERT QA_RTYPE(OBJECT_NAME,OBJECT_CREATION_DT,OBJECT_UPDATE_DT) VALUES('自动化测试','20181204','20181204') ;


CREATE TABLE `QA_RTYPE` (
`OBJECT_ID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`OBJECT_NAME` VARCHAR(100),
`OBJECT_CREATION_DT` DATE,
`OBJECT_UPDATE_DT` DATE,
`OBJECT_DELETED_FLAG` BIT DEFAULT 0)
ENGINE=InnoDB AUTO_INCREMENT=1;


##DROP TABLE  QA_RTYPE

CREATE TABLE `QA_EMPLOY` (
`EMPLOY_ID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`EMPLOY_NAME` VARCHAR(100),
`EMPLOY_CREATION_DT` date,
`EMPLOY_UPDATE_DT` date,
`EMPLOY_DELETED_FLAG` BIT default 0)
ENGINE=InnoDB AUTO_INCREMENT=1;


ALTER TABLE QA_EMPLOY ADD EMPLOY_GROUP INT DEFAULT 1;
####增加了一个字段，将测试和开发人员放在同一张表中


SELECT PROJECT_NAME,CR_NAME,BUG_CR_NUM,BUG_TASK_NUM,OBJECT_NAME,BUG_DESCRIPTION
,BUG_RCA,BUG_SOLUTION
###B.EMPLOY_NAME,QA_ASSIGNEE_ID,
###A.EMPLOY_NAME,QA_TESTER_ID
,QA_CREATIONDT,QA_UPDATEDT
FROM QA_BUGLIST
JOIN  QA_PROJECT ON BUG_PROJECT_ID = PROJECT_ID
JOIN  QA_CRTYPE ON CR_ID = BUG_CR_TYPE_ID
JOIN  QA_RTYPE ON OBJECT_ID = QA_TYPE_ID
JOIN QA_EMPLOY A ON A.`EMPLOY_ID` = QA_TESTER_ID AND A.`EMPLOY_GROUP` = 1  ###表示测试
JOIN QA_EMPLOY  B ON B.`EMPLOY_ID` = QA_ASSIGNEE_ID AND B.`EMPLOY_GROUP` = 2   ###表示开发人员
;





SELECT *
FROM QA_BUGLIST  JOIN QA_PROJECT ON BUG_PROJECT_ID = PROJECT_ID
JOIN QA_CRTYPE ON CR_ID = BUG_CR_TYPE_ID
JOIN QA_RTYPE ON OBJECT_ID = QA_TYPE_ID
JOIN QA_EMPLOY A ON A.EMPLOY_ID = QA_TESTER_ID AND A.EMPLOY_GROUP = 1
JOIN QA_EMPLOY B ON B.EMPLOY_ID= QA_ASSIGNEE_ID AND B.EMPLOY_GROUP = 2
WHERE BUG_DELETED_FLAG =0





SELECT * FROM QA_PROJECT;
SELECT * FROM QA_CRTYPE;
SELECT * FROM QA_RTYPE;
SELECT * FROM QA_EMPLOY;

update qa_project set PROJECT_NAME = '宜家' where project_id =1;
update qa_project set PROJECT_NAME = '香奈儿' where project_id =2;
update qa_project set PROJECT_NAME = '欧莱雅' where project_id =3;
update qa_project set PROJECT_NAME = '联合利华' where project_id =4;
update qa_project set PROJECT_NAME = '博西' where project_id =5;
update qa_project set PROJECT_NAME = '熙地港' where project_id =6;
update qa_project set PROJECT_NAME = '家乐福' where project_id =7;
update qa_project set PROJECT_NAME = '阿里巴巴' where project_id =8;
update qa_project set PROJECT_NAME = '阿里妈妈' where project_id =9;
update qa_project set PROJECT_NAME = '匡威' where project_id =10;
update qa_project set PROJECT_NAME = '蔻驰' where project_id =11;
update qa_project set PROJECT_NAME = '美宝莲' where project_id =12;
update qa_project set PROJECT_NAME = '3ce' where project_id =13;




=========================================
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
--  Table structure for `user`
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '账号',
  `password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '密码',
  `nickname` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT '' COMMENT '昵称',
  `roles` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '身份',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;

-- ----------------------------
--  Records of `user`
-- ----------------------------
BEGIN;
INSERT INTO `user` VALUES ('1', 'anoy', 'pwd', 'anoy', 'ROLE_USER'), ('2', 'admin', 'pwd', 'admin', 'ROLE_USER,ROLE_ADMIN');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;


update user set password = '$2a$10$A4EZrzoXqj4mVyXiw/fsp.mJ.Ne5aVAMWrMK0mAb2zY7lJ/H6Jryi'


============================================

select  * from qa_bugstatus

status_des


select bug_id,project_name,cr_name,bug_cr_num,bug_task_num,object_name,bug_description,bug_rca,bug_solution,b.employ_name developer,a.employ_name tester,qa_creationdt,qa_updatedt,bug_deleted_flag,status_des
from qa_buglist
join qa_project on bug_project_id = project_id
join qa_crtype on cr_id = bug_cr_type_id
join qa_rtype on object_id = qa_type_id
join qa_employ a on a.employ_id = qa_tester_id and a.employ_group = 1
join qa_employ b on b.employ_id= qa_assignee_id and b.employ_group = 2
join qa_bugstatus on status_id =  bug_status_id
where bug_deleted_flag =0 and bug_id = ?



select STATUS_ID,STATUS_DES from qa_bugstatus where STATUS_DELETED_FLAG =0


create table qa_user
select * from  user


select * from qa_user

内容总结

以上是互联网集市为您收集整理的buglist系统中涉及的sql SqlServer版本全部内容，希望文章能够帮你解决buglist系统中涉及的sql SqlServer版本所遇到的程序开发问题。如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容，请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/915987.html

来源：【匿名】

【上一篇】sqlserver 删除表视图函数存储过程【下一篇】一个odbc连mssql分页的类

更多 ►

【buglist系统中涉及的sql SqlServer版本】教程文章相关的互联网学习教程文章

ListalltheDatabasesonaSQLServer_javascript技巧

List all the Databases on a SQL Server. Supported Platforms SQL Server 2000 Yes Script Code strDBServerName = "." Set objSQLServer = CreateObject("SQLDMO.SQLServer") objSQLServer.LoginSecure = True objSQLServer.Connect strDBServerName Set colDatabases = objSQLServer.Databases For Each objDatabase In colDatabases WScript.Echo objDatabase.Name Next

ListtheStoredProceduresinaSQLServerdatabase_图象特效

List the Stored Procedures in a SQL Server database. Supported Platforms SQL Server 2000 Yes Script Code strDBServerName = "." strDBName = "Northwind" Set objSQLServer = CreateObject("SQLDMO.SQLServer") objSQLServer.LoginSecure = True objSQLServer.Connect strDBServerName Set objDB = objSQLServer.Databases(strDBName) Set colStoredProcedures = objDB.StoredProcedures For Each objStoredProcedure In c...

List the Stored Procedures in a SQL Server database

List all the Databases on a SQL Server

How can I list all foreign keys referencing a given table in SQL Server?

1. EXEC sp_fkeys ‘TableName‘ 2. SELECT obj.name AS FK_NAME, sch.name AS [schema_name], tab1.name AS [table], col1.name AS [column], tab2.name AS [referenced_table], col2.name AS [referenced_column]FROM sys.foreign_key_columns fkcINNER JOIN sys.objects obj ON obj.object_id = fkc.constraint_object_idINNER JOIN sys.tables tab1 ON tab1.object_id = fkc.parent_object_idINNER JOIN ...

Sql Server 逻辑文件 '' 不是数据库 '' 的一部分。请使用 RESTORE FILELISTONLY 来列出逻辑文件名。【代码】【图】

逻辑文件 ‘LenborMealOrder_Base_2017‘ 不是数据库 ‘Members_01‘ 的一部分。请使用 RESTORE FILELISTONLY 来列出逻辑文件名。消息 3013，级别 16，状态 1，第 29 行RESTORE DATABASE 正在异常终止。原因：此数据库是用sql语句备份而来，引发的错误，原备份和还原的sql语句如下：--备份 BACKUP DATABASE LeaRunFramework_Base_2016 TO DISK = ‘c:\DB_BACKUP\LenborMealOrder_Base_2017.bak‘--还原 RESTORE DATABASE Membe...

SQL Server 2008 R2 Build List【代码】

This is a list of the builds for SQL Server 2008 R2. This is a separate product than SQL Server 2008. There are other build lists available here. A list of all the builds that I can find and install on my Build VM. If you find a build not listed here, please let me know (sjones @ sqlservercentral.com). All builds are listed in reverse order, so the newest are at the top and the earliest at the bot...

Talend call SqlServer带表结构list参数的存储过程【代码】【图】

dependency><groupId>com.microsoft.sqlserver</groupId><artifactId>mssql-jdbc</artifactId><version>6.1.0.jre8</version> </dependency>2、建立测试的Job，我建立的是一个简单的restful接口调用sql server存储过程的示例，如下：在该示例中： tLibraryLoad：主要是把我们第一步下载的jar加载进来； tRESTRequest：输出body设置为Document； tJavaRow：自己写代码调用带表结构list参数的存储过程，Base Settings code如下...

c# 把List<T>转成DataTable对象，批量导入Sqlserver库

/// <summary>2 /// Sqlbulkcopies the specified SMS.批量插入到数据库3 /// </summary>4 /// <param name="data">list类型数据.</param>5 /// <param name="sqlconn">数据库连接字符串.</param>6 private void SqlbulkcopyPipeLines(List<CPipe> data, SqlConnection sqlconn, string prjId, string modid)7 {8 #region 待处理数据初始化处理9 List<Pr...

SQL Server AlwaysOn 集群关于主Server IP与Listener IP调换的详细测试【图】

168.172.40.113 168.172.40.114 168.172.40.115 168.172.40.117Role:Primary Availability Mode:Asynchronous CommitRole:Secondary Availability Mode:Asynchronous CommitRole:Secondary Availability Mode:Asynchronous CommitSSMS 连接登入各IP显示如下：4. 测试步骤（第一部分更新主节点的server IP为临时IP，即将40.112-->40.118） Step 1 通过控制面板更新主Server IPStep 2 更新前为40.112Step 3 更新后为40.118Step 4 ...

SQLServer访问SharepointLists数据方法详解

接触Sharepoint以来，如何访问Sharepoint Lists数据是我一直头疼的事情，直到找到如下的方法。暂且称此方法是EnesysRSDataExtension。这种方法基于Web Service，创建报表时数据源是http://servername(例如：http://lingyanzhu:800)，数据查询是XML语句。以下接触Sharepoint以来，如何访问Sharepoint Lists数据是我一直头疼的事情，直到找到如下的方法。暂且称此方法是EnesysRSDataExtension。这种方法基于Web Service，创建报表时数...

mybatis-plus解决 sqlserver批量插入list报错(转)【代码】

原文地址： mybatis-plus解决 sqlserver批量插入list报错注：我本地的mybits-plus版本为3.1.0 错误1：org.apache.ibatis.exceptions.PersistenceException: ### Error flushing statements. Cause: org.apache.ibatis.executor.ExecutorException: Error getting generated key or setting result to parameter object. Cause: com.microsoft.sqlserver.jdbc.SQLServerException: The statement must be executed before any re...