从2个表中选择列中不匹配的行 [英] Select the rows from 2 tables that don't match in a column

问题描述

  SELECT  userid，company，car_moto，packet，SUM（clear_protect） FROM 公司 EXCEPT   SELECT  userid，company，car_moto，packet，paid  FROM 订单其中​​ userid = '  $ userid' 

我想选择具有相同列值的2个表中的所有行userid，company，car_moto，packet但

 SUM（clear_protect）

和

 pay 

不相等。我认为

 EXPECT 

可行吗？任何建议？

解决方案

userid'

我想选择2个表中具有相同值userid，company，car_moto，packet但<的所有行pre lang =SQL> SUM（clear_protect）

和

付费

不相等。我认为

 EXPECT 

会起作用......有什么建议吗？

试试：

;   cte1  as （
  SELECT  userid，company，car_moto，packet，SUM（clear_protect） AS  SumCP 
  FROM 公司
  GROUP   BY  userid，company，car_moto，packet 
 ）
  SELECT  userid，company，car_moto，packet 
  FROM  cte1 
  INNER   JOIN 订单 ON  cte1.userid = orders.userid 
  AND  cte1.company = orders.company 
  AND  cte1 .car_moto = orde rs.car_moto 
  AND  cte1.packet = orders.packet 
  WHERE  cte1。 SumCP<> orders.paid 

; 
  WITH  CTE  AS （
  SELECT  id2，company，car_moto，packet，SUM（付费） AS  SumCP 
  FROM  orders 
  GROUP   BY  id2，company，car_moto，packet，paid 
 ）
  SELECT  CTE.id2  AS  Id，CTE.company，CTE.car_moto，CTE。数据包，CTE.SumCP 
  FROM  CTE 
  INNER   JOIN 订单
  ON  CTE.id2 = orders.id2 
  AND  CTE.company = orders.company 
  AND  CTE.car_moto = orders.car_moto 
  AND  CTE.packet = orders.packet 
  WHERE  CTE.SumCP =（ SELECT  MIN（SumCP） FROM  CTE）
  GROUP   BY  CTE.id2，CTE.company，CTE.car_moto，CTE.packet，CTE.SumCP 

SELECT userid, company, car_moto, packet, SUM(clear_protect) FROM companies EXCEPT SELECT userid, company, car_moto, packet, paid FROM orders where userid='$userid'

I want to select all the rows of the 2 tables that have the same values of the columns userid, company, car_moto, packet but

SUM(clear_protect)

and

paid

not equal. I thought

EXPECT

would work ... Any suggestion?

解决方案

userid'

I want to select all the rows of the 2 tables that have the same values of the columns userid, company, car_moto, packet but

SUM(clear_protect)

and

paid

not equal. I thought

EXPECT

would work ... Any suggestion?

Try:

;with cte1 as (
SELECT userid, company, car_moto, packet, SUM(clear_protect) AS SumCP
FROM companies 
GROUP BY userid, company, car_moto, packet
)
SELECT userid, company, car_moto, packet 
FROM cte1 
INNER JOIN orders ON cte1.userid = orders.userid
                    AND cte1.company = orders.company
                    AND cte1.car_moto = orders.car_moto
                    AND cte1.packet = orders.packet
WHERE cte1.SumCP <> orders.paid

;
WITH CTE AS (
SELECT id2,company,car_moto,packet,SUM(paid) AS SumCP
FROM orders
GROUP BY id2,company,car_moto,packet ,paid
)
SELECT CTE.id2 AS Id,CTE.company,CTE.car_moto,CTE.packet,CTE.SumCP
FROM CTE
INNER JOIN orders
ON CTE.id2       = orders.id2
AND CTE.company  = orders.company
AND CTE.car_moto = orders.car_moto
AND CTE.packet   = orders.packet
WHERE CTE.SumCP  = (SELECT  MIN(SumCP) FROM CTE)
GROUP BY CTE.id2,CTE.company,CTE.car_moto,CTE.packet,CTE.SumCP

这篇关于从2个表中选择列中不匹配的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！