帮助SubQueries？ [英] Help with SubQueries?

问题描述

伙计们，


得到了这个查询...


--------------- ------------

SELECT TOP 5 Tbl_admin_hotels.HotelName，

（SELECT COUNT（Tbl_marketing_history.HotelID）

来自Tbl_marketing_history

WHERE Tbl_marketing_history.HotelID = Tbl_admin_hotels.HotelID）

AS CountTotal，

（SELECT MAX（DateSent）

来自Tbl_marketing_history

WHERE Tbl_marketing_history.HotelID = Tbl_admin_hotels.HotelID）

AS LastSent

来自Tbl_admin_hotels

在哪里没有Tbl_admin_hotels.HotelID = 99

ORDER BY CountTotal DESC

----------------------- ----


在表Tbl_marketing_history中还有一个''主题''列，我需要
，要抓住这个主题列的行应该与行相关

我在SubQuery中选择抓住''DateSent''。


我试过去试图抓住多个colu但是惨遭失败？可以

任何人帮助pleaseeee！


干杯，Ash

Guys,

Got this query...

---------------------------
SELECT TOP 5 Tbl_admin_hotels.HotelName,
(SELECT COUNT(Tbl_marketing_history.HotelID)
FROM Tbl_marketing_history
WHERE Tbl_marketing_history.HotelID = Tbl_admin_hotels.HotelID)
AS CountTotal,
(SELECT MAX(DateSent)
FROM Tbl_marketing_history
WHERE Tbl_marketing_history.HotelID = Tbl_admin_hotels.HotelID)
AS LastSent
FROM Tbl_admin_hotels
WHERE NOT Tbl_admin_hotels.HotelID = 99
ORDER BY CountTotal DESC
---------------------------

Within the table Tbl_marketing_history there is also a ''Subject'' column that
I require, the row to grab this Subject column from should relate to the row
I''m selecting in the SubQuery to grab ''DateSent''.

I''ve tried and tried to grab multiple columns but failed miserably? Can
anyone help pleaseeee!

Cheers, Ash

推荐答案

" ;学家霍尔" <再************* @ a-hall.com>在留言新闻中写道：3p ******************** @ eclipse.net.uk ...

"J. Hall" <re*************@a-hall.com> wrote in message news:3p********************@eclipse.net.uk...

伙计们，

得到了这个查询...

---------------------------
SELECT TOP 5 Tbl_admin_hotels.HotelName，
（SELECT COUNT（Tbl_marketing_history.HotelID）
来自Tbl_marketing_history
在哪里Tbl_marketing_history.HotelID = Tbl_admin_hotels.HotelID）
AS CountTotal，
（SELECT MAX （DateSent）
FROM Tbl_marketing_history
在哪里Tbl_marketing_history.HotelID = Tbl_admin_hotels.HotelID）
AS LastSent
FROM Tbl_admin_hotels
在哪里没有Tbl_admin_hotels.HotelID = 99
订购BY CountTotal DESC
---------------------------

在表Tbl_marketing_history中还有一个我需要的''主题'专栏，抓住这个主题列的行应该与行
我在SubQuery中选择抓住''DateSent''。

我'尝试过并试图抓住多个列，但却失败了？可以
任何人帮助pleaseeee！

干杯，Ash

Guys,

Got this query...

---------------------------
SELECT TOP 5 Tbl_admin_hotels.HotelName,
(SELECT COUNT(Tbl_marketing_history.HotelID)
FROM Tbl_marketing_history
WHERE Tbl_marketing_history.HotelID = Tbl_admin_hotels.HotelID)
AS CountTotal,
(SELECT MAX(DateSent)
FROM Tbl_marketing_history
WHERE Tbl_marketing_history.HotelID = Tbl_admin_hotels.HotelID)
AS LastSent
FROM Tbl_admin_hotels
WHERE NOT Tbl_admin_hotels.HotelID = 99
ORDER BY CountTotal DESC
---------------------------

Within the table Tbl_marketing_history there is also a ''Subject'' column that
I require, the row to grab this Subject column from should relate to the row
I''m selecting in the SubQuery to grab ''DateSent''.

I''ve tried and tried to grab multiple columns but failed miserably? Can
anyone help pleaseeee!

Cheers, Ash

尝试使用派生表进行聚合：
< br $>
SELECT TOP 5

Tbl_admin_hotels.HotelName，

tbl_marketing_history.subject，

countTotal，

lastSent

FROM

Tbl_admin_hotels

加入

（选择

HotelID，

COUNT（Tbl_marketing_history.HotelID）as countTotal，

