从SQL Server中的1：n表中选择不同的重复行

I am trying to select customers and their orders in one query, but I get customer and his orders in datatable which customer table columns repeated for each order.
I tried DISTINCT, GROUP BY but can't do it.

我试图在一个查询中选择客户及其订单,但我在数据表中获得客户及其订单,每个订单重复客户表列。我尝试过DISTINCT,GROUP BY但不能这样做。

SQL:

select * 
from Customer, Order 
where Order.CustomerID = Customer.CustomerID 
  and Customer.CustomerID = '2'

Tables:

2 个解决方案

#1

Since there cannot be different columns for each row you can't do it without having duplicates. Consider reading data separately, once for the customer and once for her orders.

由于每行不能有不同的列,因此如果没有重复,则无法执行此操作。考虑单独阅读数据,一次为客户,一次为她的订单。

i want to get all customers and orders the query count will grow.if i have 3 customer i want to get orders and customers in one query.not 6 times query execution.

我希望得到所有客户和订单,查询计数将增长。如果我有3个客户,我想在一个查询中获得订单和客户。不是查询执行的6倍。

You do not need to perfrom a separate query for each customer. You just need a single query for all customers and a single query for all orders. Then you may connect them in application layer rather than a single query.

您不需要为每个客户执行单独的查询。您只需要针对所有客户的单个查询以及针对所有订单的单个查询。然后,您可以在应用程序层而不是单个查询中连接它们。

But if you argue that you have too many customers and too many orders to hold them all in memory, well, then you may perform a separate query for each customer. That's a tradeoff between memory and CPU.

但是,如果你认为你有太多的客户和太多的订单将它们全部保存在内存中,那么你可以为每个客户执行单独的查询。这是内存和CPU之间的权衡。

#2

This is a very rare query, but this my understanding of your need :p.

这是一个非常罕见的查询,但这是我对你的需求的理解:p。

select *
from (
    select 'CustomerID' as col1, 'CustomerName' as col2, 'ContactName' as col3,
        'Address' as col4, 'City' as col5, 'PostalCode' as col6, 'Country' as col7, 0 as ord
    union all
    select CustomerID, CustomerName, ContactName, Address, City, PostalCode, Country, 1 as ord
    from Customers
    union all
    select 'OrderId', 'CustomerID', 'EmployeeID', 'OrderDate', 'ShipperID', Null, Null, 0 as ord
    union all
    select OrderId, CustomerID, EmployeeID, OrderDate, ShipperID, Null, Null, 2 as ord
    from Orders) res

In the result with ord = 0 you have titles, with ord = 1 you will have customers only and with ord = 2 you will have orders, and you can use this query with this condition:

在ord = 0的结果中,你有标题,ord = 1你将只有客户,ord = 2你将有订单,你可以在这个条件下使用这个查询:

where (col1 = @customerId and ord = 1) or (col2 = @customerId and ord = 2)

You can add or ord =0 if you want to add titles in your output.

如果要在输出中添加标题,可以添加ord = 0。