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',' 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查询表中是否存在数据。
发布评论