MAX（dateSent）as lastSent

FROM Tbl_marketing_history

GROUP BY HotelID）作为mktHistory

ON mktHistory.HotelID = Tbl_admin_hotels.HotelID


加入Tbl_marketing_history

ON Tbl_marketing_history.hotelID = mktHistory。 HotelID和

Tbl_marketing_history.dateSent = mktHistory.dateSent


WHERE Tbl_admin_hotels.HotelID！= 99

订购CountTotal DESC


-

Paul Horan

Sr. Architect VCI

Springfie ld，Mass
www.vcisolutions.com

Try using a derived table for the aggregates:

SELECT TOP 5
Tbl_admin_hotels.HotelName,
tbl_marketing_history.subject,
countTotal,
lastSent
FROM
Tbl_admin_hotels
JOIN
(SELECT
HotelID,
COUNT(Tbl_marketing_history.HotelID) as countTotal,
MAX( dateSent ) as lastSent
FROM Tbl_marketing_history
GROUP BY HotelID ) as mktHistory
ON mktHistory.HotelID = Tbl_admin_hotels.HotelID

JOIN Tbl_marketing_history
ON Tbl_marketing_history.hotelID = mktHistory.HotelID and
Tbl_marketing_history.dateSent = mktHistory.dateSent

WHERE Tbl_admin_hotels.HotelID != 99
ORDER BY CountTotal DESC

--
Paul Horan
Sr. Architect VCI
Springfield, Mass
www.vcisolutions.com

您好


您可能想尝试为每个HotelId假设唯一的DateSent值：


选择TOP 5 A.HotelName，

H.CountTotal，

H.LastSent，

S.Subject

FROM Tbl_admin_hotels A

LEFT JOIN（选择HotelId，COUNT（Tbl_marketing_history.HotelID）AS

CountTotal，

MAX（DateSent）AS LastSent

FROM Tbl_marketing_history）H ON H.HotelID = A.HotelID）

LEFT JOIN Tbl_marketing_history S ON H.HotelID = S.HotelID AND H.LastSent =

S.DateSent

WHERE A.HotelID<> 99

订购H.CountTotal DESC


John


" J.霍尔" <再************* @ a-hall.com>在消息中写道

news：3p ******************** @ eclipse.net.uk ...

Hi

You may want to try it assumes unique DateSent values for each HotelId :

SELECT TOP 5 A.HotelName,
H.CountTotal,
H.LastSent,
S.Subject
FROM Tbl_admin_hotels A
LEFT JOIN (SELECT HotelId, COUNT(Tbl_marketing_history.HotelID) AS
CountTotal,
MAX(DateSent) AS LastSent
FROM Tbl_marketing_history ) H ON H.HotelID = A.HotelID)
LEFT JOIN Tbl_marketing_history S ON H.HotelID = S.HotelID AND H.LastSent =
S.DateSent
WHERE A.HotelID <> 99
ORDER BY H.CountTotal DESC

John

"J. Hall" <re*************@a-hall.com> wrote in message
news:3p********************@eclipse.net.uk...

伙计们，

得到了这个查询...

------------------------- -
SELECT TOP 5 Tbl_admin_hotels.HotelName，
（SELECT COUNT（Tbl_marketing_history.HotelID）
FROM Tbl_marketing_history
在哪里Tbl_marketing_history.HotelID = Tbl_admin_hotels.HotelID）
AS CountTotal ，
（SELECT MAX（DateSent）
来自Tbl_marketing_history
在哪里Tbl_marketing_history.HotelID = Tbl_admin_hotels.HotelID）
AS LastSent
来自Tbl_admin_hotels
在哪里没有Tbl_admin_hotels。 HotelID = 99
订购CountTotal DESC
---------------------------

在表Tbl_marketing_history中还有一个我需要的主题列
，从中获取此主题列的行应该与
行相关，我在SubQuery中选择要抓取' 'DateSent''。
任何人帮助pleaseeee！

干杯，Ash

Guys,

Got this query...

---------------------------
SELECT TOP 5 Tbl_admin_hotels.HotelName,
(SELECT COUNT(Tbl_marketing_history.HotelID)
FROM Tbl_marketing_history
WHERE Tbl_marketing_history.HotelID = Tbl_admin_hotels.HotelID)
AS CountTotal,
(SELECT MAX(DateSent)
FROM Tbl_marketing_history
WHERE Tbl_marketing_history.HotelID = Tbl_admin_hotels.HotelID)
AS LastSent
FROM Tbl_admin_hotels
WHERE NOT Tbl_admin_hotels.HotelID = 99
ORDER BY CountTotal DESC
---------------------------

