这是一个复杂的SQL查询吗？ [英] Is it a complex sql query?
问题描述
你好
我正在使用sql 2005的存储过程（使用Visual Studio 2005）
我有两个表.. TABLE1和表2
Hello
I am using stored procedure with sql 2005 (with Visual studio 2005)
I have two tables .. TABLE1 And TABLE2
从TABLE1我需要检索4个最顶行的OrderID'。所以
From TABLE1 i need to retrive the OrderID''s of the 4 most top rows. so
i做了：
SELECT TOP 4订单ID FROM TABLE1订单按订单ID desc
现在我正在尝试要做的是取4行结果（4 OrderID'）i
来自
TABLE1并检查是否存在4行（4 OrderID'） TABLE2 for a
具体
用户ID由INPUT varible（@UserId）获得..
我想要返回的是只有哪个OrderID''存在于TABLE2中才能获得
特定用户。
如果只有2个OrderID''我从TABLE1中重新存在在TABLE2中我将
只返回2个OrderID'（所以我可以在visual studio 2005中输出我的输出
使用阅读器（））
如果有人知道怎么做这个sql查询，我会很感激，
有可能在1个查询中执行此操作吗？我想把它放在一个存储的
程序中。
i did:
SELECT TOP 4 OrderID FROM TABLE1 order by OrderID desc
Now what i am trying to do is take the 4 row results (4 OrderID''s) i
got from
TABLE1 and check if the 4 rows (4 OrderID''s) exist in TABLE2 for a
specific
userID i get by INPUT varible (@UserId)..
What i want to return is only which OrderID''S existed in TABLE2 for the
specific user.
If only 2 OrderID''S i retrived from TABLE1 exist in TABLE2 i will
return only 2 OrderID''s (so i can do my output in visual studio 2005
using the reader())
I would appreciate this if anyone knows how to do this sql query , is
it possible to do this in 1 query? i want to put it in a stored
procedure.
推荐答案
我试图使用这个查询 -
SELECT TOP 4 OrderID FROM TABLE1 WHERE存在（SELECT *来自TABLE2
其中@ UserId = TABLE2.UserID）
但是这个查询显示了所有4个OrderID'，如果它在
中找到USERID。表2 ..
我想要返回的只是哪个OrderID'在TABLE2中为
特定用户提供。
如果我在TABLE1中：
OrderID
1
2
3
4
表2：
OrderID UserId
1 1001
2 1002
我希望它只返回2如果@UserID的INPUT参数是1002
I tried to use this query-
SELECT TOP 4 OrderID FROM TABLE1 WHERE exists (SELECT * From TABLE2
where @UserId=TABLE2.UserID)
But this query shows me the all 4 OrderID''s if it finds the USERID in
TABLE2..
What i want to return is only which OrderID''S existes in TABLE2 for the
specific user.
if i have in TABLE1:
OrderID
1
2
3
4
TABLE2:
OrderID UserId
1 1001
2 1002
I want it to return only "2" if the INPUT Parameter of @UserID is 1002
另一个例子..
如果我在TABLE1中：
订购ID
1
2
3
4
TABLE2：
OrderID UserId
1 1001
2 1002
3 1002
我希望它只返回2和3。如果@UserID的INPUT参数
是1002
Another example..
if i have in TABLE1:
OrderID
1
2
3
4
TABLE2:
OrderID UserId
1 1001
2 1002
3 1002
I want it to return only "2" and "3" if the INPUT Parameter of @UserID
is 1002
嗨
检查 http://www.aspfaq.com/etiquette.asp?id = 5006 关于如何以可用的形式发布DDL和
样本数据
你可以使用类似的东西：
SELECT TOP 4 t1.OrderID
FROM TABLE1 t1
WHERE存在（SELECT * FROM TABLE2 t2
WHERE @ UserId = T2.UserId
AND T1.OrderId = T2.OrderId）
或（更好！）
SELECT TOP 4 t1 .OrderID
FROM TABLE1 t1
JOIN TABLE2 t2 ON T1.OrderId = T2.OrderId AND @ UserId = T2.UserId
查看主题使用联接和加入基础知识在线书籍
John
< st ********** @ gmail.com>在消息中写道
news：11 ********************** @ g47g2000cwa.googlegr oups.com ...
Hi
Check out http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and
sample data in a usable form
You can use something like:
SELECT TOP 4 t1.OrderID
FROM TABLE1 t1
WHERE exists (SELECT * FROM TABLE2 t2
WHERE @UserId=T2.UserId
AND T1.OrderId = T2.OrderId )
Or (better!)
SELECT TOP 4 t1.OrderID
FROM TABLE1 t1
JOIN TABLE2 t2 ON T1.OrderId = T2.OrderId AND @UserId=T2.UserId
Check out the topics "Using Joins" and "Join Fundamentals" in books online
John
<st**********@gmail.com> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.com...
另一个例子..
如果我在TABLE1中：
OrderID
1
2
3
4 >
TABLE2：
OrderID UserId
1 1001
2 1002
3 1002
我希望它只返回2和3。如果@UserID的INPUT参数
是1002
Another example..
if i have in TABLE1:
OrderID
1
2
3
4
TABLE2:
OrderID UserId
1 1001
2 1002
3 1002
I want it to return only "2" and "3" if the INPUT Parameter of @UserID
is 1002
这篇关于这是一个复杂的SQL查询吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
