SQL查询表中是否存在数据。 [英] SQL Query if data exist in table or not.
问题描述
Hii all,
首先是一个场景。
我的数据库中有1000+用户。我想根据表格中的数据的存在来过滤用户(在这种情况下是帐户),即我想省略表格中没有条目的用户(表中没有用户记录)。
例如:
用户表:
用户ID ------ ---用户名
User001 -------- ABC
User002 -------- XYZ
User003 -------- PQR
....................
...................
账户表:
账户名称---- --- CreateUser ------- ModifyUser
Account1 ----------- User001 --------------- User002
Account2 ----------- User002 --------------- User001
如果达到以上我需要以下UserID:User001和User002
和 不 User003。
我如何在账户表上查询... !!
将使用哪个概念..... Plzz帮我解决这个问题......!
连接应该适用于这种情况:
- 测试数据设置
声明 @ user table (
UserId varchar ( 50 ),
用户名 varchar ( 50 )
);
声明 @ account table (
AccountName varchar ( 50 ),
CreateUser varchar ( 50 ),
ModifyUser varchar ( 50 )
);
插入 进入 @ user 值(' User001',' ABC');
插入 进入 @ user 值(' User002',' XYZ');
插入 进入 @ user 值(' User003',' PRQ');
插入 进入 @ account 值(' Account1',' User001',' < span class =code-string> User002');
插入 进入 @ account 值(' Account2',' User002',' 用户001' 跨度>);
- 加入查询
选择 distinct
u。*
来自 @ user u
inner join @ account a
on u.UserID = a.CreateUser 或 u.UserID = a.ModifyUser
;
你可以这样做,假设你需要检查CreateUser和ModifyUser:
选择 从 [用户] u 其中 存在(选择 * 来自帐户a其中 u.CreateUser = a.UserID 或 u.ModifyUser = a.UserID)
Hii all,
Here is a Scenario first.
I have 1000+ User in my database. I want to filter the Users depending upon the existence of data in tables(Account in this case) i.e I would like to omit the Users who does not have entry in the table (NO RECORDS OF USER IN TABLE).
for example:
User Table:
UserID---------Username
User001--------ABC
User002--------XYZ
User003--------PQR
...................
...................
Account Table:
AccountName-------CreateUser-------ModifyUser
Account1-----------User001---------------User002
Account2-----------User002---------------User001
Acc to above I would require following UserID : User001 and User002
and NOT User003.
How I can query this on Account Table...!!
Which concept will be Used.....Plzz help me on this...!
A join should work for this scenario:
--test data setup declare @user table( UserId varchar(50), Username varchar(50) ); declare @account table( AccountName varchar(50), CreateUser varchar(50), ModifyUser varchar(50) ); insert into @user values('User001','ABC'); insert into @user values('User002','XYZ'); insert into @user values('User003','PRQ'); insert into @account values('Account1','User001','User002'); insert into @account values('Account2','User002','User001'); --join query select distinct u.* from @user u inner join @account a on u.UserID = a.CreateUser or u.UserID = a.ModifyUser ;
You can do it like this, supposing that you need to check both CreateUser and ModifyUser:
select from [user] u where exists(select * from account a where u.CreateUser = a.UserID or u.ModifyUser = a.UserID)
这篇关于SQL查询表中是否存在数据。的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!