为什么我的SQL查询会提取其他数据? [英] Why is my SQL query pulling other data?
问题描述
我有一个SQL Query,它将一个数据库表与另一个数据库表进行比较。当我运行查询它给我数据但它也给我数据,我知道数据在数据库中。我想要做的是拿表1并将其与表2进行比较。我想显示表2没有表1所具有的数据。
这是我的查询:
SELECT User_ID,Name FROM Table1
EXCEPT
SELECT User_ID,NAME FROM Table2 where YEAR = 2016
GROUP < span class =code-keyword> BY
User_ID,姓名
我有没有想念什么?
我尝试过的事情:
我试过了切换查询。
从table1中选择*,其中user_id不在(从table2中选择user_id,其中year ='2016')
i不要*认为* EXCEPT适用于不同的表
你可能会遗漏一些事情,它按预期工作。
声明 @ t1 table (
col1 varchar ( 10 )
)
声明 @ t2 table (
col1 varchar ( 10 ),
id int )
insert < span class =code-keyword> into @ t1 (col1) values (< span class =code-string>' a')
insert 进入 @ t1 (col1)值( ' b')
insert into @ t1 (col1) values (' c')
insert < span class =code-keyword> into @ t2 (col1,id) values (' a', 0 )
插入 进入 @ t2 ( col1,id) values (' b', 1 )
插入 进入 @ t2 (col1,id) values (' c', 0 )
选择 col1 来自 @ t1
选择 col1 来自 @ t2 其中 id = 1
group by col1
它返回'a'和'c'
(SELECT User_ID,Name from Table1)
MINUS
(SELECT User_ID,Table2中名称的年份= 2016)
I have a SQL Query that compares one database table to another. When I run the query it gives me the data but it also gives me data that I know that is in the database. What I am trying to do is to take table 1 and compare it to Table 2. I want to display the data that Table 2 doesn't have that Table 1 has.
Here is my Query:
SELECT User_ID, Name FROM Table1
EXCEPT
SELECT User_ID, NAME FROM Table2 where YEAR = 2016
GROUP BY
User_ID, Name
Did I miss something?
What I have tried:
I have tried to switch the query around.
select * from table1 where user_id not in (select user_id from table2 where year='2016')
i don't *think* EXCEPT works over different tables
You might be missing something, it works as expected.
declare @t1 table ( col1 varchar(10) ) declare @t2 table ( col1 varchar(10), id int) insert into @t1 (col1) values ('a') insert into @t1 (col1) values ('b') insert into @t1 (col1) values ('c') insert into @t2 (col1,id) values ('a',0) insert into @t2 (col1,id) values ('b',1) insert into @t2 (col1,id) values ('c',0) select col1 from @t1 except select col1 from @t2 where id = 1 group by col1
it returns 'a' and 'c'
(SELECT User_ID, Name FROM Table1)
MINUS
(SELECT User_ID,Name from Table2 where Year =2016)
这篇关于为什么我的SQL查询会提取其他数据?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!