Sql server之常用三表联查举例

1.创建表

 

CREATE TABLE [dbo].[Customer](

    [CustomerID] [int] NULL,

    [CustomerName] [nvarchar](50) NULL

) ON [PRIMARY]

 

CREATE TABLE [dbo].[Customer_Order](

    [CustomerID] [int] NULL,

    [OrderID] [int] NULL,

    [Data] [datetime] NULL

) ON [PRIMARY]

 

CREATE TABLE [dbo].[OrderID](

    [OrderID] [int] NULL,

    [Amount] [int] NULL

) ON [PRIMARY]

 

 

2.无订单的用户

select customerName

from customer C

left join Customer_Order CO

on c.customerid=co.customerid

where ISNULL(CO.customerid,0)!=0

group by c.customername

 

3.每个人的订单成交量

select customername,isnull(SUM(Amount),0) as amounttotal from customer c

left join customer_order co on c.customerid=co.customerid

left join orderid o on co.orderid=o.orderid

group by customername

order by amounttotal

 

 

4.2009年谁成交的订单大于7000

select customername,isnull(SUM(Amount),0) as amounttotal,YEAR(co.data) as yearDate from customer c

left join customer_order co on c.customerid=co.customerid and YEAR(co.data)=2009

left join orderid o on co.orderid=o.orderid

group by customername,YEAR(co.data)

having isnull(SUM(Amount),0) >7000