如何比较SQL Server中的两行

时间:2022-03-03 12:32:34

I'm used to mysql when you can do that with no problems. I would like to run the following statement in SQL Server however it doesn't see the column C_COUNT.

我已经习惯了mysql,你可以毫无问题地做到这一点。我想在SQL Server中运行以下语句,但它没有看到列C_COUNT。

SELECT 
   A.customers AS CUSTOMERS, 
  (SELECT COUNT(ID) FROM Partners_customers B WHERE A.ID = B.PIID) AS C_COUNT 
FROM Partners A
WHERE CUSTOMERS <> [C_COUNT]

Is it possible to utilize any mathematical operations in the SELECT area like

是否可以在SELECT区域中使用任何数学运算

SELECT (CUSTOMERS - C_COUNT) AS DIFFERENCE 

3 个解决方案

#1


5  

SQL Server does not allow you to use aliases in the WHERE clause. You'll have to have something like this:

SQL Server不允许您在WHERE子句中使用别名。你必须得到这样的东西:

SELECT *, Customers - C_COUNT "Difference"
FROM  (
    SELECT 
    A.customers AS CUSTOMERS, 
    (SELECT COUNT(ID) 
    FROM Partners_customers B WHERE A.ID = B.PIID) 
    AS C_COUNT FROM Partners A
) t
WHERE CUSTOMERS <> [C_COUNT]

Or, better yet, eliminating an inline count:

或者,更好的是,消除内联计数:

select A.customers, count(b.id)
FROM Partners A
LEFT JOIN Partners_customers B ON A.ID = B.PIID
Group By A.ID
having a.customers <> count(b.id)

#2


0  

WITH A AS
(
    SELECT 
       A.customers AS CUSTOMERS, 
      (SELECT COUNT(ID) FROM Partners_customers B WHERE A.ID = B.PIID) AS C_COUNT 
    FROM Partners A
    WHERE CUSTOMERS <> [C_COUNT]
)
SELECT 
    *, 
    (CUSTOMERS - C_COUNT) AS DIFFERENCE 
FROM A 

#3


0  

Completely untested....

完全未经测试....

(select * from TabA 
 minus
 select * from TabB) -- Rows in TabA not in TabB
union all
( 
 select * from TabB 
 minus
 select * from TabA
) -- rows in TabB not in TabA

#1


5  

SQL Server does not allow you to use aliases in the WHERE clause. You'll have to have something like this:

SQL Server不允许您在WHERE子句中使用别名。你必须得到这样的东西:

SELECT *, Customers - C_COUNT "Difference"
FROM  (
    SELECT 
    A.customers AS CUSTOMERS, 
    (SELECT COUNT(ID) 
    FROM Partners_customers B WHERE A.ID = B.PIID) 
    AS C_COUNT FROM Partners A
) t
WHERE CUSTOMERS <> [C_COUNT]

Or, better yet, eliminating an inline count:

或者,更好的是,消除内联计数:

select A.customers, count(b.id)
FROM Partners A
LEFT JOIN Partners_customers B ON A.ID = B.PIID
Group By A.ID
having a.customers <> count(b.id)

#2


0  

WITH A AS
(
    SELECT 
       A.customers AS CUSTOMERS, 
      (SELECT COUNT(ID) FROM Partners_customers B WHERE A.ID = B.PIID) AS C_COUNT 
    FROM Partners A
    WHERE CUSTOMERS <> [C_COUNT]
)
SELECT 
    *, 
    (CUSTOMERS - C_COUNT) AS DIFFERENCE 
FROM A 

#3


0  

Completely untested....

完全未经测试....

(select * from TabA 
 minus
 select * from TabB) -- Rows in TabA not in TabB
union all
( 
 select * from TabB 
 minus
 select * from TabA
) -- rows in TabB not in TabA