MSSQL - 使用查询结果查找其他详细信息

i have a query that i want to use the results from to search for other details in another table and display the results together

我有一个查询,我想使用结果来搜索另一个表中的其他详细信息并一起显示结果

my initial query is to show all accounts and how many bookings have been made for the day specified

我最初的查询是显示所有帐户以及指定日期的预订量

SELECT ACCOUNT, COUNT(ACCOUNT) Bookings 
FROM dbo.bookings 
WHERE DATEADDED = 101 
GROUP BY ACCOUNT

I then want to use the "ACCOUNT" which is a code to search for the name of the account in dbo.accounts.....

然后我想使用“ACCOUNT”这是一个代码来搜索dbo.accounts中帐户的名称.....

ACCOUNT is equal to CODE in dbo.accounts table and i need to get the NAME from this table

ACCOUNT等于dbo.accounts表中的CODE,我需要从此表中获取NAME

2 个解决方案

#1

Sounds like you just want to join the two tables together, so:

听起来你只想把两张桌子连在一起,所以:

SELECT a.NAME, b.ACCOUNT, COUNT(*) Bookings
FROM dbo.bookings b
INNER JOIN dbo.accounts a ON a.CODE = b.ACCOUNT
WHERE b.DATEADDED = 101
GROUP BY a.NAME, b.ACCOUNT

#2

You must be looking for something like this:

你一定在寻找这样的东西:

SELECT b.ACCOUNT
    ,a.NAME
    ,COUNT(ACCOUNT) Bookings
FROM dbo.bookings b
JOIN dbo.accounts a ON b.account = a.code
WHERE DATEADDED = 101
GROUP BY b.ACCOUNT
        ,a.NAME

Alternatively, you could use a CTE if you are using SQL Server:

或者,如果您使用的是SQL Server,则可以使用CTE:

WITH b(SELECT ACCOUNT, COUNT(ACCOUNT) Bookings 
        FROM dbo.bookings 
        WHERE DATEADDED = 101 
        GROUP BY ACCOUNT)

SELECT a.NAME
    ,b.*
FROM b
JOIN dbo.accounts a ON b.account = a.code;

#1