SQL帮助 [英] SQL help
问题描述
我坚持以下查询。任何帮助将不胜感激！
table1：客户
uid，名称
1，bob
2，jane
3，john
table2：购买
uid，custID，datepurchase，item
1,1,1 / 1/2004，鼠标
2,1,1 / 2/2004，键盘
3,1,1 / 3 / 2004，usb key
4,2,1 / 1/2004，鼠标
5,2,6 / 19/2004，键盘
打印机
我想要一个查询来显示客户上次购买的是什么。
即结果：
bob，1/3/2004，usbkeyboard
jane，9/19/2004，键盘
john，1/1/2004，打印机
有谁知道怎么做？
/ r
尝试以下查询：
SELECT Customers.name，P1.datepurchase，P1.item
FROM Customers INNER JOIN购买AS P1 ON客户.uid = P1.custID
WHERE P1.datepurchase =（
SELECT Max（P2.datepurchase）
FROM FROM AS AS
WHERE P2.custID = P1.custID;
）;
" R.Chan" < RA ********** @ yahoo.com>在消息中写道
新闻：54 ************************** @ posting.google.c om ...
我坚持以下查询。任何帮助将不胜感激！
表1：客户
uid，名称
1，鲍勃
2，简，
3，约翰
表2：购买
uid，custID，datepurchase，item
1,1,1 / 1/2004，鼠标
2,1 / 1/2/2004，键盘
3,1 / 3/2004，usb key
4,2,1 / 1/2004，鼠标
5,2,6 / 19/2004，键盘
6， 3，2004年1月1日，打印机
我想要一个查询来显示客户上次购买的是什么。
即结果：
bob，1/3/2004， usbkeyboard
jane，9/19/2004，键盘
约翰，2004年1月1日，打印机
有谁知道怎么做？
/ r
" R.Chan" < RA ********** @ yahoo.com>在消息中写道
新闻：54 ************************** @ posting.google.c om ...
我坚持以下查询。任何帮助将不胜感激！
表1：客户
uid，名称
1，鲍勃
2，简，
3，约翰
表2：购买
uid，custID，datepurchase，item
1,1,1 / 1/2004，鼠标
2,1 / 1/2/2004，键盘
3,1 / 3/2004，usb key
4,2,1 / 1/2004，鼠标
5,2,6 / 19/2004，键盘
6， 3，2004年1月1日，打印机
我想要一个查询来显示客户上次购买的是什么。
即结果：
bob，1/3/2004， usbkeyboard
jane，9/19/2004，键盘
约翰，2004年1月1日，打印机
有谁知道怎么做？
/ r
SELECT tblGroup.GroupID，tblGroup.GroupName，tblRegion.RegID，
tblRegion.RegName，tblLocation.LID，tblLocation。 LocName，
tblIncident.IncdntDate，IIf（（不是IsNull（[RemAct1]）和IsNull（[Done1]）），1,0）
AS Open1，IIf（ （不是IsNull（[RemAct1]）而不是IsNull（[Done1]）），1,0）AS
Closed1，IIf（（不是IsNull（[RemAct2]）和IsNull（[Done2]）），1,0）AS Open2，
IIf（（不是IsNull（[RemAct2]）而不是IsNull（[Done2]）），1,0）AS Closed2 ，
IIf（（不是IsNull（[RemAct3]）和IsNull（[Done3]）），1,0）AS Open3，IIf（（不是
IsNull）[ RemAct3]）而不是IsNull（[Done3]）），1,0）AS Closed3，IIf（（不是
IsNull（[RemAct4]）而不是IsNull（[Done4]）），1,0）AS Closed4，IIf（（不是
IsNull（[RemAct5]）和IsNull（[Done5]）），1,0）AS Open5，IIf（（不是
IsNull（[RemAct5]）而不是IsNull（[Done5]）） ，1,0）AS Closed5
FROM（（tblRegion INNER JOIN tblLocation ON tblRegion.RegName =
tblLocation.RegName）INNER JOIN（tblIncident INNER JOIN tblFormal ON
tblIncident.GUIncdntID = tblFormal.GUIncdntID）ON tblLocation.LID =
tblIncident.LID）INNER JOIN tblGroup ON tblRegion.GroupID = tblGroup.GroupID
WHERE （（（tblFormal.Who1EID）= [Forms]！[frmRptDesign]！[cmbEID]））或
（（（tblFormal.Wh o2EID）= [表格]！[frmRptDesign]！[cmbEID]））或
（（（tblFormal.Who3EID）= [表格]！[frmRptDesign]！[cmbEID]））或
（（（tblFormal.Who4EID）= [Forms]！[frmRptDesign]！[cmbEID]））或
（（（tblFormal.Who5EID）= [Forms]！[frmRptDesign] ！[cmbEID]））;
* Sherwood Wang MVP *
这些天恩尼斯，MT的事情必定真的很慢让你花费所有的b
来创建那些不适用于
问题的愚蠢的SQL语句。唐，你不是在欺骗任何人 - 没有人相信这样一个荒谬的帖子来自微软Access MVP。
Sherwood Wang < SH **** @ waynes.net>在留言中写道
新闻：9Q ******************* @ animal.nntpserver.com ..
Hi,
I''m stuck on the following query. Any help would be greatly appreciated!
table1: Customers
uid, name
1, bob
2, jane
3, john
table2: Purchases
uid, custID, datepurchase, item
1, 1, 1/1/2004, mouse
2, 1, 1/2/2004, keyboard
3, 1, 1/3/2004, usb key
4, 2, 1/1/2004, mouse
5, 2, 6/19/2004, keyboard
6, 3, 1/1/2004, printer
I want a query to show what customers'' last purchase is.
i.e. results:
bob, 1/3/2004, usbkeyboard
jane, 9/19/2004, keyboard
john, 1/1/2004, printer
Does anyone know how to do this?
/r
Try the following query:
SELECT Customers.name, P1.datepurchase, P1.item
FROM Customers INNER JOIN Purchases AS P1 ON Customers.uid = P1.custID
WHERE P1.datepurchase = (
SELECT Max(P2.datepurchase)
FROM Purchases AS P2
WHERE P2.custID=P1.custID;
);
"R.Chan" <ra**********@yahoo.com> wrote in message
news:54**************************@posting.google.c om...Hi,
I''m stuck on the following query. Any help would be greatly appreciated!
table1: Customers
uid, name
1, bob
2, jane
3, john
table2: Purchases
uid, custID, datepurchase, item
1, 1, 1/1/2004, mouse
2, 1, 1/2/2004, keyboard
3, 1, 1/3/2004, usb key
4, 2, 1/1/2004, mouse
5, 2, 6/19/2004, keyboard
6, 3, 1/1/2004, printer
I want a query to show what customers'' last purchase is.
i.e. results:
bob, 1/3/2004, usbkeyboard
jane, 9/19/2004, keyboard
john, 1/1/2004, printer
Does anyone know how to do this?
/r
"R.Chan" <ra**********@yahoo.com> wrote in message
news:54**************************@posting.google.c om...Hi,
I''m stuck on the following query. Any help would be greatly appreciated!
table1: Customers
uid, name
1, bob
2, jane
3, john
table2: Purchases
uid, custID, datepurchase, item
1, 1, 1/1/2004, mouse
2, 1, 1/2/2004, keyboard
3, 1, 1/3/2004, usb key
4, 2, 1/1/2004, mouse
5, 2, 6/19/2004, keyboard
6, 3, 1/1/2004, printer
I want a query to show what customers'' last purchase is.
i.e. results:
bob, 1/3/2004, usbkeyboard
jane, 9/19/2004, keyboard
john, 1/1/2004, printer
Does anyone know how to do this?
/r
SELECT tblGroup.GroupID, tblGroup.GroupName, tblRegion.RegID,
tblRegion.RegName, tblLocation.LID, tblLocation.LocName,
tblIncident.IncdntDate, IIf((Not IsNull([RemAct1]) And IsNull([Done1])),1,0)
AS Open1, IIf((Not IsNull([RemAct1]) And Not IsNull([Done1])),1,0) AS
Closed1, IIf((Not IsNull([RemAct2]) And IsNull([Done2])),1,0) AS Open2,
IIf((Not IsNull([RemAct2]) And Not IsNull([Done2])),1,0) AS Closed2,
IIf((Not IsNull([RemAct3]) And IsNull([Done3])),1,0) AS Open3, IIf((Not
IsNull([RemAct3]) And Not IsNull([Done3])),1,0) AS Closed3, IIf((Not
IsNull([RemAct4]) And IsNull([Done4])),1,0) AS Open4, IIf((Not
IsNull([RemAct4]) And Not IsNull([Done4])),1,0) AS Closed4, IIf((Not
IsNull([RemAct5]) And IsNull([Done5])),1,0) AS Open5, IIf((Not
IsNull([RemAct5]) And Not IsNull([Done5])),1,0) AS Closed5
FROM ((tblRegion INNER JOIN tblLocation ON tblRegion.RegName =
tblLocation.RegName) INNER JOIN (tblIncident INNER JOIN tblFormal ON
tblIncident.GUIncdntID = tblFormal.GUIncdntID) ON tblLocation.LID =
tblIncident.LID) INNER JOIN tblGroup ON tblRegion.GroupID = tblGroup.GroupID
WHERE (((tblFormal.Who1EID)=[Forms]![frmRptDesign]![cmbEID])) OR
(((tblFormal.Who2EID)=[Forms]![frmRptDesign]![cmbEID])) OR
(((tblFormal.Who3EID)=[Forms]![frmRptDesign]![cmbEID])) OR
(((tblFormal.Who4EID)=[Forms]![frmRptDesign]![cmbEID])) OR
(((tblFormal.Who5EID)=[Forms]![frmRptDesign]![cmbEID]));
*Sherwood Wang MVP*
Things must be real slow in Ennis, MT these days for you to spend all the
effort to create that silly SQL statement that doesn''t apply to the
question. Don, you aren''t fooling anybody -- nobody believes such a
ridiculous post would be from a Microsoft Access MVP.
"Sherwood Wang" <sh****@waynes.net> wrote in message
news:9Q*******************@animal.nntpserver.com.. .
这篇关于SQL帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
