在sql server中加入group by [英] group by with joins in sql server

问题描述

我正在使用sql server。现在我想加入两个如下表格。

Hi,
i am working with sql server. now i want to join two tables like below.

select 
bill.bill_no as BillNo,
bill.customer_name,
room_no_info.room_number,
isnull(convert(varchar(11),room_no_info.in_date,106),'01-Jan-99') as InDate,
isnull(convert(varchar(11),room_no_info.out_date,106),'01-Jan-99') as OutDate
 from bill inner join room_no_info on bill.bill_no=room_no_info.bill_no group by bill.bill_no,room_no_info.room_number,room_no_info.in_date,room_no_info.out_date,
bill.customer_name

it返回如下..



BillNo名称RoomNo InDate OutDate



20姓名：zzzz 315 02 Jun 2013 2013年6月14日

21姓名：xxxx 309 02 Jun 2013 15 Jun 2013

21姓名：xxxx 310 02 Jun 2013 03 Jun 2013

22姓名：yyyy 206 01 Jun 2013 2013年6月2日





它返回满足连接条件的所有行，但我想只返回一行从第二个表。



你可以帮助我吗？

it returns like below..

BillNo Name RoomNo InDate OutDate

20 Name:zzzz 315 02 Jun 2013 14 Jun 2013
21 Name:xxxx 309 02 Jun 2013 15 Jun 2013
21 Name:xxxx 310 02 Jun 2013 03 Jun 2013
22 Name:yyyy 206 01 Jun 2013 02 Jun 2013


it returns all rows which are satisfies join conditions but i want return only one row from the second table.

can u help me any one?

推荐答案

而不是 INNER JOIN ，使用 RIGHT 或 LEFT JOIN 。

了解差异，请阅读这篇文章： SQL连接的可视化表示 [ ^ ]

Instead INNER JOIN, use RIGHT or LEFT JOIN.
To understand the difference, please, read this article: Visual Representation of SQL Joins[^]

我使用聚合函数解决了。

i solved using aggregate function.

select
bill.bill_no as BillNo,
bill.customer_name,
max(room_no_info.room_number),
isnull(convert(varchar(11),room_no_info.in_date,106),'01-Jan-99') as InDate,
isnull(convert(varchar(11),room_no_info.out_date,106),'01-Jan-99') as OutDate
 from bill inner join room_no_info on bill.bill_no=room_no_info.bill_no group by bill.bill_no,room_no_info.in_date,room_no_info.out_date,
bill.customer_name

这篇关于在sql server中加入group by的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！