sql语句中'173' AS TESTITEM_CODE的意思
时间: 2024-01-03 22:04:59 浏览: 30
在 SQL 语句中,'173' AS TESTITEM_CODE 是一种给字段取别名的方式。这个语句的意思是将字段名或字段表达式 '173' 重命名为 TESTITEM_CODE,以便在查询结果中更方便地识别这个字段。在查询结果中,TESTITEM_CODE 列的值将会是 '173',但是在 SELECT 子句中,可以使用 TESTITEM_CODE 来引用这个字段。这种方式可以使查询结果更加易读并且可以减少歧义。
相关问题
SELECT v.Version_No,CONCAT( s.Soft_Name , '-',vtl.VTL_TechStrategy ) AS Test_level_name,a.TestItem,a.testType,a.use_cases,b.instance_data, CONCAT( a.use_cases , '/',b.instance_data) AS cases_instances FROM t_softlist AS s INNER JOIN t_testversion AS v ON v.TreeUid = s.TreeUid INNER JOIN t_versiontotestlevel AS vtl ON vtl.Version_Uid = v.Version_Uid LEFT JOIN view_soft_testreqcanbuildusecase AS qcan ON qcan.VTL_Uid = vtl.VTL_Uid LEFT JOIN ( SELECT qcan.TestItem,qcan.testType,COUNT(us.TreeUid) AS use_cases FROM view_soft_testreqcanbuildusecase AS qcan INNER JOIN t_usecase AS us ON us.TreeUid = qcan.ReqTreeUid GROUP BY qcan.TestType )AS a ON a.TestItem = qcan.TestItem LEFT JOIN ( SELECT qcan.TestItem,qcan.testType,COUNT(uci.UC_Uid) AS instance_data FROM view_soft_testreqcanbuildusecase AS qcan INNER JOIN t_usecase AS us ON us.TreeUid = qcan.ReqTreeUid INNER JOIN t_ucinstances AS uci ON uci.UC_Uid = us.UC_Uid GROUP BY qcan.TestType )AS b ON b.testType = a.testType GROUP BY Test_level_name,a.TestType;这是查询当前项目的语句,要查看所有库下的这些信息怎么改
如果要查询所有库下的这些信息,你需要在查询语句中添加库的信息。假设你要查询的库名为`database_name`,你可以将查询语句修改为以下形式:
```
SELECT v.Version_No, CONCAT(s.Soft_Name, '-', vtl.VTL_TechStrategy) AS Test_level_name, a.TestItem, a.testType, a.use_cases, b.instance_data,
CONCAT(a.use_cases, '/', b.instance_data) AS cases_instances
FROM database_name.t_softlist AS s
INNER JOIN database_name.t_testversion AS v ON v.TreeUid = s.TreeUid
INNER JOIN database_name.t_versiontotestlevel AS vtl ON vtl.Version_Uid = v.Version_Uid
LEFT JOIN database_name.view_soft_testreqcanbuildusecase AS qcan ON qcan.VTL_Uid = vtl.VTL_Uid
LEFT JOIN (
SELECT qcan.TestItem, qcan.testType, COUNT(us.TreeUid) AS use_cases FROM database_name.view_soft_testreqcanbuildusecase AS qcan
INNER JOIN database_name.t_usecase AS us ON us.TreeUid = qcan.ReqTreeUid
GROUP BY qcan.TestType
) AS a ON a.TestItem = qcan.TestItem
LEFT JOIN (
SELECT qcan.TestItem, qcan.testType, COUNT(uci.UC_Uid) AS instance_data FROM database_name.view_soft_testreqcanbuildusecase AS qcan
INNER JOIN database_name.t_usecase AS us ON us.TreeUid = qcan.ReqTreeUid
INNER JOIN database_name.t_ucinstances AS uci ON uci.UC_Uid = us.UC_Uid
GROUP BY qcan.TestType
) AS b ON b.testType = a.testType
GROUP BY Test_level_name, a.TestType;
```
在每个表名之前添加`database_name.`,以指定要查询的库。请确保将`database_name`替换为实际的库名。
SELECT Prj_DBName FROM kiyunstm_bq.t_projects 查询出库名,然后根据库名查询SELECT s.Prj_Uid,v.Version_No,CONCAT( s.Soft_Name , '-',vtl.VTL_TechStrategy ) AS Test_level_name,a.TestItem,a.testType,a.use_cases,b.instance_data, CONCAT( a.use_cases , '/',b.instance_data) AS cases_instances FROM t_softlist AS s INNER JOIN t_testversion AS v ON v.TreeUid = s.TreeUid INNER JOIN t_versiontotestlevel AS vtl ON vtl.Version_Uid = v.Version_Uid LEFT JOIN view_soft_testreqcanbuildusecase AS qcan ON qcan.VTL_Uid = vtl.VTL_Uid LEFT JOIN ( SELECT qcan.TestItem,qcan.testType,COUNT(us.TreeUid) AS use_cases FROM view_soft_testreqcanbuildusecase AS qcan INNER JOIN t_usecase AS us ON us.TreeUid = qcan.ReqTreeUid GROUP BY qcan.TestType )AS a ON a.TestItem = qcan.TestItem LEFT JOIN ( SELECT qcan.TestItem,qcan.testType,COUNT(uci.UC_Uid) AS instance_data FROM view_soft_testreqcanbuildusecase AS qcan INNER JOIN t_usecase AS us ON us.TreeUid = qcan.ReqTreeUid INNER JOIN t_ucinstances AS uci ON uci.UC_Uid = us.UC_Uid GROUP BY qcan.TestType )AS b ON b.testType = a.testType GROUP BY Test_level_name,a.TestType
要根据前一个查询结果中的库名查询相关信息,你可以将第一个查询作为子查询,并在第二个查询中使用这个子查询的结果。以下是修改后的查询语句示例:
```
SELECT s.Prj_Uid, v.Version_No, CONCAT(s.Soft_Name, '-', vtl.VTL_TechStrategy) AS Test_level_name, a.TestItem, a.testType, a.use_cases, b.instance_data,
CONCAT(a.use_cases, '/', b.instance_data) AS cases_instances
FROM t_softlist AS s
INNER JOIN t_testversion AS v ON v.TreeUid = s.TreeUid
INNER JOIN t_versiontotestlevel AS vtl ON vtl.Version_Uid = v.Version_Uid
LEFT JOIN view_soft_testreqcanbuildusecase AS qcan ON qcan.VTL_Uid = vtl.VTL_Uid
LEFT JOIN (
SELECT qcan.TestItem, qcan.testType, COUNT(us.TreeUid) AS use_cases FROM view_soft_testreqcanbuildusecase AS qcan
INNER JOIN t_usecase AS us ON us.TreeUid = qcan.ReqTreeUid
GROUP BY qcan.TestType
) AS a ON a.TestItem = qcan.TestItem
LEFT JOIN (
SELECT qcan.TestItem, qcan.testType, COUNT(uci.UC_Uid) AS instance_data FROM view_soft_testreqcanbuildusecase AS qcan
INNER JOIN t_usecase AS us ON us.TreeUid = qcan.ReqTreeUid
INNER JOIN t_ucinstances AS uci ON uci.UC_Uid = us.UC_Uid
GROUP BY qcan.TestType
) AS b ON b.testType = a.testType
WHERE s.Prj_Uid IN (
SELECT Prj_Uid
FROM kiyunstm_bq.t_projects
)
GROUP BY s.Prj_Uid, Test_level_name, a.TestType;
```
在这个查询语句中,我们添加了一个`WHERE`子句,使用`IN`关键字将第一个查询结果中的库名作为条件进行筛选。请确保将`t_projects`和其他表名替换为实际的表名。
相关推荐
![7z](https://img-home.csdnimg.cn/images/20210720083312.png)
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![doc](https://img-home.csdnimg.cn/images/20210720083327.png)
![zip](https://img-home.csdnimg.cn/images/20210720083736.png)