mysql - 如何使用 'AS'语法？

Question

我需要不同表中的不同数据，我正在尝试使用 AS我尝试了所有代码，它们都可以工作，但是一一，我不能一起工作我尝试了两种方法，但它不起作用我认为是圆形语法我研究但我是 SQL 新手

我的第一种方法:

            string cmd = @" SELECT COUNT(Ref) AS data,"; 
        cmd += @" (select count(CustomerID) as data2 from Contract where DATEDIFF(DAY,StartDate,GETDATE()) between 0 and 30),";
        cmd += @" (select count(distinct(CustomerID)) as data3 from Contract where FinishDate > GETDATE()),";
        cmd += @" (select count(Ref) as data4 from Support where DATEDIFF(DAY,StartDate,GETDATE()) between 0 and 30)";
        cmd += @" FROM Customer WHERE (Deleted = 0 or Deleted is null) ";

我的第二种方式:

    string cmd = @" SELECT COUNT(Ref) ,"; 
        cmd += @" (select count(CustomerID) from Contract where DATEDIFF(DAY,StartDate,GETDATE()) between 0 and 30) as data2 ,";
        cmd += @" (select count(distinct(CustomerID))  from Contract where FinishDate > GETDATE()) as data3,";
        cmd += @" (select count(Ref)  from Support where DATEDIFF(DAY,StartDate,GETDATE()) between 0 and 30) as data4";
        cmd += @" FROM Customer WHERE (Deleted = 0 or Deleted is null) AS data ";

Answer 1

别名必须在查询级别分配，而不是在子查询(第一个变体)中分配。

WHERE 条件不能有别名(第二个变体)。

SELECT COUNT(Ref) AS data, 
       ( select count(CustomerID)
         from Contract 
         where DATEDIFF(DAY,StartDate,GETDATE()) between 0 and 30) as data2 , 
       ( select count(distinct(CustomerID))
         from Contract 
         where FinishDate > GETDATE()) as data3, 
       ( select count(Ref) 
         from Support 
         where DATEDIFF(DAY,StartDate,GETDATE()) between 0 and 30)  as data4
FROM Customer 
WHERE (Deleted = 0 or Deleted is null)