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
