SQL Server 子查询错误:No column name was specified for column 2 of 'a' error (转载)
问:
I have a MySQL query and I ran it working fine but same query showing error in SQL Server.
SQL Server query:
SELECT COUNT(*) cnt FROM (SELECT DISTINCT tc_id, MAX(exn_time), STATUS FROM release_details a, tc_details b WHERE a.project = b.project AND a.tc_id = b.tc_name AND logicaldel = 0 AND a.project = 'test' GROUP BY tc_id, STATUS) a WHERE a.status = 'PASS';
Error:
No column name was specified for column 2 of 'a'.
How do I modify the above query?
答:
Use the Alias name for your inner query.You are getting the MAX(exn_time) but not specified the name for that column that's why throwing the error. And you can use the Joins to the tables to make it more readable.
SELECT COUNT(*) cnt FROM ( SELECT DISTINCT tc_id, MAX(exn_time) AS Maxtime , STATUS FROM release_details a JOIN tc_details b ON a.project= b.project AND a.tc_id = b.tc_name WHERE logicaldel = 0 AND a.project ='test' GROUP BY tc_id, STATUS ) a WHERE a.status='PASS';
所以记住SQL Server的子查询,每一列都要起个列名,否者会报错!