数据库中有两表:1。orders订单表2。orderDetail订单详细表
表结构如下/
CREATE TABLE [dbo].[OrderDetail] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[OrderID] [int] NULL ,
[CardID] [int] NULL ,
[Numbers] [int] NULL
) ON [PRIMARY]
GOCREATE TABLE [dbo].[Orders] (
[id] [int] NOT NULL ,
[UserEmail] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[TotalPrice] [real] NULL ,
[OrderDate] [smalldatetime] NULL ,
[FullName] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[UserAddress] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[UserTel] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[OrderState] [int] NULL
) ON [PRIMARY]
GO表中的记录如:Orders
id UserEmail TotalPrice OrderDate
1 [email protected] 130.0 2007-07-05
2 [email protected] 280.0 2007-07-06 OrderDetail
ID orderID ProductID Numbers
1 1 1 4
2 1 10 5
3 2 1 3如何显示用SQL查询得出这样的结果:显示订单ID,总价格,日期,订单详细(产品ID*数量)
orderID totalPrice orderDate detail
1 130 2007-07-05 1*4;10*5
2 280 2007-07-06 1*3我是在Access中要用,所以请勿列存储过程。只用SQL语句,并可在ACCESS中使用,感谢!
表结构如下/
CREATE TABLE [dbo].[OrderDetail] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[OrderID] [int] NULL ,
[CardID] [int] NULL ,
[Numbers] [int] NULL
) ON [PRIMARY]
GOCREATE TABLE [dbo].[Orders] (
[id] [int] NOT NULL ,
[UserEmail] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[TotalPrice] [real] NULL ,
[OrderDate] [smalldatetime] NULL ,
[FullName] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[UserAddress] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[UserTel] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[OrderState] [int] NULL
) ON [PRIMARY]
GO表中的记录如:Orders
id UserEmail TotalPrice OrderDate
1 [email protected] 130.0 2007-07-05
2 [email protected] 280.0 2007-07-06 OrderDetail
ID orderID ProductID Numbers
1 1 1 4
2 1 10 5
3 2 1 3如何显示用SQL查询得出这样的结果:显示订单ID,总价格,日期,订单详细(产品ID*数量)
orderID totalPrice orderDate detail
1 130 2007-07-05 1*4;10*5
2 280 2007-07-06 1*3我是在Access中要用,所以请勿列存储过程。只用SQL语句,并可在ACCESS中使用,感谢!
select orderID,orderDate,sum( ProductID*Numbers) as totalprice
from Orders INNER JOIN OrderDetail on Orders.orderID = OrderDetail.orderID
group by orderID,orderDate
detail就没办法了
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[OrderID] [int] NULL ,
[CardID] [int] NULL ,
[Numbers] [int] NULL
) ON [PRIMARY]
GOCREATE TABLE [dbo].[Orders] (
[id] [int] NOT NULL ,
[UserEmail] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[TotalPrice] [real] NULL ,
[OrderDate] [smalldatetime] NULL ,
[FullName] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[UserAddress] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[UserTel] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[OrderState] [int] NULL
) ON [PRIMARY]
GO
insert into [dbo].[Orders](id,[UserEmail],[TotalPrice],[OrderDate])
select 1,'[email protected]',130.0,'2007-07-05' union all
select 2,'[email protected]',280.0,'2007-07-06'
go
insert into [OrderDetail](orderID,CardID,Numbers)
select 1,1,4 union all
select 1,10,5 union all
select 2,1,3
gocreate function (@orderid int)
returns varchar(50)
as
begin
declare @s varchar(50)
set @s=''
select @s=@s+ '+'+cast(CardID as varchar)+'*'+cast(Numbers as varchar) from OrderDetail where OrderID=@OrderID
set @s=stuff(@s,1,1,'')
return @s
end
goselect distinct a.id as orderid,a.TotalPrice,a.OrderDate,dbo.fun(b.OrderID) as detail from Orders a inner join OrderDetail b on a.id=b.OrderID
orderid TotalPrice OrderDate detail
----------- ------------- ----------------------- --------------------------------------------------
1 130 2007-07-05 00:00:00 1*4+10*5
2 280 2007-07-06 00:00:00 1*3(2 行受影响)