我需要一些sql查询的帮助 [英] i need an assistance on some sql query

问题描述

hi

我正在处理两个表，就像table1和table2

在两个表中，pid是ex
的常见用法
table1 ==>
pid，pcode
1000，jk1
1001，jk2
1002，jk3
1003，jk4
1004，jk5
1005，jk6
table2 ==>
pid，状态
1001，已完成
1005，已完成

输出==>
pid，pname
1000，jk1
1002，jk3
1003，jk4
1004，jk5


< pre>注意:在这里为了可视化，我已经用comms//pre>
分隔了两列
因为前两个是表，所以我应该从table1获取数据，但是我们应该考虑table2数据；
我需要输出表作为上面
中指定的输出表 这意味着table2包含pid不应出现在输出表中

我在想，但我无法写这个东西，所以请

有人可以告诉我有关此操作的查询吗?
谢谢Advanced

hi

i am dealing with two table just like table1 and table2

in two table pid is the Common thing for ex

table1==>
pid , pcode
1000 , jk1
1001 , jk2
1002 , jk3
1003 , jk4
1004 , jk5
1005 , jk6
table2==>
pid , status
1001 , completed
1005 , completed

output==>
pid , pname
1000 , jk1
1002 , jk3
1003 , jk4
1004 , jk5


<pre>note: here for visualization i had saparated the two columns by comms</pre>

for ex these two are the tables so that i should get the data from the table1 but we should consider the table2 data;
i need the output table as output table which was specified in above
it mean that table2 contains the pid should not come in the output table

i am thinking but iam not able to write this thing so please

can any one tell me the query for this Operation
thanks in advanced

推荐答案

您可以尝试

You can try

SELECT *
FROM table1 AS T1
LEFT JOIN table2 AS T2 ON T2.pid = T1.pid
WHERE T2.pid IS NULL

或

or

SELECT *
FROM table1 AS T1
WHERE NOT EXISTS (SELECT * FROM table2 AS T2 WHERE T2.pid = T1.pid)

当table2仅包含有关您希望您不在结果中的记录的信息时，此方法有效.

请参阅加入基础知识 [存在(Transact-SQL) [

This works when table2 only contains information about records you want you leave out of the result.

See Join Fundamentals[^] to read about JOIN and EXISTS (Transact-SQL)[^] for the EXISTS command.


[edit]Changed "FROM table AS t1" to "FROM table1 AS t1" - OriginalGriff[/edit]

select table1.pid,table1.pcode from table1 where pid not in (select pid from table2)

我想它可以为您提供帮助.

I think it can help you.

示例1 -从状态完成的table2中获取所有pid:

Example 1 - get all pid''s from table2 where status is completed:

SELECT t1.[pid], t1.[pcode] AS [pname]
FROM table2 AS t2 LEFT JOIN table1 AS t1 ON t1.[pid] = t2.[pid]
WHERE t2.[status] = 'completed'

示例2 -无论状态如何，都从table1获取所有pid:

Example 2 - get all pid''s from table1 no matter of status:

SELECT t1.[pid], t1.[pcode] AS [pname], t2.[status]
FROM table1 AS t1 LEFT JOIN table2 AS t2 ON t1.[pid] = t2.[pid]

这篇关于我需要一些sql查询的帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！