关于JOIN语法的问题 [英] Question about JOIN syntax
问题描述
使用antoher查询结果连接表的语法是什么。
例如我有两张表
创建表客户（
CustomerID int，
LastPaymentDate日期时间）
创建表付款（
PaymentID int，
CustomerID int，
PaymentDate日期时间）
什么查询将带给我的最后一笔付款日期在
的客户客户表不正确。
只能通过将其与付款表中每个
客户的最大付款日期进行比较来检查。
我希望这是由ansi标准sql完成的。没有使用任何特定的功能
的sql server。
thx
what is the syntax to join a table with the result of antoher query.
For example i have two tables
Create Table Customers (
CustomerID int,
LastPaymentDate Datetime )
Create Table Payments (
PaymentID int,
CustomerID int,
PaymentDate Datetime )
What query will bring me the customers whose lastpaymentdate in the
customers table is not correct.
That can only be checked by comparing it with the max paymentdate for each
customer in the payments table.
I want this to be done by ansi standard sql. Not using any specific feature
of sql server.
thx
推荐答案
SELECT *
来自客户AS C
WHERE lastpaymentdate<>
（SELECT MAX（付款日期）
来自付款AS P
在哪里P.customerid = C.customerid）
-
David Portas
------------
请只回复新闻组
-
SELECT *
FROM Customers AS C
WHERE lastpaymentdate <>
(SELECT MAX(paymentdate)
FROM Payments AS P
WHERE P.customerid=C.customerid)
--
David Portas
------------
Please reply only to the newsgroup
--
" David Portas" < RE **************************** @ acm.org>在消息中写道
news：2v ******************** @ giganews.com ...
"David Portas" <RE****************************@acm.org> wrote in message
news:2v********************@giganews.com...
SELECT *
来自客户作为最后付款日期<>
（SELECT MAX（付款日期）
来自付款AS P
在哪里P.customerid = C.customerid）
SELECT *
FROM Customers AS C
WHERE lastpaymentdate <>
(SELECT MAX(paymentdate)
FROM Payments AS P
WHERE P.customerid=C.customerid)
哦可能是我的例子不是最好的！
如果我有什么
创建表客户（
CustomerID int，
LastPaymentAmount int，
LastPaymentDate Datetime）
创建表付款（
PaymentID int，
CustomerID int，
金额int，
PaymentDate日期时间）
我想查看LastPaymentAmount字段是否包含正确的值。
我显然无法使用max函数。
thx
oh may be my example was not the best!
what if I have
Create Table Customers (
CustomerID int,
LastPaymentAmount int,
LastPaymentDate Datetime )
Create Table Payments (
PaymentID int,
CustomerID int,
Amount int,
PaymentDate Datetime )
and I want to see if the LastPaymentAmount field holds correct values.
I couldnt use the max function for this obviously.
thx
>我显然无法使用max函数。
为什么不呢？我的查询仍然有效。
如果你想要一个使用连接的查询，那么试试这个：
SELECT C。*
来自客户AS $
LEFT JOIN
（SELECT customerid，MAX（paymentdate）AS lastpaymentdate
FROM Payments
GROUP BY customerid）AS P
ON C.customerid = P.customerid
AND C.lastpaymentdate = P.lastpaymentdate
WHERE C.lastpaymentdate IS NOT NULL
AND P.customerid IS NULL
这相对于相关子查询版本有一个可能的优势。这将是
包括持有Lastpaymentdate但在Payments表中没有任何非NULL
日期的客户。
-
David Portas
------------
请回复新闻组
-
> I couldnt use the max function for this obviously.
Why not? My query should still work.
If you want a query that uses a join then try this:
SELECT C.*
FROM Customers AS C
LEFT JOIN
(SELECT customerid, MAX(paymentdate) AS lastpaymentdate
FROM Payments
GROUP BY customerid) AS P
ON C.customerid=P.customerid
AND C.lastpaymentdate=P.lastpaymentdate
WHERE C.lastpaymentdate IS NOT NULL
AND P.customerid IS NULL
This has a possible advantage over the correlated subquery version. It will
include Customers who have a Lastpaymentdate but don''t have any non-NULL
dates in the Payments table.
--
David Portas
------------
Please reply only to the newsgroup
--
这篇关于关于JOIN语法的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
