sql查询以从两个表中获取记录 [英] sql query to fetch the records from a two tables

问题描述

表A

Id    pin    status   etc
1   11  FAILED  
2   22      
3   44      
4   55  FAILED  

表B

id   PIN      msg    counter
1    11      xyz         1
4    55      wsc         10

表数据:我有2个表，表A(状态，id，列为列)，表B(计数器，id为列)

Table data: I have 2 tables table A(status,id,pin as columns),table B(counter,id as columns)

我需要一个sql查询来选择A.status = failed以及b.counter< 10

I need a sql query to select the records where A.status =failed and also b.counter <10

最终结果中，我需要所有具有a.status=failed和B.counter <10的记录，以及需要在表B中不存在的新记录.

In final result I need all those records with a.status=failed and B.counter <10 and also fresh records which wont be present in table B.

但是新记录不会出现在表B中，因此不满足b.count <10.

But fresh records wont be present in table B so the b.counter <10 is not satisfied.

如何处理这种情况?

推荐答案

如果我正确地理解了这一点，则您想在PIN列上连接表A和B，并且希望记录A.status = failed和B.计数器< 10.但是您还希望表A中的记录在表B中没有匹配的PIN吗?

If I'm interpreting this correctly, you want to join tables A and B on the PIN column and you want records with A.status = failed and B.counter < 10. But you also want records from table A that have no matching PIN in table B?

这可能不是最有效的查询，但是如果我正确解释了您的要求，它将可以完成工作.

This may not be the most efficient query but it will get the job done, if I interpreted your requirements correctly.

SELECT *
  FROM A
 WHERE A.pin = B.pin
   AND A.status = 'FAILED'
   AND B.counter < 10
 UNION
SELECT *
  FROM A
 WHERE A.status = 'FAILED'
   AND A.pin NOT IN (SELECT pin FROM B);

这篇关于sql查询以从两个表中获取记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！