1、订单表 myOrder
订单号 订单时间 客户ID 金额2、订单明细表 orderDetail(一个订单号对应多行)
订单号 商品类别 商品名称 单价 数量3、客户表 Client
客户ID 客户名称页面展示:
订单号 订单时间 金额 客户名称 【商品类别1金额】 【商品类别2金额】写了一个不符合要求
SELECT 订单号,订单时间,客户ID,客户名称,商品类别,商品名称,SUM(单价*数量)
FROM myOrder AS O
JOIN orderDetail AS D ON(O.订单号=D.订单号)
JOIN Client AS C ON(C.客户ID=O.客户ID)
GROUP BY 订单号,订单时间,客户ID,客户名称,商品类别,商品名称结果不是按订单号区分的,而是按客户ID生成的行。
问:怎样按订单号一条语句实现页面展示的行数据。
订单号 订单时间 客户ID 金额2、订单明细表 orderDetail(一个订单号对应多行)
订单号 商品类别 商品名称 单价 数量3、客户表 Client
客户ID 客户名称页面展示:
订单号 订单时间 金额 客户名称 【商品类别1金额】 【商品类别2金额】写了一个不符合要求
SELECT 订单号,订单时间,客户ID,客户名称,商品类别,商品名称,SUM(单价*数量)
FROM myOrder AS O
JOIN orderDetail AS D ON(O.订单号=D.订单号)
JOIN Client AS C ON(C.客户ID=O.客户ID)
GROUP BY 订单号,订单时间,客户ID,客户名称,商品类别,商品名称结果不是按订单号区分的,而是按客户ID生成的行。
问:怎样按订单号一条语句实现页面展示的行数据。
GO
IF OBJECT_ID('myOrder') IS NOT NULL DROP TABLE myOrder
IF OBJECT_ID('orderDetail') IS NOT NULL DROP TABLE orderDetail
IF OBJECT_ID('Client') IS NOT NULL DROP TABLE Client
GO
CREATE TABLE myOrder(
orderId INT IDENTITY(1,1) PRIMARY KEY,
orderTime DATETIME,
clientId INT,
pay INT
)
CREATE TABLE orderDetail(
orderId INT,
categoryId INT,
goodsName NVARCHAR(20),
price INT,
num INT
)
CREATE TABLE Client(
clientId INT PRIMARY KEY,
clientName NVARCHAR(20)
)
GO
SET NOCOUNT ON
INSERT INTO myOrder(orderTime,clientId,pay) VALUES( '2018-01-01 08:00:00',1,200);
INSERT INTO myOrder(orderTime,clientId,pay) VALUES( '2018-01-02 08:00:00',2,350);
INSERT INTO orderDetail(orderId,categoryId,goodsName,price,num) VALUES(1,1,'面膜',100,1)
INSERT INTO orderDetail(orderId,categoryId,goodsName,price,num) VALUES(1,2,'漫画',50,2)
INSERT INTO orderDetail(orderId,categoryId,goodsName,price,num) VALUES(2,1,'面膜',100,2)
INSERT INTO orderDetail(orderId,categoryId,goodsName,price,num) VALUES(2,3,'键盘',150,1)
INSERT INTO Client(clientId,clientName) VALUES(1,'小明')
INSERT INTO Client(clientId,clientName) VALUES(2,'小红')DECLARE @sql NVARCHAR(MAX),@sqlGoods NVARCHAR(MAX)SET @sqlGoods=(
SELECT ',ISNULL( (SELECT d.price*d.num FROM orderDetail AS d WHERE d.orderId=m.orderId AND d.categoryId='
+RTRIM(d.categoryId)+'), 0) AS ['+d.goodsName+']' FROM orderDetail AS d
GROUP BY d.categoryId,d.goodsName FOR XML PATH('')
)SET @sql='
SELECT
m.orderId
,m.orderTime
,m.pay
,c.clientName
'+@sqlGoods+'
FROM myOrder AS m INNER JOIN Client AS c ON m.clientId=c.clientId'PRINT @sql
/*
SELECT
m.orderId
,m.orderTime
,m.pay
,c.clientName
,ISNULL( (SELECT d.price*d.num FROM orderDetail AS d WHERE d.orderId=m.orderId AND d.categoryId=1), 0) AS [面膜]
,ISNULL( (SELECT d.price*d.num FROM orderDetail AS d WHERE d.orderId=m.orderId AND d.categoryId=2), 0) AS [漫画]
,ISNULL( (SELECT d.price*d.num FROM orderDetail AS d WHERE d.orderId=m.orderId AND d.categoryId=3), 0) AS [键盘]
FROM myOrder AS m INNER JOIN Client AS c ON m.clientId=c.clientId
*/
EXEC (@sql)
from (select 商品类别 from #orderDetail group by 商品类别) as Aset @sql='select A.订单号,订单时间,金额,客户名称,'+@sql+' from #myOrder A
join #orderDetail B on A.订单号=B.订单号
join #Client C on A.客户ID=C.客户ID
group by A.订单号,订单时间,金额,客户名称'exec(@sql)