Within the table Tbl_marketing_history there is also a ''Subject'' column that I require, the row to grab this Subject column from should relate to the row I''m selecting in the SubQuery to grab ''DateSent''.

I''ve tried and tried to grab multiple columns but failed miserably? Can
anyone help pleaseeee!

Cheers, Ash

优秀！不得不做一些小改动，但查询工作正常 - 我

实际上明白你做了什么（没想到我会从最初开始

查看它！）。


总而言之，你已经创建了一个虚拟表格，然后你将其加入

到表中。 HotelID的？只是为了确认我的理解？


另一个问题，语法是什么！=意思？以前没见过

。


非常感谢你的帮助！


干杯，Ash

" -P-" <恩********** @ hotmail.DOTcom>在消息中写道

news：xM ******************** @ adelphia.com ...

Excellent! Had to make a few minor changes but the query worked fine - I
actually understand what you''ve done (didn''t think I would from initially
viewing it!).

So in summary you''ve created a ''virtual'' table that you''re then left joining
to the table of HotelID''s? Just to confirm my understanding?

One other question, what does the syntax != mean? haven''t seen that
previously.

Many thanks for your help!

Cheers, Ash
"-P-" <en**********@hotmail.DOTcom> wrote in message
news:xM********************@adelphia.com...

" ;学家霍尔" <再************* @ a-hall.com>在消息中写道

"J. Hall" <re*************@a-hall.com> wrote in message

新闻：3p ******************** @ eclipse.net.uk ...

news:3p********************@eclipse.net.uk...

伙计们，

有这个查询...

------------- --------------
SELECT TOP 5 Tbl_admin_hotels.HotelName，
（SELECT COUNT（Tbl_marketing_history.HotelID）
FROM Tbl_marketing_history
WHERE Tbl_marketing_history。 HotelID = Tbl_admin_hotels.HotelID）
AS CountTotal，
（SELECT MAX（DateSent）
FROM Tbl_marketing_history
WHERE Tbl_marketing_history.HotelID = Tbl_admin_hotels.HotelID）
AS LastSent
FROM Tbl_admin_hotels
在哪里没有Tbl_admin_hotels.HotelID = 99
ORDER BY CountTotal DESC
----------------------- ----

在表Tbl_marketing_history中还有一个我需要的''主题'列
，从中获取此主题列的行应该与
相关我在S中选择了一行ubQuery抓住''DateSent''。

我已经尝试过试图抓住多个列，但却失败了？可以
任何人帮助pleaseeee！

干杯，Ash

Guys,

Got this query...

---------------------------
SELECT TOP 5 Tbl_admin_hotels.HotelName,
(SELECT COUNT(Tbl_marketing_history.HotelID)
FROM Tbl_marketing_history
WHERE Tbl_marketing_history.HotelID = Tbl_admin_hotels.HotelID)
AS CountTotal,
(SELECT MAX(DateSent)
FROM Tbl_marketing_history
WHERE Tbl_marketing_history.HotelID = Tbl_admin_hotels.HotelID)
AS LastSent
FROM Tbl_admin_hotels
WHERE NOT Tbl_admin_hotels.HotelID = 99
ORDER BY CountTotal DESC
---------------------------

Within the table Tbl_marketing_history there is also a ''Subject'' column that I require, the row to grab this Subject column from should relate to the row I''m selecting in the SubQuery to grab ''DateSent''.

I''ve tried and tried to grab multiple columns but failed miserably? Can
anyone help pleaseeee!

Cheers, Ash

尝试使用派生表进行聚合：

SELECT TOP 5
Tbl_admin_hotels.HotelName，
tbl_marketing_history.subject，
countTotal，
lastSent
FROM
Tbl_admin_hotels
加入
（选择
HotelID，
COUNT（Tbl_marketing_history.HotelID）as countTotal，
MAX（dateSent）as lastSent
FROM Tbl_marketing_history
GROUP BY HotelID）as mktHistory
ON mktHistory.HotelID = Tbl_admin_hotels.HotelID

加入Tbl_marketing_history
ON Tbl_marketing_history.hotelID = mktHistory.HotelID和
Tbl_marketing_history.dateSent = mktHistory.dateSent

Tbl_admin_hotels.HotelID！= 99
订购CountTotal DESC

-
Paul Horan
高级建筑师VCI
斯普林菲尔德，马克斯
< a rel =nofollowhref =http://www.vcisolutions.comtarget = _blank> www.vcisolutions.com

这篇关于帮助SubQueries？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！