Sql Server子查询

-- 查询 prod_id 为 BR01 的客户信息

select * from OrderItems; -- order_num

select * from Orders; -- cust_id

select * from Customers;

--使用子查询 子查询子句需要使用 in 而不是= 除非你的子查询结果只返回一条数据可以使用 =
select * from Customers where Customers.cust_id in
(select Orders.cust_id from orders where orders.order_num in
(select OrderItems.order_num from OrderItems where orderitems.prod_id='BR01'));

select COUNT(*) from Orders where cust_id='1000000001';

select COUNT(*) from Orders,Customers where orders.cust_id=Customers.cust_id;

--子查询用于计算字段
select Customers.cust_id, Customers.cust_name,Customers.cust_state,(select COUNT(*) from Orders where Orders.cust_id=Customers.cust_id) as orders from customers group by Customers.cust_id ,cust_name,cust_state;

select * from Orders;
select COUNT(*),cust_id from Orders group by cust_id;

select * from OrderItems;
select OrderItems.prod_id,(select COUNT(*) from Orders where Orders.order_num=OrderItems.order_num) as orders from OrderItems group by prod_id ,orderitems.order_num;

select cust_id,(select COUNT(order_num) from Orders where Orders.cust_id=Customers.cust_id) from Customers;

select * from Customers where cust_id=(
select cust_id from Orders where cust_id='1000000003')