一、Select distinct [Email] from chinadat.chinadat.Members
Where [Email] not in (select [Email] from Email.dbo.Emails) and [Email] <> ''二、Select distinct [Email],[type] from chinadat.chinadat.Members
Where [Email] not in (select [Email] from Email.dbo.Emails) and [Email] <> ''解释一下,上面的SQL语句牵扯到2张个数据库,chinadat数据库的Members表和Email数据库的Emails表,这两条SQL语句唯一的区别就是第2条语句多查询了一个type字段,但是查询出来的结果数是不一样的,怎么修改第2条语句使其能和第一条语句有同样的效果?
Where [Email] not in (select [Email] from Email.dbo.Emails) and [Email] <> ''二、Select distinct [Email],[type] from chinadat.chinadat.Members
Where [Email] not in (select [Email] from Email.dbo.Emails) and [Email] <> ''解释一下,上面的SQL语句牵扯到2张个数据库,chinadat数据库的Members表和Email数据库的Emails表,这两条SQL语句唯一的区别就是第2条语句多查询了一个type字段,但是查询出来的结果数是不一样的,怎么修改第2条语句使其能和第一条语句有同样的效果?
Select [Email],[type] from chinadat.chinadat.Members t where not exists(select 1 from chinadat.chinadat.Members where
[Email]=t.[Email] and [type]<t.[type] )
and [Email] not in (select [Email] from Email.dbo.Emails) and [Email] <> ''
而只有一个字段的情况自然比这个要少.
Where [Email] not in (select [Email] from Email.dbo.Emails) and [Email] <> ''
group by [Email]
Where [Email] not in (select [Email] from Email.dbo.Emails) and [Email] <> '' Select [Email],max([type]) type from chinadat.chinadat.Members
Where [Email] not in (select [Email] from Email.dbo.Emails) and [Email] <> ''
group by [email]-- or
Select [Email],min([type]) type from chinadat.chinadat.Members
Where [Email] not in (select [Email] from Email.dbo.Emails) and [Email] <> ''
group by [email]
[type]--查詢結查,同一個[Email]存在不同[type]時,會顯示多條查詢優化有可能會造成用到聚集索引
Where [Email] not in (select [Email] from Email.dbo.Emails) and [Email] <> ''
group by [Email]