Question

Sql Server2005 查询sql语句怎么写

Sql Server2005 查询sql语句怎么写
Cart表(购物车)
cartId 购物车ID
productId 商品ID
orderId 订单ID

Pruduct表(商品表)
productId 商品ID
productName 商品名称

Order表(订单表)
orderId 订单ID
orderNumber 订单编号
shopId 供应商ID
userId 会员ID

User表(会员表)
userId 会员ID
userName 会员名称
password 会员密码

Shop表(供应商表)
shopId 供应商ID
shopName 供应商名称

查询 订单编号，会员名称，商品名称，供应商名称
如果能写出sql语句请高手帮个忙交下，如果一条sql语句写不出来，有什么其他方法也可以，我是要把这些数据导到Excel，获取这些数据，越简单获取越好，至

于Excel的读写可以略，我会

Answer 1

Easy!

SELECT Order.orderNumber,
User.userName,
Pruduct.productName,
Shop.shopName
FROM Order, User, Cart, Pruduct, Shop
WHERE Order.userId = User.serId
AND Cart.orderId = Order.orderId
AND Cart.productId = Pruduct.productId
AND Shop.shopId = Order.shopId

这样效率会高一点
SELECT Order.orderNumber,
User.userName,
Pruduct.productName,
Shop.shopName
FROM Order INNER JOIN USER ON Order.userId = User.serId
INNER JOIN Cart, Pruduct ON Cart.orderId = Order.orderId AND Cart.productId = Pruduct.productId
INNER JOIN Shop ON Shop.shopId = Order.shopId

追问

第一种不行：Msg 208, Level 16, State 1, Line 1
第二种还是不行：Msg 102, Level 15, State 1, Line 6

追答

你把User， Order加上中括号
[User], [Order]看看

追问

行不通，想问一个连接sql查询怎么写，三张表 Student,Class,SC_table
Student 有三个字段 stuId,stuName,stuDate,
Class有两个字段 classId,className
SC_table有三个字段scId,stuId,classId（stuId对应学生表classId对应班级）
根据stuId查询stuId,stuName,stuDate,className这四个字段的sql语句
我上面那问题我觉得太复杂了，用程序解决算了，我都高不清了，所以想问这个简单的怎么写，解决这个我会给分的 谢谢！感激不尽

追答

SELECT stu.stuId, stu.stuName, stu.stuDate, c.className
FROM Student stu, Class c, SC_table sc
where stu.stuId = sc.stuId
AND sc.classId = c.classId

Answer 2

先抢下沙发。马上解决

select order.orderid,user.username,pruduct.productname,(select shop.shopname from shop where shopid = order.orderid) from (order right join user

on order.userid = user.userid ) right join shop on order.shopid = shop.shopid;

给分吧

Answer 3

select orderNumber as 订单编号,
(select userName from User) as 会员名称,
(select productName from Pruduct) as 商品名称,
(select shopName from Shop) as 供应商名称 from Order

追问

不用试了，压根不可能有这种方法

追答

哎、无语了 无知哦

Answer 4

SELECT Order.orderNumber,
User.userName,
Pruduct.productName,
Shop.shopName
FROM User right join Order on Order.userId = User.serId right join Cart on Order.orderId=Cart.orderId right join Pruduct on Cart.productId=Product.productId right join Shop on Order.shopId=Shop.shopId