解决方案 »
- SQL 优化工具
- 求一sql 语句,查询并修改后插入(在线急等)
- 请教两问题,select into 和 group by
- SQL2005怎样可以通过增加服务器的形式提高负荷能力?
- 简单问题
- 告急,求一存储过程的写法~ (从一个表统计数据得到一行,插入到另一个表),我不知道写:(,我举了例,请进.
- SQL Server 查询树结构的表,查询一个节点的所有子节点
- SQL Server Management Studio安装失败
- 今天灵感来了,有一个贴,自认为答得妙。 给出链接大家看看。
- 各位高手快来救急:这条查询语句是否非得用临时表啊?
- 怎样查询出MS SQL表中某一字段上否有下列特性(高手请进)
- sql数据对比大问题(急!!!!)
select * from ppp
select * from #t
--都是可以正常執行得
select * from #t B, ppp a WHERE b.書名=a.h_name
select h_name from ppp a,#t b where a.h_name=b.書名
在調用時需要用分佈式事務
--加上這個選項
EXEC master.dbo.sp_serveroption @server=N'連接服務器名', @optname=N'rpc out', @optvalue=N'true'
一个行, 一个不行, 有点奇怪
|--Merge Join(Right Anti Semi Join, MANY-TO-MANY MERGE:([Expr1009])=([Expr1008]), RESIDUAL:([Expr1008]=CONVERT(varchar(100),[SQLNCLI].[h_name],0)))
|--Remote Query(SOURCE:(10.0.0.9), QUERY:(SELECT "Col1020","Expr1009" FROM (SELECT "Tbl1006"."h_name" "Col1020","Tbl1006"."h_isbn" "Col1019","Tbl1006"."h_price" "Col1021",CONVERT(varchar(100),"Tbl1006"."h_name",0) "Expr1009" FROM "jxbookall"."dbo"."join_product_ty" "Tbl1006") Qry1022 ORDER BY "Expr1009" ASC))
|--Sort(ORDER BY:([Expr1008] ASC))
|--Nested Loops(Left Anti Semi Join, WHERE:([Expr1008] IS NULL))
|--Compute Scalar(DEFINE:([Expr1008]=CONVERT(varchar(100),[tempdb].[dbo].[##t].[書名],0)))
| |--Table Scan(OBJECT:([tempdb].[dbo].[##t]))
|--Row Count Spool
|--Remote Query(SOURCE:(10.0.0.9), QUERY:(SELECT TOP 1 1 FROM "jxbookall"."dbo"."join_product_ty" "Tbl1006"))
----------------Join執行計劃
|--Merge Join(Right Anti Semi Join, MANY-TO-MANY MERGE:([Expr1008])=([tempdb].[dbo].[##t].[書名]), RESIDUAL:([tempdb].[dbo].[##t].[書名]=CONVERT_IMPLICIT(nvarchar(100),[SQLNCLI].[h_name],0)))
|--Remote Query(SOURCE:(10.0.0.9), QUERY:(SELECT "Col1019","Expr1008" FROM (SELECT "Tbl1006"."h_name" "Col1019","Tbl1006"."h_isbn" "Col1018","Tbl1006"."h_price" "Col1020",CONVERT(nvarchar(100),"Tbl1006"."h_name",0) "Expr1008" FROM "jxbookall"."dbo"."join_product_ty" "Tbl1006") Qry1021 ORDER BY "Expr1008" ASC))
|--Sort(ORDER BY:([tempdb].[dbo].[##t].[書名] ASC))
|--Nested Loops(Left Anti Semi Join, WHERE:([tempdb].[dbo].[##t].[書名] IS NULL))
|--Table Scan(OBJECT:([tempdb].[dbo].[##t]))
|--Row Count Spool
|--Remote Query(SOURCE:(10.0.0.9), QUERY:(SELECT TOP 1 1
http://topic.csdn.net/u/20080628/13/f56c94ac-5767-43fe-bcde-d1398bd6dcd2.html
|--Merge Join(Right Anti Semi Join, MANY-TO-MANY MERGE:([Expr1009])=([Expr1008]), RESIDUAL:([Expr1008]=CONVERT(varchar(100),[SQLNCLI].[h_name],0)))
|--Remote Query(SOURCE:(10.0.0.9), QUERY:(SELECT "Col1020","Expr1009" FROM (SELECT "Tbl1006"."h_name" "Col1020","Tbl1006"."h_isbn" "Col1019","Tbl1006"."h_price" "Col1021",CONVERT(varchar(100),"Tbl1006"."h_name",0) "Expr1009" FROM "jxbookall"."dbo"."join_product_ty" "Tbl1006") Qry1022 ORDER BY "Expr1009" ASC))
|--Sort(ORDER BY:([Expr1008] ASC))
|--Nested Loops(Left Anti Semi Join, WHERE:([Expr1008] IS NULL))
|--Compute Scalar(DEFINE:([Expr1008]=CONVERT(varchar(100),[tempdb].[dbo].[##t].[書名],0)))
| |--Table Scan(OBJECT:([tempdb].[dbo].[##t]))
|--Row Count Spool
|--Remote Query(SOURCE:(10.0.0.9), QUERY:(SELECT TOP 1 1 FROM "jxbookall"."dbo"."join_product_ty" "Tbl1006")) -------------------in可以執行 |--Merge Join(Right Semi Join, MANY-TO-MANY MERGE:([Expr1009])=([Expr1008]), RESIDUAL:([Expr1008]=CONVERT(varchar(100),[SQLNCLI].[h_name],0)))
|--Remote Query(SOURCE:(10.0.0.9), QUERY:(SELECT "Col1016","Expr1009" FROM (SELECT "Tbl1006"."h_name" "Col1016","Tbl1006"."h_isbn" "Col1015","Tbl1006"."h_price" "Col1017",CONVERT(varchar(100),"Tbl1006"."h_name",0) "Expr1009" FROM "jxbookall"."dbo"."join_product_ty" "Tbl1006") Qry1018 ORDER BY "Expr1009" ASC))
|--Sort(ORDER BY:([Expr1008] ASC))
|--Compute Scalar(DEFINE:([Expr1008]=CONVERT(varchar(100),[tempdb].[dbo].[##t].[書名],0)))
|--Table Scan(OBJECT:([tempdb].[dbo].[##t]))
as
select *
from openrowset('sqloledb','10.10.88.***';'sa';'***',master.dbo.sysobjects)
goselect * into #t from sysobjects
select * from #t where name not in(select name from ppp)drop table #t
drop view ppp可以查询到数据阿
Join
IN
都沒問題
就是NOT IN 報錯
呵呵,奇怪了
Join
IN
都沒問題
就是NOT IN 報錯我咋都不抱错
我测试了一下,用in可以查出来。
用not in查没有报错,但是查不出数据来!
实际上应该有数据的,并且数据类型长度完全一致。
包括数字型、字符型都查不出来。
有null的情况下是查不出数据的
select 1 where 1 not in(2,null)
select 1 where 1 not in(2)
Microsoft Analysis Services 客户端工具 2005.090.2047.00
Microsoft 数据访问组件 (MDAC) 2000.080.0194.00
Microsoft MSXML 2.6 3.0 4.0 6.0
Microsoft Internet Explorer 6.0.2800.1106
Microsoft .NET Framework 2.0.50727.1433
操作系统 5.0.2195